Excel with Access datasource

**corix** · 01-05-2010, 04:01 PM

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

**ConneXionLost** · 01-05-2010, 04:07 PM

Hi corix,

Welcome to the forum!

You can build an Access "CrossTab query" to manipulate the data into the layout you need, then connect your Excel spreadsheet to the Crosstab query as the data source instead of the table.

Cheers,

**corix** · 01-05-2010, 04:15 PM

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

**corix** · 01-05-2010, 05:05 PM

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?

**ConneXionLost** · 01-05-2010, 07:38 PM

Small hiccup; Excel only sees tables and "Select" queries as data sources. Not a problem, just create a select query that shows all the fields in your Xtab query, then use the select query as the data source.

**corix** · 01-05-2010, 10:02 PM

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?

**ConneXionLost** · 01-05-2010, 11:32 PM

Hi corix,

Sorry. In answering your post, my response was directed at improving the presentation of the data, thus the crosstab query suggestion. Unfortunately, I'm not familiar with the VBA required to open an Access database from an Excel workbook and "automatically refresh" your data.

Perhaps you should start a new thread in the Excel Programming forum with the title "Refreshing Excel spreadsheet from an Access Database using VBA", or something similar.

As before, link to this thread so others will know the history of your question.

Cheers,

**corix** · 01-06-2010, 12:30 AM

Oh... That's just too bad.. I was hoping my situation would not warrant any coding specially in VBA. But if that's the only way to accomplish what I'm trying to do then I guess I have no choice. Still I'm hoping you or somebody here will still come up with a different solution later on.. Preferably a simpler one