Sum two columns in different tables

[thurlob]

So the database this user was using was made in Office 97. The query to sum two different columns in two different queries worked fine. Then we upgraded her to Office 2003 and converted the database and it no longer worked.

Here is what I need to do:

In total query there is a sum of account and sum of extended column that sums everything in the account and extended columns from table1.
In total a query there is a sum of account and sum of extended column that sums everything in the account and extended columns from table2.

In my grand total query I want two figures returned...one will be the sum of account columns from both total and total a queries. And the other figure will be the sum of extended columns from both total and total a queries.

I have tried this numerous different ways and always get the same result...a blank returned in the grand total query. From some research I've done this could be because one of the tables/queries frequently returns no data?

Thanks for your help!

 

[cpremo]

Have you tried creating two separate queries to obtain the "Sum Totals" to test if your logic is working? Are the two tables linked by another table or to each other? Is Table1 the one side and Table2 the many side?

Can you provide a stripped down version of your database with just the tables and the queries?

 

[thurlob]

Grand total database

I am attaching the .mdb file.

This is just a sample database and it doesn't include the grand total query that I'm trying to make. If someone could just take a look and give me some ideas I would appreciate it...thanks! Sometimes the tables will have numbers in them, sometimes they will have nothing.

Betsey

 

[thurlob]

answer

I have two separate queries that sum the fields in the two separate tables.
What I need is one query that returns a two grand totals of those two fields in the two total queries.

Betsey

 

[cpremo]

Are you using another query to enter the data into these tables? It looks like you're using a query to "stuff" the "sumofaccount" and "sumofextended" figures. (Guessed from the titles of your fields.) You may be causing your self a lot of extra work. Do you compute these "Totals" from another table? Where does the data originate from?

What ties the two tables together??? Since the "ID" number is autogenerated, they have no relationship to each other. This in itself will cause you no end of problems down the road. If you are computing these totals from another table, it would be better to use some "ID" from that table if you have to maintain a "Fix" computation value in a table. Or can you compute the totals on the Fly any time you need to (basing the computation on a date range, PO Number range, etc.)?

 

[thurlob]

answer

The query 1 and query 2 total the sum of account and sum of extended from table 1 and table 2 respectively. There is really nothing that links the two tables so I think creating a relationship would just cause confusion.

Then I want a query that totals the sum of account from query 1 with the sum of account from query 2 and the same for sum of extended. This is the only way I know to total two different columns from two different tables into one grand total. My report then does a real grand total which is sum of account + sum of extended from query 3 (the grand total query).

Does that make this any more clear? If there's an easier way I'd love to hear it. This is just how it was done before in 97 and the query had an expression something like this: Sumofaccount: (query1.sumofaccount + query2.sumofaccount) but that isn't working in 2003 and I think it's because sometimes query 2 will return zeroes if there is no data in table 2 to sum.

Betsey

 

[thurlob]

clarification

To clarify a little...this is not the real table, names, etc. That is confidential HR information so I had to mimic the table so you could see what I"m trying to do.
No, the information is not stuffed into table 1 and table 2, it is entered manually. Just ignore the column names for the tables.

 

[cpremo]

Try this formula instead of the field name:

Account Sum: IIf(IsNull([sumofaccount]),0,[sumofaccount])

This accounts for Null entries. This will enable you to collect the total of each field. However, joining the two will not really be possible unless you have another field that "Links" the data together, such as a date range, location, or account range. Do you limit your range? If so by what?

 

[thurlob]

answers

So here is the query code from the real table.
What happens is...if total query returns zeroes...because the table it's based on is empty...then the grand total (code below) returns nothing even though there is information in total-b to sum.

Betsey

SELECT Sum([total]![SumOfamount]*[total - b]![SumOfamount]) AS Expr2, Sum([total]![SumOfoverhead]*[total - b]![SumOfoverhead]) AS Expr1
FROM total, [total - b];