Help on Counting and grouping distinct records

[Metcare]

I have an 2 years worth of encounter data file that contains all records for
the office visits of patients. I would like to create a query to count the
no. of DISTINCT patients seen by patient ID ( as patients can be seen several
times).
I was trying to create a crosstab so I could see how many distinct patients
were seen by physicians by month. I am not successful as I am not sure how to
incorporate the distinct portion. I'm jsut a beginner in acccess. Appreciate
your help. Thanks

 

[Dale Fye]

Well, you can do this using a distinct clause in your query, and then create
a crosstab query based on that. But before you do that, you need to decide
whether you want to count patients multiple times if they are seen by
different physicians, or by the same physician in different months. My guess
is that the answer to these questions is yes. So I would start out with a
query that looks something like:

SELECT DISTINCT Format(VisitDate, "yyyymm") as Visit, PhysicianID, PatientID
FROM yourTable

You will probably want a WHERE clause in there to refine the period you are
looking at.

You can then use this query (either as a subquery or as a saved query) as
the source for your Crosstab, which would show you how many DISTINCT Patients
each physician saw during each month

 

[John Spencer MVP]

The easiest way is to use two queries. The first query would return the
distinct patientids by month.

SELECT DISTINCT PatientID
, Format(AppointmentDate,"yyyymm") as YearMonth
FROM YourTable

Now use that saved query as the source for your crosstab query.
TRANSFORM Count(PatientID)
SELECT Null as Blank
FROM TheSavedQuery
GROUP BY Null
PIVOT YearMonth


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Michel Walsh]

SELECT DISTINCT district, patientID
FROM originalTable



saved as query1, then


TRANSFORM COUNT(*)
SELECT "all"
FROM query1
GROUP BY "all"
PIVOT district


should do (assuming you have less than about 250 districts).



Vanderghast, Access MVP