Hello -

I've built multiple select queries off of a master table. Each has a count of accounts that match the criteria, grouped by Date and Pool. Now I want to create a Make Table query that combines all of the select queries and rolls them up to get a Total.

I created a "Date Available" table that shows all available dates and then ran left joins to each of the select queries. Pulled the Date column from the Date Available table and then summed the count column from each of the select queries. Everything tied out except the one date that had records in 2 different pools. On April 20, there were 5292 records in Pool 538 and 92 records in Pool 638. When I roll it up to the Total, I want it to display 20100420 - 5384 records. With my current query, it's doubling the sum and displaying 10,768.

I assume this is a 1 to Many relationship problem but I'm not sure how to fix it.

Example Queries and results:

Query: OB Unique

SELECT [OB Unique Accts].Date, [OB Unique Accts].POOL, Count([OB Unique Accts].ACCOUNT_NUM) AS [Unique]
FROM [OB Unique Accts]
GROUP BY [OB Unique Accts].Date, [OB Unique Accts].POOL;

Results:
Date POOL Unique
20100401 538 4466
20100402 538 4142
20100403 538 2471
20100405 538 4407
20100406 538 4252
20100407 538 4194
20100408 538 4199
20100409 538 4938
20100410 538 1766
20100411 538 3243
20100412 538 4868
20100413 538 4845
20100414 538 4845
20100415 538 5930
20100416 538 4995
20100419 538 5910
20100420 538 5292
20100420 638 92



Query: OB Calls Dialed

SELECT [OB Calls].Date, [OB Calls].POOL, Count([OB Calls].ACCOUNT_NUM) AS Dials
FROM [OB Calls]
GROUP BY [OB Calls].Date, [OB Calls].POOL;

Results:
Date POOL Dials
20100401 538 21167
20100402 538 10304
20100403 538 8776
20100405 538 16510
20100406 538 14564
20100407 538 9684
20100408 538 10284
20100409 538 12500
20100410 538 4134
20100411 538 6645
20100412 538 11132
20100413 538 10549
20100414 538 11434
20100415 538 14116
20100416 538 11251
20100419 538 15410
20100420 538 12860
20100420 638 112



Query: Combined Example

SELECT [Dates Available].Date, Sum([OB Unique].Unique) AS SumOfUnique, Sum([OB Calls Dialed].Dials) AS SumOfDials
FROM ([Dates Available] LEFT JOIN [OB Unique] ON [Dates Available].Date = [OB Unique].Date) LEFT JOIN [OB Calls Dialed] ON [Dates Available].Date = [OB Calls Dialed].Date
GROUP BY [Dates Available].Date;

Results:
Date SumOfUnique SumOfDials
20100401 4466 21167
20100402 4142 10304
20100403 2471 8776
20100404
20100405 4407 16510
20100406 4252 14564
20100407 4194 9684
20100408 4199 10284
20100409 4938 12500
20100410 1766 4134
20100411 3243 6645
20100412 4868 11132
20100413 4845 10549
20100414 4845 11434
20100415 5930 14116
20100416 4995 11251
20100417
20100418
20100419 5910 15410
20100420 10768 25944


Any help would be greatly appreciated!

Thanks!