Is it possible to populate a table with multiple query results?

[gnatsnyder]

I am new to all of this and am really stumped. I have a report that needs to summarize a database based on several criteria. For example: query Q1 filters the table on criteria a, b, c, and d. This results in a few records but are are reduced to 1 row by the sum of column d. Q2 does the same thing except criteria b is now e. Q3 is the sum of both Q1.d and Q2.d. Here is my problem. I must repeat this process 160 times and include the results of the queries in 1 report. Could I populate a table results of the queries and use that table as the report's record source?

Any suggestions would be most welcome.

 

[jrbarnett]

Hi,

Yes, it's certainly possible to put query results into a new (or existing) table.
Access has what is called a Make Table query, which will create a new query based on the results of a select or union query. The table won't have a primary key or any other indexes set (unfortunately) which is a bad idea if you need to search or sort on anything, or join it to other tables, but you can always add it later in code.

If you are an SQL head, the code to create a make table query is:

SELECT table.field1, table.field2, table.field3
INTO newtable
FROM table
WHERE table.field1 = 1234

and you can add all the rest of the clauses - group by, having, union, order by etc.
This would create a table called "newtable" in the current database based on the result of "table" where field1 had the value 1234.

John

 

[gnatsnyder]

Thanks for answering.

I have a report that needs the results of 160 queries. I understand that the report must have a single record source. How do I create a single record source to hold the results of 160 queries?

 

[jrbarnett]

Make the first query a Make Table, as shown above.

Make all subsequent queries insert queries. This will insert the results of a select or union query into an existing table, provided that primary key or other restrictions on the table are not broken.

The SQL syntax for an insert query is as follows:

Insert into Tablename (Field1, Field2, Field3)
Select Field4, Field5, Field6
From Table2
Where Field4 = 1234

etc

By the time your 160 queries have executed, you will have one table which can be used as your record source.

However, I must ask: why are there 160 queries? Do a number vary only slightly in the code or the where clause?

John