Comparing and calculating trip-date differences

Topic

New Reply

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?

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote