Thread: Excel with Access datasource

Hi All,

With the forum admin's permission, I wish to move this thread back here in Excel forum failing to find solution in Access. Here it goes:

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.

To see how the discussion went please click here.

Thanks

Thanks for replying guys. I'm not familiar with pivot table but I would certainly look into this pivot table thing. Perhaps this is the solution I'm looking for. Do I apply this pivot table on the Access table or on the Excel document?
Thanks again.