Combining Multi-step Queries - best practice

[Rx_]

Here is a question that may not have one single answer, but here goes.

In building a Excel Report off of somewhat normalized data.

By using a combination of 7 queries (Cartsian, Group-by, and joins) the result is a recordset ready to paste (with vba automation of course) into Excel.

The question now is:
How to automate the 7 query steps?

1. Call them withe VBA and create temp tables, with the next query step calling the temp table as the source and creating the next one, until the final table is ready.
2. Some code I was recently maintanining, the person was really good at using an embedded Select statement where ; next Select statement...
3. Just run the last query and let it call the others down the chain. Would there be any considerations to move these queries from the front-end to the back end?

Access 2007 back end - abour 3,000 records. Around 15 users that run this report twice a day.

Suggestions are welcome

 

[pbaldy]

I suppose it would depend on the specifics, but I would be highly unlikely to use temp tables. I would typically use option 3. Option 2 is okay, but can be difficult to maintain as things get more complicated.

 

[gfultz]

I just did something very similar to this, merging data from different systems, summing sum based on other criteria, etc. I used option 3, building all of the queries and merging them into one. It works efficiently and sorts through about 15k records in one table pretty efficiently. In fact it runs a heavily formatted report that can be up to 6 pages in about 2 to 3 seconds.

 

[Rx_]

Thanks!
After designing the seven steps into business discrete blocks, the ability to use a front end-form so the user can customize each step (customers, region, dates, types, status...) worked out nicely. On my old workstation, it runs in one second. It will run on a Windows 2003 Citrix server much faster.
For Query naming conventions, I used 1_TeamReport_ CustomerList, 2_TeamReport_ PermitTypes, 3_TeamReport_ TimePeriods...

In a past maintenance contract with SQL Server linked tables, some of the data intelligence pulled megs of data transactions, thousands of locations and market positions to crunch down to a 200Kbyte Excel worksheet. The temp tables were efficient.

The answer "it depends" is always the right answer!
Thanks very much for you inputs.

 

[namliam]

Temp tables are hardly ever the best solution
Nested queries are 'nice and all' but can be a maintenance nightmare.
Stacked queries can be a concern but ... IMHO, most likely in most cases will be a higly likely out come for any such question you have here.