Hello, thnaks for having a look.

I have worked this query a number of ways but just can't get wnat I want.  3 Columns (veterinary):  
ClinicName, PatientID, Amount

I'm trying to SUM the Amount, COUNT the DISTINCT PatientIDs and group them by ClinicName.  Sounds easy enough...

Here's the hbest I can do, in two queries:

THE COUNT:
SELECT Count(*) AS Patients, ClinicName
 FROM (SELECT DISTINCT PatientID, Name FROM Data ORDER  BY Name, PatientID)

GROUP BY Name
ORDER BY Name;

THE SUMS:
SELECT Name, Sum(Amount)
FROM Data
GROUP BY Name;

Is there a way to combine these into one query.  I was think maybe a JOIN of some kind.  In the first query, if I add the Sum(Amount) to the sub-select then I loose the distinct count of the PatientIDs.

I'm baffled.
thanks again

Sample Data

Name               PatientID          Amount
Vet Pets             100              350.00
Vet Pets             100              150.00
Bowsers              101               99.00
Bowsers              102              100.00

Desired Results

Clinic             Patients           Amount     
Vet Pets              1               500.00
Bowsers               2               199.00


 

---

Answer from Richard Rost:

You can EASILY do this with an aggregate query. Turn on TOTALS and then select Group By for clinic name, Count for patient id, and Sum for amount. If you then need the SQL from it, switch over to SQL view and there you go.

To do this, use an Aggregate Query... also called a Summary Query or Totals Query. There is a little button on the query toolbar that looks like a wierd "E" (actually the Greek letter Sigma). Press that and you get a new Total row. Just add the fields you want to group by (like customer name or ID) and the fields you want to run calculations on (like SUM, Count or Average) and there you go.

I have a FREE tutorial on my web site that teaches you how to create an Aggregate Query. See:

http://599cd.com/tips/access/aggregate-query?key=BlFAlEx

Enjoy!