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

[Rachel Garrett]

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

 

[Fazza]

PS with erratum

I see I posted an extra character in the last code section. Please delete it: join$(array(" _

F

 

[JPCMcAllen]

You could also use the TransferSpreadsheet Method from within Access to export your crosstab to a named Excel spreadsheet.

Test by manually running file - export on the crosstab.

 

[Rachel Garrett]

Thank you very much. I'm in now, but I'd like to clarify a confusing point for the benefit of anyone who'd be reading this in the future.

When I right-clicked and said "Edit Query", I get a simple Query Wizard window. There is no SQL option and no menu. When I click "Cancel", it says, "Do you want to continue editing your query in Microsoft Query?" I always said No before, because I figured that meant I was already in Microsoft Query. (To most people, "continue" means "do the same as what came before.")

Come to find out...after reading the above posts, I clicked "Yes" to "continue" -- and there is this whole option where I can design a query and write in SQL, just like I can in Access!!! This about made my week. Or month.

So "Continue editing in MS Query?" actually means "Do you want to switch over to the real MS Query instead of the Query Wizard?"

--Rachel

 

[Rachel Garrett]

I am sorry, I'm stuck again. I thought that once I got into MS Query (the real version, not the wizard version), the problem was solved because I could replace the specific columns with an asterisk. However, as soon as I do this, MS Query INSISTS that I really wanted to spell out each column that existed at that moment. Here is the query before...

SELECT `Crosstab for Achieved Dates PSD`.`Question name`, `Crosstab for Achieved Dates PSD`.`2007/07/01`, `Crosstab for Achieved Dates PSD`.`2008/01/01`, `Crosstab for Achieved Dates PSD`.`2008/03/01`, `Crosstab for Achieved Dates PSD`.`2008/04/01`, `Crosstab for Achieved Dates PSD`.`2008/05/01`, `Crosstab for Achieved Dates PSD`.`2008/06/01`, `Crosstab for Achieved Dates PSD`.`2008/07/01`
FROM `T:\DATABASENAME`

So I change it to this:

SELECT `Crosstab for Achieved Dates PSD`.*
FROM `T:\DATABASENAME`

And as soon as I click "OK", and go back to SQL view, it has been changed back again.

So does this mean that the MS Query solution isn't going to work? If not I will try Fazza's solution in #10. But I don't understand why using VBA to assign the SQL to be "* from table" will work, if using MS Query to assign the SQL to be "* from table" keeps getting reset to the specific names existing in the table at the moment I wrote it.

 

[Fazza]

Hi, Rachel.

The MS Query GUI is not ideal. You could investigate further whether or not it will work, but it is better to take other routes. It is MS Query that is making the change from "SELECT * FROM ..." to "SELECT list the fields FROM..". Using VBA has nothing to do with MS Query so the change does not occur.

Simplest is with the worksheet containing the query active, go into the VBE and type in the immediate window,

activesheet.querytables(1).sql="SELECT * FROM `T:\DATABASENAME`"

That should be all that is required for pre Excel 2007. For Excel 2007 the objects are a little different & I can not advise on that. Use the macro recorder to see what to use.

The query table approach is usually OK however for a better solution, like I posted above,

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.

regards, Fazza