Nbring same records showing 1x sequencially Closed

Question

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.


Configuration: Windows XP / Internet Explorer 7.0

Ray · Answer

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.

Vivian · Answer

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

Ray · Answer

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

Vivian · Answer

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!!!

Vivian · Answer

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 
__________________________________________________

Ray · Answer

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.

Vivian · Answer

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

Nbring same records showing > 1x sequencially

7 responses

Subscribe To Our Newsletter!