DCOUNT

[pgh2377]

I have a patient table name "PATIENTS" and contains 3 fields:

CLIENT
PATIENT ID
SALES

Using access, because I don't know SQL, how do I group the Client, counting unique patients and summing sales. The patient may be in there twice for two different sales. I've been trying DCOUNT but having problems. Any suggestions.

 

[PHV]

SELECT CLIENT, Count(*) AS CountOfPatient, Sum(SumOfSales) AS TotalSales
FROM (SELECT CLIENT, [PATIENT ID], Sum(SALES) AS SumOfSales
FROM PATIENTS GROUP BY CLIENT, [PATIENT ID]) AS S

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244