COunting patients visits

N

Nir N

I have a database detailing patients visits. In each visit I take patient's
photos, so each patient has many records for the same visit.
I created a query using the "unique values" property that outputs only one
record per patient's visit:

SELECT DISTINCT dbo_M2000.LASTNAME, dbo_M2000.FIRSTNAME, dbo_M2000.PIN,
dbo_IMAGE.PHOTODATE
FROM dbo_M2000 INNER JOIN dbo_IMAGE ON dbo_M2000.RECNUM = dbo_IMAGE.LINKNUM
ORDER BY dbo_M2000.LASTNAME;

Now, I need to count how many patients visited only once, how many patients
visited 2 times etc. I don't need the patient's names- only the summery
numbers (or a bar chart). How can I do it?

Thanks,


Nir
 
W

Wolfgang Kais

Hello "Nir N".

Nir N said:
I have a database detailing patients visits. In each visit I take
patient's photos, so each patient has many records for the same
visit. I created a query using the "unique values" property that
outputs only one record per patient's visit:

SELECT DISTINCT dbo_M2000.LASTNAME, dbo_M2000.FIRSTNAME,
dbo_M2000.PIN, dbo_IMAGE.PHOTODATE
FROM dbo_M2000 INNER JOIN dbo_IMAGE ON dbo_M2000.RECNUM =
dbo_IMAGE.LINKNUM ORDER BY dbo_M2000.LASTNAME;

Now, I need to count how many patients visited only once, how many
patients visited 2 times etc. I don't need the patient's names-
only the summery numbers (or a bar chart). How can I do it?

Let's assume that you query was named "PatientVisits".
You could create another query based on "PatientVisits" calculating
the number of visits per patient ("NumVisitsPerPatient"):

Select LastName, FirstName, PIN, Count(*) As NumPatVisits
From PatientVisits Group By LastName, FirstName, PIN

And then the query you want:
Select NumPatVisits, Count(*) As NumberOfThisCountVisits
From NumVisitsPerPatient
Group by NumPatVisits
 
N

Nir N

Thanks!

Nir

Wolfgang Kais said:
Hello "Nir N".



Let's assume that you query was named "PatientVisits".
You could create another query based on "PatientVisits" calculating
the number of visits per patient ("NumVisitsPerPatient"):

Select LastName, FirstName, PIN, Count(*) As NumPatVisits
From PatientVisits Group By LastName, FirstName, PIN

And then the query you want:
Select NumPatVisits, Count(*) As NumberOfThisCountVisits
From NumVisitsPerPatient
Group by NumPatVisits
 

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