Excel with Access datasource

Hi All,

This question is also posted in the excel forum. I just thought that perhaps this particular problem might be solved in Access using query. Anyways, here it is...

I am tasked with creating a report using Excel with data coming from Access database. I am able to connect Excel to the Access database and view the data as-is. However, the data needs to be presented in such a way quite different from how the Access table is structured.

Here's simplified version of how the access table looks like:
NO___PRJNAME____YEAR__AMORT
001__Project A__2008__100,000
001__Project A__2009__200,000
001__Project A__2010__100,000
002__Project B__2007__400,000
002__Project B__2008__800,000
002__Project B__2009__800,000
002__Project B__2010__800,000
002__Project B__2011__400,000

Here's what the excel document should look like:
NO____PRJNAME_____2007______2008______2009______2010______2011___
001___Project A_____________100,000___200,000___200,000__________
002___Project B___400,000___800,000___800,000___800,000___400,000

The data in excel is refreshed automatically everytime it is opened to reflect any changes made to the data in the Access database. Is this possible at all? By the way I'm using Office 2007. Any help would be greatly appreciated.

- corix

Hi ConneXionLost,

Thanks for the quick reply. I'm not familiar with cross-tab query but I will look into it. I'll post again soon if I get stuck somewhere.
Again, thanks for the help.

- corix

Hi, I'm back. So I created a crosstab query, saved the file and closed Access. Now when I try to look for the crosstab query in Excel (Data/Get External Data/From Access), all I see are tables and views inside my database. I don't see the crosstab query. What am I missing here?

Thanks for bearing with me ConneXionLost. Ok, so I created a select query with all the data I need for the crosstab.. then I used it as the datasource. So far so good.. What's the next step?