Unanswered: Linking Select Queries - Duplicate Problem
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;
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;