Hi,

Is it possible to transform an Access cross-tab query to a table?

Example:

Cross-tab query returns:

Company South North West East
Comp1 10 11 12 14
Comp2 15 16 17 18
Comp3 19 20 21 22

I want to end up with a table which has the following fields:

Company
South
North
West
East

Thanks
Sayed

Can you open the query in designer view, and then click Query in the text menu, and then select Make Table query. This will make a table from your query results, which is good for the first run. Then change the query to an Append query for subsequent runs. Then also create a delete query for the new table. Then each time you want to fill the table, do this with your code:

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryDELETEmyNewTable"
DoCmd.OpenQuery "qryAPPENDmyXTabData"
DoCmd.SetWarnings True

This will clean out old data, and repopulate your table with new data. Then DoCmd.OpeReport etc.

Did that help?



mmcdonal

Look it up at: http://wrox.books24x7.com

Thank you Sir!

I have got the following answer from another site:(it works fine)

Create a new query that is based on your existing crosstab query. Make the new query a 'make table' query (see menu option Query > Make Table Query) and follow the on-screen steps.