Add results of 2 queries using 3rd query??? Don't get expected results

[INeedAccessHelp]

Hello everybody

I'm attempting to add the results of 2 queries using a 3rd. Here is an example:

Query_1:
Counts (by using Total in the design grid) the number of ID's in Table_1

Query_2:
Counts (by using Total in the design grid) the number of ID's in Table_2

Query_3:
Adds the results of Query_1 and Query_2 using this expression:

Expr1: [Query_1].[CountOfID]+[Query_2].[CountOfID]

Query_3 works fine AS LONG AS Query_1 AND Query_2 returns something. Say Query_1 does not return anything but Query_2 does. Because Query_1 did not return anything, Query_3 does not return anything even though Query_2 did.

I need Query_3 to return something (at least 0) even if one or both of the other queries do not return anything. I hope this makes sense.

Any help would be greatly appreciated. Thanks in advance for your help.

--Rob

 

[Smitty020]

Try using UNIONS!

Query1 Union All Query2

hth,

Smitty

 

[INeedAccessHelp]

Thanks Smitty, but I don't think a UNION will work here. The union query returns the count FROM EACH query as a separate record. I need Query_3 to ADD the results of Query_1.CountOfID and Query_2.CountOfID and return the total. Does this make sense?

 

[Smitty020]

Well then do the Union Query. Then in another query, total up CountofID FROM Query3. Seems a little messy, but I think that would work. Are you using Access?? Your orginal idea should work also though. If you want to post the SQL code, I'll take a look at it.

Smitty