Results 1 to 9 of 9
  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 (2003)

    Hi all,
    I have a UR with multiple admission dates. i am trying to write a query to find out the days between the first, the second and sometimes the third or fourth date. I can do a first or last filter (or a min or max (never sure) and then a date diff but this only gives me the difference between the first and the last. How can I get all the other ones in between?
    thanks,
    Van

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

    Re: date differences (2003)

    There are various ways you can do this, but in order to help you it would be useful to have more information about the structure of the table you're working with. Perhaps you could post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

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

    Re: date differences (2003)

    I'm sorry Hans.

    A single table with the field entitled admindate holds the date data that we want to work with. (this table relates to another table containing patient demographic data in a one-to-many with the PK being UR number). Each patient can have multiple admission dates. Hope this helps.

    Thanks,
    Van

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

    Re: date differences (2003)

    I'd like to know more about the design of the table - apart from the admindate field, what are the other relevant fields (name of the field, data type)?

  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 (2003)

    Sorry Hans,

    I don't have the database at hand and my colleague is away on leave.

    the field name is : admindate. It is a date/time (general date) data type. There are no other relavent fields. We need to find the date differences between the initial admindate and each subsequent admindate.

    Regards,
    Van

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

    Re: date differences (2003)

    Let's say the table is named tblDates.
    Create a query in design view based on tblDates.
    Add the admindate field to the query grid.
    Select Ascending from the Sort Order dropdown (this isn't essential, but it helps checking the results).
    Add a calculated column:

    Diff: DMin("admindate","tblDates","admindate>#" & Format([admindate],"mm/dd/yyyy") & "#")-[admindate]

    You won't know whether the dates you're comparing are for the same patient or not, because we don't have any information about that.

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

    Re: date differences (2003)

    Hi Hans,
    that works well for finding number of days between each date in sucession....but we want to return the number of days between the 1st date and each of the other dates. (ie. date1 - date2, date1 - date3, date 1 - date4, etc etc).

    Thanks,
    Van

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

    Re: date differences (2003)

    Change the definition of Diff to

    Diff: [admindate]-DMin("admindate","tblDates")

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

    Re: date differences (2003)

    that works perfectly.

    Thanks Muchly Hans,
    Van

Posting Permissions

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