Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Oct 2006
    Location
    Melbourne, Victoria, Australia
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    date differences between successive records (2003)

    Hi all, Is it possible to calculate (in a query or report) the number of days between successive dates? What we need to find out is how many days between each hospital admission for a given person. The table contains "surname", "hospnumber (PK)", and "admindate".
    Thanks, Van

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: date differences between successive records (2003)

    Is Hospnumber the unique key within this table, or is it the unique identifier of the patient?

  3. #3
    Star Lounger
    Join Date
    Nov 2002
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: date differences between successive records (2003)

    Hi Hans, sorry for the tardy reply...I was out of the office.

    Hospitalnumber is the unique identifier of the patient, not the unique in the table. In other words, there can be more than one record in the table with the same hospitalnumber (eg. the patient has been admitted on three separate occasions).

    Van

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: date differences between successive records (2003)

    Let's say that the table is named tblAdmissions. You could use a query with the following SQL:

    SELECT tblAdmissions.Surname, tblAdmissions.HospNumber, tblAdmissions.AdminDate, (SELECT Max(t.AdminDate) FROM tblAdmissions AS t WHERE t.HospNumber=tblAdmissions.HospNumber AND t.AdminDate<tblAdmissions.AdminDate) AS PrevDate, [AdminDate]-[PrevDate] AS DaysBetween
    FROM tblAdmissions;

    The previous admission date is calculated in the subquery

    SELECT Max(t.AdminDate) FROM tblAdmissions AS t WHERE t.HospNumber=tblAdmissions.HospNumber AND t.AdminDate<tblAdmissions.AdminDate

    See the attached sample database (I omitted the Surname field).
    Attached Files Attached Files

  5. #5
    2 Star Lounger
    Join Date
    Oct 2006
    Location
    Melbourne, Victoria, Australia
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: date differences between successive records (2003)

    thanks Hans.

    that works great. quick question: what is the "t" for before the field names? are these temp variables that you've created?

    thanks again, Van

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: date differences between successive records (2003)

    No, the t is an alias for the table in the subquery. It is used to distinguish the table in the subquery from the same table in the main query. If I had written

    WHERE tblAdmissions.HospNumber=tblAdmissions.HospNumber

    the condition would always be true since Access sees the same thing on both sides of the =.

Posting Permissions

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