Count and Group By two fields in a Query 3

[Comp2008]

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.

Comp2008

 

[AndyInNC]

It might be easiest to simply select the data straight; no Counts or Group Bys in the query. Then do the grouping and counting in the report itself. Create 2 groups: one for the Location (show the header) and one for the Customer (show the footer).

Add the Customer & Appointments into the detail. (Give the textboxes sensible names like txtCustomerName otherwise Access can get confused.) In the Customer footer, add another CustomerName textbox along with a textbox with the formula =Count([CustomerName]) and any other counts or sums you may need.

Then set the Detail band to be invisible. This should get you pretty close.

 

[Comp2008]

AndyInNC,
I followed your recommendations and It worked very well. Thank you for the input. I really appreciate it.

Comp2008.

 

[dhookom]

It's appropriate to give a star to someone who answers your question. This also gives other TT'rs a visual indication that a question has been answered.

Duane
Hook'D on Access
MS Access MVP

 

[Comp2008]

Thank you for the observation

Comp2008