Counting Distinct Entries

[Rick Deemer]

I am trying to create a query that traces our sales in the following manner:

Sorted by Zipcode
Sorted by Dr. Name
Sorted by Patient Name
Totalled by Dr. Name
Then counting the distinct number of patients per Dr. Name

The problem that I am running into is when I try and get the total number of
patients I can only get it seems the total number of records not necessarily
the patients. What I would like to do is then create a crosstab query and
send it to Excel for the marketing reps to view. My issue is with getting
the distinct number of patients per Dr. Name. I have tried to use the 2
queries one to count distinct and another for the rest of the information
but apparently I am not understanding that quite so well.

I have read many posts on items similar to this but have not yet found an
answer.

Also can someone recommend a good reference for Access? Not a basic book but
something more intermediate to advanced. Currently I am using 2K.

Thank you

 

[G. Vaught]

In the report set the Sorting and Grouping select the Dr Name field and set
the Header and Footer to Yes. This will allow you to count the number of
patients per doctor. You can also set the order of you sorting here also.
You can sort up to 4 fields (going by memory)

 

[Guest]

You'll need to use a correlated subquery. Without knowing what tables are
involved I can't give you a complete solution, but here's a simple query with
subquery which would sum sales amounts per doctor and count patients per
doctor:

SELECT Doctors.DoctorID, Doctor, SUM(Amount) AS TotalSales,
(SELECT COUNT(*)
FROM Patients
WHERE Patients.DoctorID = Doctors.DoctorID) As NumberOfPatients
FROM Doctors INNER JOIN Sales
ON Doctors.DoctorID = Sales.DoctorID
GROUP BY Doctors.DoctorID, Doctor;

As regards books for Access 2000 take a look at the Access 2000 Developers
Handbook by Ken Getz et al (published by Sybex). Its not a reference as
such, but is a must-have for any serious Access developer. The latest
version comes in 2 volumes, Desktop and Enterprise, but I'm not sure about
that for Access 2000 (I jumped from the Access 97 edition to the Access 20o2
edition).