Query to show totals from multiple tables

[dponce]

Hello again,
I have come across a brick wall in my database. I am looking for a Query to show me the combined information from multiple tables on one report. Each table has its own Query showing me the info I need but now I need to see all 14 of then combined. Such as Sum of totals and the company names from each table. These tables are not joined to each other, not sure if this helps.

Any suggestion would be appreciated.

[jaxjagfan]

Hello again,
I have come across a brick wall in my database. I am looking for a Query to show me the combined information from multiple tables on one report. Each table has its own Query showing me the info I need but now I need to see all 14 of then combined. Such as Sum of totals and the company names from each table. These tables are not joined to each other, not sure if this helps.

Any suggestion would be appreciated.

Make a Union query to pull all 14 totals together. You will have to switch the QBE to SQL view to build a Union query.

Select Sum(Query1.YourValue) as Total From Query1;
UNION
Select Sum(Query2.YourValue) as Total From Query2;
UNION
Select Sum(Query3.YourValue) as Total From Query3;
...

Then:

Select Sum(MyUnionQry.Total) From MyUnionQry;

You could also do this with 14 DSum's:

=DSum("[YourValue]","Query1")+DSum("[YourValue]","Query2") + ...

[dponce]

Thx for you help jaxjagfan,
I am not familiar with SQL but willing to try it out seeing that you generously provided the code for me.

I have entered the following to test it and even renamed my original Query’s to “Query1”, etc… and when I try to run it I get the following error message: Characters found after end of SQL statement.

Select Sum(Query1.Produced) as Total From Query1;
UNION
Select Sum(Query2.Produced) as Total From Query2;
UNION
Select Sum(Query3.Produced) as Total From Query3;
Then
Select Sum(MyUnionQry.Total) From MyUnionQry;

[dponce]

Thx jaxjagfan,
I just wanted to let you know that I was able to collect the information using your second method with the DSum. Works great, I really appreciated it. I would have still loved to learn how to make it work using the SQL format. Nothing big, if you get the chance or anyone else if you could educate me a little more on how to get this to work using the SQL UNION Query format. This is just incase I come across another one of these obstacles.

[jaxjagfan]

You would need to do the all of the Selects/Unions and save that as a query (I.E. qryMyUnion). That would gather all of the values together in one point to reference. You can make a query in the query designer and select qryMyUnion as the source - you don't have to use SQL statements.

I'm a SQL Server DBA - Don't work in Access a lot here but still like to help. I answer posts while running my daily DTS jobs. We post the SQL here as examples or solutions as to what the SQL would look like but a lot of what we post can be done in the query designer. No one wants to type step by step on what to do there.

HTH