Counting patient encounters by provider

[Guest]

I need to count the total number of patient visits accumulated by each
provider in a medical practice between selected dates. I am using the table
of charges. Each visit may include several individual charges during the
visit, but I want to count the visit only once. Therefore I used this
statement (the date range is not actually hard coded):

SELECT DISTINCT ProviderCode, PatUniqueID, service_date
FROM Charges
WHERE service_date Between '01/01/1995' And '12/31/2005' ;

What I get is:

Provider Code PatUniqueID service_date
---------------- -------------- --------------
RMB 25AAAAAA 01/01/2005
RMB 983AAAAA 02/01/2005
etc.

However, what I readlly want is a count. How many of these lines occur for
provider RMB and each provider. The desired table would be:

Provider Code Encounter Count
---------------- -------------------
RMB 25
JDR 51
etc.

Can anyone help me to write this statement, please.

 

[Jeff Boyce]

Richard

Take a look at the query designer window in Access. There's a "Totals"
query that allows you to GroupBy Provider, and Count PatientID.

You might need to first run your query to get unique instances for each
date, then create a new query that does the "Totals".

 

[Guest]

I don't see a Totals Query in the query designer window of my Access (part of
Office XP). I know how to do this with two queries, or I could use a totals
only report. However, I'm trying to get my result in a single Pass Through
query.

 

[John Vinson]

I'm trying to get my result in a single Pass Through
query.

Pass Through into... what? If it's a pass-through query then the
syntax of Access is irrelevant, since you're explicitly not using it!

If it's truly a PassThrough query (say into SQL/Server) then you would
need to code your totals query in whatever dialect of SQL your server
supports.

In Access (as a native Access query rather than a pass-through query)
you would click the Greek Sigma icon on the toolbar to make the query
into a Totals query.

In most dialects of SQL you can use something like

SELECT ProviderCode, Count(*)
FROM Charges
WHERE service_date Between '01/01/1995' And '12/31/2005' ;

though in Access SQL you'll need # rather than ' to delimit the
date/time fields.

John W. Vinson[MVP]

 

[John Spencer (MVP)]

Since you want a unique count. You might try using your query as a "virtual table?


SELECT Tmp.ProviderCode, Count(tmp.PatUniqueID) as PatientCount
FROM
[SELECT DISTINCT ProviderCode, PatUniqueID, service_date
FROM Charges
WHERE service_date Between #01/01/1995# And #12/31/2005#]. As Tmp
GROUP BY Tmp.ProviderCode