How do i get a query to total the results of two queries

[Scott_Withey]

Hi,

I have two separte tables in my database, each have the same fields but one table holds current data and the second expired data. How do i get a query to count the total for both tables in an overall total

Thanks

 

[sonny]

Create a query on each table. In the query select the field you want to count the amount of records for, set it to count. Do the same for the second query for the other table. Then create a 3rd query that has the two fields(one from each query) and create a 3rd column where you add the two columns up. Below is the sql for the 3rd query.

SELECT [Query1]![CountOftest2]+[Query2]![CountOftest3] AS [Sum]
FROM Query1, Query2;

 

[sfreeman@co.mer]

I can name that code in 2 queries... :-B

Scott: the above answer is fine, but it can also be done is 2 queries.

1) Union query:

SELECT Table1.RecNo
FROM Table1;
UNION SELECT Table2.ID
FROM Table2;


2) Select:

SELECT Count(UnionQry.RecNo) AS CountOfRecNo
FROM UnionQry;