trouble with query

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.

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/WileyT...764559036.html


"Gary B" wrote in message
...
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.

On Mon, 24 May 2004 23:59:12 -0400, "Gary B" wrote:

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 W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

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.

Gary B wrote:

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.