Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Sep 2010
    Location
    Las Vegas Nevada USA
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a table that consists of Customer ID's and trip start and end dates. I need to find any trips where the start date of a trip is within one day of the previous trips end date. For example, customers trip ends on 01/01/10 and the next trip started on 01/02/10. Does anyone have any suggestions on how I might do that?

  2. #2
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post
    First you need to understand that dates are just numbers. In fact they are more or less double, so the time now is 40438.6782638889

    Anything to the left of the decimal point is the day, the decimal part is the time.

    So if you store just the date, today is 40438.0

    To calculate differences you must ensure that you are not being messed around by the time.

    Now down to business - This is the kind of thing where Excel is actually better than access. In Excel you can use a formula that compare the value in a cell with the value in a cell in the previous row.

    To do the same thing in Access I would use a report with some code in the detail's format event to set a module level variable and then for each record compare the start date to the variable from the last record then set the variable again.

    You can see a simple example in the attached database.
    Attached Files Attached Files

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    Quote Originally Posted by Tina Anderson View Post
    I have a table that consists of Customer ID's and trip start and end dates. I need to find any trips where the start date of a trip is within one day of the previous trips end date. For example, customers trip ends on 01/01/10 and the next trip started on 01/02/10. Does anyone have any suggestions on how I might do that?
    Do you need to do this via queries? If so, check out the attached.
    I have assumed that it can be across all customers, not just the same customer. I have included another query for the same customer if that is required.
    Attached Files Attached Files

Posting Permissions

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