Macro help??

[cornishteeth]

I have looked in the help files and posted this question previously, but I still can't figure it out. I have created a macro that transfers my queries to a spreadsheet. I have a total of 12 queries based on date inputs and utilize the TransferSpreadsheet option. This works just fine, but I would now like to refine the output. When I open the Excel file it has created 12 different spreadsheets. The data contained is quite minimal (usually 1 to 10 rows by 2 columns.) How can I put all the data on one single spreadsheet and not 12? For example, query 1 could be in cells in A1:B200, query 2 in C1:D200, etc. I read in the help files that when exporting data from Access to Excel, the range option is not allowed. How do I do this? I am very new to Access and quite green.

Cornish Teeth

 

[Ziggy]

Is the data that is exported the same for all 12, as in same fields and columns.

 

[cornishteeth]

Unfortunately, they do not. 4 come from the same table. 4 come from another table, and 4 come from yet another. It would be alright if they were just broken down into three vice twelve. Any one know???

Cornish teeth

 

[jobb]

try this, create NEW tables that will contain the results of each of your queries.

Include an AutoNumber field in each one, set to increment.

make a BIG table with a number id field (not autonumber) and then across the columns, each query as you would like it to appear in excel
opened up, the table's fields would look like this:

idnumber, query1fA, query1fb, query1fc, query2fa, query2fb, query3fa, query3fb

in your macro, append all your queries to their new tables with the autonumbers. then run another query appending each of these NEW tables with their autonumbers to your BIG tables. The autonumbers will be appended to the number ID field.

THEN, your output to excel query will GROUP BY the number ID field from all of the results.

After that, in your macro, clear out all the NEW tables with your BIG table.

Before you run it again, you will need to compact the database, so all the AUTONUMBERs will reset and start with #1 again.

Kind of complicated, but I like it.

 

[jobb]

"then run further queries appending each of these NEW tables with their autonumbers to your BIG table"

 

[jobb]

one more edit, because I don't want you to misunderstand:

"THEN, your output to excel query will GROUP BY the number ID field from all of the results in your BIG table. So, in your TOTALS query, GROUP BY the number ID, then the MAX of all other fields. You can click on properties for your queries fields to change the CAPTION name that will appear in your output"