trouble with query

  • Thread starter Thread starter Gary B
  • Start date Start date
G

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.
 
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.
 
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.
Click to expand...

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.
 
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.
 
Back
Top