Multiple Queries to 1 Report

You probably need a union query. Create a new query but don't select any tables for the query. Then go to the toolbar and select Query / SQL Specific / Union. This opens a window that is waiting for SQL instructions. To combine three separate queries into a single result, the following commands would be needed for a query on a table named "Sites" that contains a field labeled "Name". Name can contain a variety of items that include three base names: Dell, Gateway and Microsoft. Here are the SQL instructions for the union query:

SELECT "Dell" AS Site, Count(Sites.Name) AS [Count]
FROM Sites
WHERE (((Sites.Name) Like "*Dell*"))

UNION ALL SELECT "Gateway" AS Site, Count(Sites.Name) AS [Count]
FROM Sites
WHERE (((Sites.Name) Like "*Gateway*"))

UNION ALL SELECT "Microsoft" AS Site, Count(Sites.Name) AS [Count]
FROM Sites
WHERE (((Sites.Name) Like "*Microsoft*"));

Please notice that the 2nd, 3rd, etc. Select commands start with UNION ALL to connect the statements together. Also, only the last statement ends with a semi-colon. This arrangement can be any length desired as long as each Select statement returns the same number of fields (in this case, two visible and one not visible) and all fields names are identical (in this case, Site and Count for the visible fields and Name for the hidden field created by the Where statement).

- Jim

Yes, the union query will work with any arrangement of select queries as long as the first is written in a normal fashion (with no semicolon at the end) and each added query starts with Union All. Only the last query statement ends with a semicolon.

Write each query separately using the Access tools. Copy the SQL statements of each into a new Union query and edit it to eliminate the semicolons and add the Union All statements.

The Union query must be created as an SQL query because Access doesn't provide the query builder interface for this special query.

- Jim