Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2005
    Posts
    4

    Unanswered: Previous Date in Query

    I have a table containing data regarding patients' visits to medical offices. Here is a sample:

    Patientnumber patientname dateofvisit
    001 Joe 1/15/04
    001 Joe 2/21/05
    001 Joe 2/11/06
    001 Joe 3/1/07
    002 Jane 1/2/04
    002 Jane 1/13/05
    002 Jane 1/16/06
    002 Jane 1/3/07

    I want to create a query that will add a field showing the date of the last visit for each record. It should look like this:

    Patientnumber patientname dateofvisit lastvisitdate
    001 Joe 1/15/04
    001 Joe 2/21/05 1/15/04
    001 Joe 2/11/06 2/21/05
    001 Joe 3/1/07 2/11/06
    002 Jane 1/2/04
    002 Jane 1/13/05 1/2/04
    002 Jane 1/16/06 1/13/05
    002 Jane 1/3/07 1/16/06

    Any help would be greatly appreciated. Thanks in advance.

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Why????

    SELECT TOP 2 PatientID, PatientName, LastDate FROM BlahBlah
    GROUP BY PatientID, LastDate
    ORDER BY PatientID, LastDate DESC

    This gets you the lastmost 2 dates for each patient ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    You're slipping, Mike.

    That would only return 2 records... The top 2 for the first patient.
    Inspiration Through Fermentation

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by RedNeckGeek
    You're slipping, Mike.

    That would only return 2 records... The top 2 for the first patient.
    Well ... Crap off the top of the head ... I know that Rudy's done this query before ... I even think it's been questioned recently ...

    But, this is the way to go ... Why daisy chain the dates and break normalization????
    Back to Access ... ADO is not the way to go for speed ...

  5. #5
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    It can be done with standard SQL. I'm just having a little trouble getting there.
    Inspiration Through Fermentation

  6. #6
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Here it is... I'll leave it to you to convert to your field names. I gotta rush off to a meeting.

    Code:
    SELECT     B.PRODUCT, MAX(a.MNTHYR) AS dt1, B.DT2
    FROM         PRODUCTION_CMPLT a INNER JOIN
                              (SELECT     PRODUCT, MAX(MNTHYR) AS DT2
                                FROM          PRODUCTION_CMPLT
                                GROUP BY PRODUCT) B ON a.PRODUCT = B.PRODUCT AND a.MNTHYR < B.DT2
    GROUP BY B.PRODUCT, B.DT2
    ORDER BY B.PRODUCT
    edit: This only works if the patient has actually visited twice (or more). I think a left join is needed, too.
    Last edited by RedNeckGeek; 03-09-07 at 15:55.
    Inspiration Through Fermentation

  7. #7
    Join Date
    Dec 2005
    Posts
    4
    The table has more fileds containing information about the visit (charges, doctor info, etc.) The query will ultimately be used to analyze aspects of each office visit, one of which is the length of time since the last visit.

  8. #8
    Join Date
    Dec 2005
    Posts
    4
    Thanks for the help. I appreciate it.

  9. #9
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    This one works, even if the patient only visited once.

    Code:
    SELECT   B.PatientID, MAX(B.lastdate) AS dt1, 
                 MAX(a.lastdate) AS dt2
    FROM     Patients a RIGHT OUTER JOIN
                  (SELECT     PatiendID, lastdate
                    FROM  patients) B ON a.lastdate < B.lastdate 
            AND a.PatientID = B.PatientID
    GROUP BY B.PatientID
    ORDER BY B.PatientID
    Inspiration Through Fermentation

Posting Permissions

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