trying to create a sum query with multiple tables

[Buddha1]

I am trying to pull together data that has been summed from multiple tables. Example: Tbl1 has four fields: GROUP,DIVISION,PAYCODE,CURRENTMONTHTOTALS. Tble2 has four fields: GROUP,DIVISION,PAYCODE,MONTH1TOTALS. The only column that changes from table to table is the last field which is the sum field. I would like for the end report to look like this:

GROUP DIVISION PAYCODE CURRENT MONTH1 MONTH2 MONTH3 MONTH4
SUM SUM SUM SUM SUM

 

[GoDawgs]

If group, division, and paycode are all the same type in each table then you could just link those three columns and bring in the sum columns from however many tables you need. Hope that helps.

Kevin

 

[Buddha1]

unfortunately
There may be a balance in month1 and month3 for BlueCross but not in month4 or month5.
Is there any way of doing it?

 

[GoDawgs]

You'd need a master table that would contain all of the possible group, division, and paycode combinations...then link from there to the other tables (click on the links and make them show all from the master table). Then you would show the entire master table and just drop in the sum fields from each of the other tables.

 

[JonFer]

Select Distinct GROUP,DIVISION,PAYCODE
from tbl1
Union
Select Distinct GROUP,DIVISION,PAYCODE
from tbl2
...

This will generate the unique combinations of Group, Division and Paycode from all of the tables. Use this as the Master table suggested by GoDawgs and link the other tables to it (outer join). The query design window will look rather interesting with 36 join lines and 13 tables.