Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2013
    Posts
    3

    Unanswered: How to retrieve records of patients revisit a within certain days

    Hi experts,

    I have an Access table that hold the patient's info including AdmDate and DisChrgDate. I'm using VB front end to populate the data. I want to run a report showing all patients that return within 30days from the day they discharged. Right now what i'm doing is loop through each record for each patient and determine if the admdate is within the 30 days of the previous dischrgdate. If it is, I'll print that record out. Doing this way in vb is slow. Is there a way that this can be done via a query? Below is example of the table:

    UserID AdmDate DisChrgDate Reason ...
    1 1/1/13 1/2/13 Cold ...
    2 1/1/13 1/1/13 Flu ...
    3 1/1/13 1/3/13 Cough ...
    1 1/4/13 1/4/13 Cough ...
    2 2/2/13 2/3/13 .....
    3 1/28/13 2/4/13 ....
    2 2/7/13 2/19/13 .....

    The results from the example above would be:
    UserID AdmDate DisChrgDate Reason ....
    1 1/1/13 1/2/13 Cold ...
    1 1/4/13 1/4/13 Cough ...
    2 2/2/13 2/3/13 .....
    2 2/7/13 2/19/13 .....
    3 1/1/13 1/3/13 Cough ...
    3 1/28/13 2/4/13 ....

    Thanks!

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I would think you could use this:

    Microsoft Access tips: Subquery basics

    to get the previous visit's discharge date on the same line, then use DateDiff() to find the number of days difference, and put a criteria on that.
    Paul

  3. #3
    Join Date
    Jan 2013
    Posts
    3
    Hi pbaldy,

    Thanks for your response. I looked at that example and it seems like it only return one record. Is there a way to return multiple records?

    Thanks!

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    It should return multiple records.
    Paul

  5. #5
    Join Date
    Jan 2013
    Posts
    3
    Hi,

    I tried this,
    SELECT tblTest.UserID, tblTest.AdmDate, tblTest.DisChrgDate, tblTest.Reason,
    (SELECT TOP 1 T.DisChrgDate FROM tblTest AS T WHERE T.UserID = tblTest.UserID ORDER BY T.UserID) AS PreviousDate
    FROM tblTest;

    and I got this error "At most one record can be returned by this subquery.". I saw this note from the example, but I'm not sure how to correct this error

    •If the main query displays the result, the subquery must return a single value only. You get this error if it returns multiple values:
    At most one record can be returned by this subquery.

    Thanks!

  6. #6
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    Perhaps you could try something like this
    Code:
    SELECT tblTest.UserID, tblTest.AdmDate, tblTest.DisChrgDate, tblTest.Reason 
    FROM tblTest INNER JOIN 
    (SELECT TOP Max(DisChrgDate) as LastDisChrgDate, tblTest.UserID FROM tblTest GROUP BY tblTest.UserID) AS PreviousDate 
    ON WHERE PreviousDate.UserID = tblTest.UserID and tblTest.DisChrgDate = PreviousDate.LastDisChrgDate 
    ORDER BY T.UserID
    ?

    MTB

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •