UNIQUE COUNT

[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.

 

[Golom]

Try

Select T.Client, 
 
       (Select Count(*) From 
           (Select DISTINCT [Patient ID] From tbl As X
            Where X.Client = T.Client) As Dummy ) As [PatientCount],

        SUM ( T.Sales ) As [TotalSales]

From tbl As T

Group By T.Client