Better Example of Count() Query

[Gary B]

MASTER Table: CHARGES
Fields: ChargesKey, ChargesDate
Data: Row1 = 1, 05/25/2004

DETAIL Table: SERVICES
Fields: ServicesKey, ChargesKey, ServicesDate, SalesRepKey
Data: Row1 = 1, 1, 05/25/2004, 5
Row2 = 2, 1, 05/25/2004, 5

I need to return the Count of CHARGES(ChargesKey) for each SalesRepKey. In
the above example, the result should be 1

 

[Michel Walsh]

Hi,

In Northwind, the following will count, by country, the number or distinct
cities and the total number of records (by country):



TRANSFORM COUNT(*) As countCity
SELECT Customers.Country,
COUNT(countCity) As DistinctCount,
COUNT(*) As TotalCount
FROM Customers
GROUP BY Country
PIVOT city IN( NULL)


(adapted from an original presentation by Steve Dassin).


In your case, it seems country <-> SalesRepKey and city <-> ChargesKey.



Hoping it may help,
Vanderghast, Access MVP