COunting patients visits

[Guest]

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

 

[Wolfgang Kais]

Hello "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?

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

 

[Guest]

Thanks!

Nir

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