Count Problem in Query

Z

Zarko

I use Access to condense 50,000-80,000 records with 15 fields into forms to
create reports based on 10 of the fields. In my query, I use 4 other tables
to rename data and classify certain information. On the two fields with
currency, I use a SUM total, and on the product number (primary) field I use
a count option.
For the current report I am trying to create, I am pulling only the records
associated with one region. There are 6,475 unique records associated with
the region. However, when I use the count function on the product number, I
am getting 29,697. I have not been able to figure out why this is happening.
I haven't had this problem since I started this project. Does anyone have any
ideas?
 
Z

Zarko

Thought maybe this will help...

SELECT [Region Mapping].Region, [Submissions 2005 @ 7-21-2005].[Profit
Center], [Submissions 2005 @ 7-21-2005].[Line Of Business], [Submissions 2005
@ 7-21-2005].[Product Type], [Marsh Wholesaler Split].Group, [Processing
Dates].Month, [Processing Dates].Year, [Processing Dates].Quarter,
Sum([Submissions 2005 @ 7-21-2005].[Estimated Premium]) AS [SumOfEstimated
Premium], Sum([Submissions 2005 @ 7-21-2005].[Exp Premium]) AS [SumOfExp
Premium], [Status Mapping].Status, Count([Submissions 2005 @
7-21-2005].[Product Number]) AS [CountOfProduct Number], [Specialty Brokerage
Products].[SBD Sub Group]
FROM (((([Submissions 2005 @ 7-21-2005] LEFT JOIN [Region Mapping] ON
[Submissions 2005 @ 7-21-2005].[Puc Name] = [Region Mapping].Field1) LEFT
JOIN [Specialty Brokerage Products] ON [Submissions 2005 @ 7-21-2005].[Line
Of Business] = [Specialty Brokerage Products].[Line Of Business]) LEFT JOIN
[Processing Dates] ON [Submissions 2005 @ 7-21-2005].[Effective Date] =
[Processing Dates].Day) LEFT JOIN [Status Mapping] ON [Submissions 2005 @
7-21-2005].Status = [Status Mapping].Field1) LEFT JOIN [Marsh Wholesaler
Split] ON [Submissions 2005 @ 7-21-2005].[Producer Name] = [Marsh Wholesaler
Split].[Producer Name]
GROUP BY [Region Mapping].Region, [Submissions 2005 @ 7-21-2005].[Profit
Center], [Submissions 2005 @ 7-21-2005].[Line Of Business], [Submissions 2005
@ 7-21-2005].[Product Type], [Marsh Wholesaler Split].Group, [Processing
Dates].Month, [Processing Dates].Year, [Processing Dates].Quarter, [Status
Mapping].Status, [Specialty Brokerage Products].[SBD Sub Group]
HAVING ((([Region Mapping].Region)="Specialty Brokerage") AND (([Status
Mapping].Status)<>"Exclude"));
 
Top