trouble with query

[Gary B]

MASTER
Table: CHARGES
Fields: ChargesKey, ChargesDate

DETAIL
Table: SERVICES
Fields: ServicesKey, ServicesDate, SalesRep

I need to return the Count of CHARGES(ChargesKey) for each SalesRep

Thank you.

 

[Graham R Seach]

Gary,

Untested - but this should do it:

SELECT Count(CHARGES.*) As CountOfCharges,
SERVICES.SalesRep
FROM CHARGES
INNER JOIN SERVICES
ON SERVICES.ServicesKey = CHARGES.ChargesKey
GROUP BY SERVICES.SalesRep

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

 

[Gary B]

MASTER
Table: CHARGES
Fields: ChargesKey, ChargesDate

DETAIL
Table: SERVICES
Fields: ServicesKey, ChargesKey, ServicesDate, SalesRep

I need to return the Count of CHARGES(ChargesKey) for each SalesRep

Thank you.

 

[John Vinson]

MASTER
Table: CHARGES
Fields: ChargesKey, ChargesDate

DETAIL
Table: SERVICES
Fields: ServicesKey, ChargesKey, ServicesDate, SalesRep

I need to return the Count of CHARGES(ChargesKey) for each SalesRep

Thank you.

SELECT SalesRep, Count([ChargesKey]) FROM Services GROUP BY SalesRep;

In the query grid, add just the Detail table; add the SalesRep and
ChargesKey fields; click the Greek Sigma icon (like a sideways M), and
change the default GroupBy under ChargesKey to Sum.

 

[John Spencer (MVP)]

This could be a two-query problem.

Query one.

SELECT Distinct ChargesKey, SalesRep
FROM Detail

Save that as qGetUnique and then

SELECT SalesRep, Count(ChargesKey)
FROM qGetUnique
GROUP BY SalesRep

In later versions of Access you can combine this into one query using a subquery.