Nbring same records showing > 1x sequencially

Closed
Vivian - Aug 27, 2010 at 02:42 PM
 Vivian - Aug 31, 2010 at 01:38 PM
Greetings,

My first experience asking a question on this forum was extremely helpful. So I'm trying my luck again. Many thanks to those who could help.

I have a table "Readmission" that contains patients' information - Access 2007. There is a field "UniqueID" for each patient each time a patient enters into the hospital. What the doctors and nurses wanted to find out is:
This is X out of X times a patient came back to the hospital.

For example:
Doctors may dicuss things like:
John Doe did these things and they improved his health on the 7th out of 10th visit that made a difference for the remainder of his chemotherapy.

I'd like to create "X out of X" in a query/queries. Is there anyway doing that? I don't want to do this in VBA - wouldn't know how. If someone could help me I'm most greatful.


7 replies

This would make more sense if it was written :
"This is the X out of Y times a patient came back to the hospital"

The Y value in this case is merely a COUNT of the number of uniqueid's
"select count(*) as no_of_visits
from readmission
where uniqueid = patient_no"
patient_no could be prompted for or could be a field in a form

Save this as a seperate query. Join it to the main query.

I'm not sure how you want this information to be presented. In a continuous form, report, just a query?
Depending on which the answer is would govern how to go about answering the rest of your question.
1
Hi Ray,

Thank you for the speedy response. Excellent suggestion on "X out of Y visit". It made my question clearer.

To answer your question, the outcome will be presented in query. May I ask how may I go about numbering the number of visits sequentially? Many thanks
0
Try this:
SELECT t1.UniqueID, t1.fldDate,
(select count(*) from Readmission t2 where t2.UniqueID=t1.UniqueID and t2.fldDate<=t1.fldDate) AS Rank
FROM Readmission AS t1
order by t1.UniqueID, t1.fldDate;

This will output:
patient1 1/1/2010 1
patient1 1/2/2010 2
patient2 2/1/2010 1
patient2 3/1/2010 2
patient2 3/2/2010 3
etc
0
Hi Ray,

Your recommendation W O R K E D!!! It's amazing. I gasped when the query ran....couldn't believe it would work (thought there would be more back and forth discussion on this). I wasn't expecting it to run so nicely. It did exactly what I needed. I thought I need to struggle a lot to make it run. But nope.

Thanks a million! I think other users like me would appreciate your solution as well. But I'm not sure how to paste your solution on the web for others to look at (as your response did not come through the web). But thanks again. You are wonderful!!!
0

Didn't find the answer you are looking for?

Ask a question
Try this:
SELECT t1.UniqueID, t1.fldDate,
(select count(*) from Readmission t2 where t2.UniqueID=t1.UniqueID and t2.fldDate<=t1.fldDate) AS Rank
FROM Readmission AS t1
order by t1.UniqueID, t1.fldDate;

This will output:
patient1 1/1/2010 1
patient1 1/2/2010 2
patient2 2/1/2010 1
patient2 3/1/2010 2
patient2 3/2/2010 3
etc
__________________________________________________
0
You're welcome,
Glad it worked for you.
Don't forget to click on 'I have an answer' I think this marks the question solved.
0
Try this:
SELECT t1.UniqueID, t1.fldDate,
(select count(*) from Readmission t2 where t2.UniqueID=t1.UniqueID and t2.fldDate<=t1.fldDate) AS Rank
FROM Readmission AS t1
order by t1.UniqueID, t1.fldDate;

This will output:
patient1 1/1/2010 1
patient1 1/2/2010 2
patient2 2/1/2010 1
patient2 3/1/2010 2
patient2 3/2/2010 3
etc
0