Totaling a query

S

Stefan

I have a table with patient visits. Each visit may have multiple procedures.

ex j smith
1/1/07 procedure 97110
1/1/07 procedure 97140

I Want to count the total number of visits the patient has had.

I have a group by query which list the patient and groups the dates no
matter how many procedures are performed on a day which works fine

results of this query are

j smith 1/1/07
j smith 2/1/07
j smith 3/1/07

So the patient has had total of 3 visits.

I would like the query to return the total number of visits only

j smith 3

I know I can do this in a report but would like to get the values in the
query itself.

I need this number to use as a filter in other querys. Basically if a
patient has a certain insurance they may only be allowed 9 visits before they
need a new authorization. I would like to flag the outgoing claims before we
go past this number.

The office staff frequently misses these issues so this will reduce our
denials
 
J

John Spencer

Field names would help

Preliminary query to get only unique combinations of PatientId and
Visitdate
SELECT Distinct PatientID, VisitDate
FROM YourTable

Now it is easy using that saved query to get an accurate count of visits for
any specific period

SELECT PatientID, Count(VisitDate)
FROM TheSavedQuery
WHERE VisitDate Between #1/1/07# and #9/30/07#
GROUP BY PatientID
HAVING Count(VisitDate) > 9

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
S

Stefan

Thanks worked perfect
--
Thanks in advance
Stefan


John Spencer said:
Field names would help

Preliminary query to get only unique combinations of PatientId and
Visitdate
SELECT Distinct PatientID, VisitDate
FROM YourTable

Now it is easy using that saved query to get an accurate count of visits for
any specific period

SELECT PatientID, Count(VisitDate)
FROM TheSavedQuery
WHERE VisitDate Between #1/1/07# and #9/30/07#
GROUP BY PatientID
HAVING Count(VisitDate) > 9

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top