431,780 Members | 1,550 Online
Need help? Post your question and get tips & solutions from a community of 431,780 IT Pros & Developers. It's quick & easy.

# Count and Group By two fields in a Query

 P: 58 How can I Count the number of Customers that a Sales Person had in a City X? The idea is to Group By [Location Name] and Sub Group By [Sales Person], and then Count the rest of the fields. This is the scenario: [Location Name][Sales Person].[CountOfCustomers]..etc CITY1 .......Steve K..25 .John M..15 ...Dianne L.26 ..CITY 2 ......Scott P.36 ..Steve N....24 Christian G....10 So far, I can Count (in this case Customer Name or any other field) if I Group By only one field, for instance: Code: SELECT [Sales Table].[Location Name], Count([Sales Table].[Customer Name]) AS [CountOfCustomers], Count([Sales Table].[Appointments]) AS [CountOfAppointments] FROM [Sales Table] GROUP BY [Sales Table].[Location Name]; The problem I have is, when I Group By two fields, the Count is 1 when the record is duplicated. Any help will be greatly appreciated. Thank you in advance. plaguna Oct 16 '08 #1