By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,963 Members | 1,902 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,963 IT Pros & Developers. It's quick & easy.

most recent records

P: n/a
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

Oct 21 '06 #1
Share this Question
Share on Google+
9 Replies


P: n/a
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.
Oct 21 '06 #2

P: n/a
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
Oct 21 '06 #3

P: n/a
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
Oct 22 '06 #4

P: n/a
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

Oct 23 '06 #5

P: n/a
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
Oct 24 '06 #6

P: n/a
>
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

Nov 2 '06 #7

P: n/a
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
Nov 3 '06 #8

P: n/a
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
Nov 3 '06 #9

P: n/a
I am in awe!

Thanks,Shumit

Nov 6 '06 #10

This discussion thread is closed

Replies have been disabled for this discussion.