Excel pulls crosstab from Access; how to update new months?

R

Rachel Garrett

New Member
Joined
May 13, 2008
Messages
11
I have a crosstab query in Access that groups by month and year. In Excel 2003, I have a query that pulls in the crosstab query.

At the time, I couldn't just pull in the Access table. For the other Access tables and queries, I could go to Data >> Import External Data >> Import Data >> select the database as a source >> select the table name.

However, to get the crosstab query, I had to go to Data >> Import External Data >> New Database Query >> MS Access Data Source* >> browse to my database >> select the crosstab query name >> select the data I wanted.

Since I wanted all the data, there did not seem to be a difference. But now that new dates are being added to the database, there *is* a problem. When I wrote the query, I only included the months available -- up to June 08. Now that there is data in July 08, that month exists as a heading in the crosstab, but Excel's query doesn't know to pull it! I have to manually edit the query to add the new month.

So my question is... is there a way to do this automatically? I have two crosstabs in five different spreadsheets, making a total of ten manual updates I have to remember to do each month.

Thank you in advance for any help.

--Rachel Garrett
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
You might be best to use Excel's Pivot table feature and use your source table as the data source for the pivot, rather than trying to link to a crosstab. Then when you refresh the pivot, any new months will be picked up automatically.
 
Upvote 0
Hi, Rachel.

Rorya's suggestion is likely best. For your information, another option might be to do the cross tab in Excel. Excel can pull the data from the Access tables / queries such as with ADO and the basic form of the cross tab SQL will be as below. (I guess this will be like in Access but as I don't work with Access I don't know.) This can be programmed. If you want to avoid programming, a query table can be used and set to refresh on file opening to get the desired automatic operation. Be aware that query tables have limitations and a VBA + ADO approach will be better.

The pivot table might use more memory. It will be wonderfully flexible and the approach I mention will be just the results.

Regards, Fazza

Code: 
TRANSFORM SumOrWhatever(data field)
SELECT row field/s
FROM table
WHERE criteria
GROUP BY row field/s
PIVOT column field optionally [IN particular column fields]
 
Upvote 0
Thank you both for your responses. I have more math based on the crosstab, and it is easier if I pull it in from Access and have a "plain" spreadsheet, rather than forcing my formulas to reference the data with GETPIVOT syntax.

What kind of automation could I set up or program, so that the query keeps grabbing data when it appears in new months (a new column on the crosstab)? If there isn't a way to do this, I would like to know that it's not just me. :)

Thank you.
 
Upvote 0
Rachel Garrett said:
What kind of automation could I set up or program, so that the query keeps grabbing data when it appears in new months (a new column on the crosstab)?
Click to expand...

You can set the query to autorefresh on file open. Just right click from a cell in the query and go to the query's properties, or via the toolbar or via the menu, and set the refresh on open. That is all. No VBA. Then everytime the file opens the query will refresh and it will automatically add/delete columns in the results set.

If you wanted to program it, you can use a single "workbook reference.RefreshAll" or "worksheet reference.querytables(1).refresh" (prior to Excel 2007).

Suggest you set up some test data in Excel or Access and run a simple cross tab.

Or the whole thing could be programmed with some error checking - such as check the source file exists at the expected path, that the results are returned OK, etc. This could be a query table however it is better with ADO returning a recordset. This can be pasted to the worksheet with copyfromrecordset. Again, if the cross tab needs 5 columns that is how many fields you will have retruend, and if it needs 100 that is how many you will have.

HTH, Fazza
 
Upvote 0
I do have it set to refresh on open, but "refreshing" only grabs new data in existing columns (months). Right now, it would have columns for Jan 08, Feb 08...up through July 08.

When users start entering new data in the Access database, the Access crosstab query automatically adds any new months to the grouping. If users started entering target dates for August and September, the crosstab query in Access would have columns for Aug 08 and Sep 08.

However...the new columns for Aug 08 and Sep 08 do not get added automatically to the query in Excel. I have to manually edit the query.

Tell you what, I think it would make more sense if I were showing you some screenshots. I will paste some on Monday when I have access to my database.
 
Upvote 0
Rachel,

What I'm describing is having Excel (Excel's MS Query) do the cross tab. Then it should dynamically adjust the columns.

What I think you have is a normal query from Excel to the database. When it is set up it will be pulling particular fields from the crosstab in Access. Say, "SELECT field_1, field_2". When the crosstab in Access additionally then has new field_3, the Excel query's SQL is still "SELECT field_1, field_2" so it only pulls those fields. If this is the case, suggest you edit the SQL in Excel's query table from "SELECT field_1, field_2" to "SELECT *" to pull all. This might get the results you want & maybe not in the right order because new columns will add on the end as they are found.

Just for info but maybe not useful, I guess another way if you were pulling from the cross tab in Access, and not doing the cross tab in Excel, would be to pull in first the field names, such as with DAO, and then create a text string of these and use it to edit the SQL in Excel. So it would be a little like "SELECT " & Join$(array("field_1", "field_2", "field_3"),",")

HTH. Regards, Fazza
 
Upvote 0
You can view the SQL (and change it) within MSQuery.
 
Upvote 0
Hi, Rachel.

Within MS Query, hit the SQL icon. That should open a form where you can edit the SQL. (Or there will be a menu path to get there too. I haven't looked. I guess edit, etc.) I think if you have complex SQL, you can get a message that it isn't editable in MS Query - that is, this option to edit within the MS Query GUI isn't always available. One easy workaround to this is go to the VBE's immediate window. Pre Excel 2007 - and I don't know 2007 objects - if the sheet with the query is active, the SQL (aka CommandText) is read/write. So to see it,
Code: 
? activesheet.querytables(1).sql

The SQL can be edited and assigned again such as by deleting the "? " from the debug print line. Deleting its end character so the next bit of the SQL appears on the same line after the ".sql" Insert the equals and surround with quotes. So it becomes.
Code: 
activesheet.querytables(1).sql="SELECT * FROM table"
Entering this will assign the new SQL.

Or do the whole thing in VBA. It is litle more than text manipulation.

So something like below. HTH, Fazza

Code: 
strSQL = join$(array(" _
"SELECT *", _
"FROM table", _
"WHERE criteria", _
"GROUP BY whatever"), vbcr)
 
wksRef.querytables(1).sql = strSQL
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Querying access db from excel - 32 bit to 64 bit conversion
Replies
2
Views
68
JEC
JEC
M
  • Question
Getting Power Query data to an Excel worksheet
Replies
4
Views
255
MichaelMorris
M
L
  • Solved
Option to combine single row from many tables into one new table
Replies
3
Views
185
awoohaw
awoohaw
D
  • Question
Excel adodb Operation must use an updateable query
Replies
2
Views
341
drag1c
D
R
  • Question
Need To Insert Values From Excel To Access
Replies
4
Views
246
ranjith2523
R

Forum statistics

Threads
1,215,523
Messages
6,125,315
Members
449,218
Latest member
Excel Master

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top