Minimize number of queries by Macro/VBA

[accessfever]

Hi, I have a 500K+ Access table which has detail information of 4 product type, tons of product names and 10 manufacturing locations. Currently, I have 40 queries to download the table by product type by locations in Excel and run a macro to execute the file downloading. I wonder if there is a way to minimize the 40 queries into just 4 queries (one query one product type) then use a macro or VBA code to breakout further by locations. I'm asking this since it is time consuming to update those 10 to 40 queries (one product type = 10 download files) when there is a request to change the download files' layout.

 

[boblarson]

My question is how different are your queries? Are they different just in criteria? Or at least are several like that? You don't need to build queries for each criteria. You don't even need to have a query for each product type really (unless you have a non-normalized structure where there are specific fields for specific products).

So, the question is what exactly do your queries look like?

 

[accessfever]

The query A and query B are in same layout in Excel except product type is different. The query C and query D are in same layout in Excel but different from quary A and B.

 

[boblarson]

I meant what is the SQL of them.

 

[accessfever]

The SQL code for each query is quite long since it tries to call out about 20 fields from the original table. I just came up with an idea if it is working or not:
A) Create just a function named "GETFIELDS" to select fields except the critera for locations and product types.
B) Then use a Click event to include the function "GETFIELDS" then add additional critera (IF ) for one location and one product type. Then export the output to an Excel file.
C) Repeat the criteria (ELSE) to download all outputs to Excel files.

Would it be working?

 

[gemma-the-husky]

however many queries you have shouldn't matter, surely.

can you not code them all onto a single button event?