most recent records

[shumit]

Hi

I have a patient table with patient names and visiting dates. I want to
select those patients who have visited me at least 6 times and show the
dates that they visited ordered by name. I have managed this by
creating another table but Im sure it can be done with a couple of
queries.

thanks,Shumit

[salad]

sh****@eskatongardens.com wrote:

Hi

I have a patient table with patient names and visiting dates. I want to
select those patients who have visited me at least 6 times and show the
dates that they visited ordered by name. I have managed this by
creating another table but Im sure it can be done with a couple of
queries.

thanks,Shumit

You could create a column in your query that stores the counts using
Dcount(). Ex:
VisitCount : NZ(Dcount("PatientID","Patients","PatientID = " &
[PatientID]),0)

It's possible you have a patient name but havent entered the visit
data...thus I added the NZ to force a 0 to be displayed.

[Smartin]

sh****@eskatongardens.com wrote:

Hi

I have a patient table with patient names and visiting dates. I want to
select those patients who have visited me at least 6 times and show the
dates that they visited ordered by name. I have managed this by
creating another table but Im sure it can be done with a couple of
queries.

thanks,Shumit

If all the information is in one table, this should do it:

SELECT PatientName, Visit
FROM Patients P1
WHERE P1.PatientName IN
(SELECT PatientName
FROM Patients
GROUP BY PatientName
HAVING COUNT(Visit) >= 6)
ORDER BY PatientName, Visit;

As an aside, if you are in fact storing patient information (name, DOB,
gender, etc) in the same table as visit information (date of visit,
reason, charges, etc.) it should be said this is generally considered
poor database design, and you think about normalizing the data structure.

Let us know if you have any questions.
--
Smartin

[AA Arens]

Take a look at this:

http://groups.google.com/group/comp....frm/thread/e11
90eba19e4e62f/4671fb0f3a8758e9?lnk=gst&q=aa+arens&rnum=1#4671fb0 f3a8758e9

Smartin wrote:

sh****@eskatongardens.com wrote:

Hi

I have a patient table with patient names and visiting dates. I want to
select those patients who have visited me at least 6 times and show the
dates that they visited ordered by name. I have managed this by
creating another table but Im sure it can be done with a couple of
queries.

thanks,Shumit

If all the information is in one table, this should do it:

SELECT PatientName, Visit
FROM Patients P1
WHERE P1.PatientName IN
(SELECT PatientName
FROM Patients
GROUP BY PatientName
HAVING COUNT(Visit) >= 6)
ORDER BY PatientName, Visit;

As an aside, if you are in fact storing patient information (name, DOB,
gender, etc) in the same table as visit information (date of visit,
reason, charges, etc.) it should be said this is generally considered
poor database design, and you think about normalizing the data structure.

Let us know if you have any questions.
--
Smartin

[shumit]

If all the information is in one table, this should do it:

SELECT PatientName, Visit
FROM Patients P1
WHERE P1.PatientName IN
(SELECT PatientName
FROM Patients
GROUP BY PatientName
HAVING COUNT(Visit) >= 6)
ORDER BY PatientName, Visit;

As an aside, if you are in fact storing patient information (name, DOB,
gender, etc) in the same table as visit information (date of visit,
reason, charges, etc.) it should be said this is generally considered
poor database design, and you think about normalizing the data structure.

Thanks for your reply but I omitted to state that I only want the last
6 records for each patient. My tables are separate, I am using
patientID to link between the visits table and the patients table,

thanks,Shumit

[Smartin]

sh****@eskatongardens.com wrote:

>If all the information is in one table, this should do it:

SELECT PatientName, Visit
FROM Patients P1
WHERE P1.PatientName IN
(SELECT PatientName
FROM Patients
GROUP BY PatientName
HAVING COUNT(Visit) >= 6)
ORDER BY PatientName, Visit;

As an aside, if you are in fact storing patient information (name, DOB,
gender, etc) in the same table as visit information (date of visit,
reason, charges, etc.) it should be said this is generally considered
poor database design, and you think about normalizing the data structure.

Thanks for your reply but I omitted to state that I only want the last
6 records for each patient. My tables are separate, I am using
patientID to link between the visits table and the patients table,

thanks,Shumit

OK I am not the best at subqueries, but I will work on this because I
love this sort of pain.

I hope no one has a textbook answer (^:

--
Smartin

[shumit]

>

OK I am not the best at subqueries, but I will work on this because I
love this sort of pain.

I hope no one has a textbook answer (^:

--
Smartin

Its been a week now - This must be pretty painful !

Shumit

[Smartin]

sh****@eskatongardens.com wrote:

>OK I am not the best at subqueries, but I will work on this because I
love this sort of pain.

I hope no one has a textbook answer (^:

--
Smartin

Its been a week now - This must be pretty painful !

Shumit

Yes, if you could send something for the pain, and the swelling too LOL...

Actually, I kinda of track of this. I'll put it back on my to-do list.

--
Smartin

[Smartin]

sh****@eskatongardens.com wrote:

>OK I am not the best at subqueries, but I will work on this because I
love this sort of pain.

I hope no one has a textbook answer (^:

--
Smartin

Its been a week now - This must be pretty painful !

Shumit

OK, here you go. I hope you can use this.

Explanation follows SQL:
SELECT V1.VisitID, V1.PatientID, V1.VisitDate
FROM Visits AS V1

WHERE V1.VisitDate IN
(SELECT TOP 6 V2.VisitDate
FROM Visits AS V2
WHERE V1.PatientID = V2.PatientID
ORDER BY V2.VisitDate DESC)

AND V1.PatientID IN
(SELECT V3.PatientID
FROM Visits AS V3
GROUP BY V3.PatientID
HAVING COUNT (V3.VisitID) >=6)

ORDER BY PatientID, VisitDate DESC;
The Visits table consists of PatientID (which you can join to your
Patients table for more patient details) and VisitDate. VisitID is
totally optional here. I include it to demonstrate the uniqueness of
returned rows.

The first subquery limits the returned rows to the 6 most recent visits
per patient, however it allows for patients that have fewer than 6 visits.

Caveat: If there are ties on VisitDate within the top 6 you may get more
than 6 rows returned per patient. TOP works that way...

The second subquery further limits the returned rows to only include
those patients that have 6 or more visits.

This runs in < 1 sec against my table of 5000 records, YMMV. There may
be other solutions.

Regards,

--
Smartin

[shumit]

I am in awe!

Thanks,Shumit