Compare the field of one row with that of the next row.

[pgoulet]

I need to compare the time difference (gap) between one record and the next record.


The query produces data like this.

Date Time
9/1/03 15:45
9/1/03 15:46
9/1/03 16:02
9/1/03 17:08

The output should look something like...

Date Time Next Time Gap
9/1/03 15:45 15:46 1
9/1/03 15:46 16:01 15
9/1/03 16:01 17:08 1:07
9/1/03 17:08 17:22 14

Currently, I am exporting the data to Excel while concluding that there must be a way to this in Access without me ever having to export the data to Excel.

Thanks

<insert favorite joke here>

 

[dhookom]

This should get you most of the way there. You can add another select (subquery) if you want the NextTime column.

SELECT [Date], [Time],
(SELECT Min([Date]+[Time]
FROM qryA A
WHERE A.[Date]+A.[Time] > qryA.[Date]+qryA.[Time])-[Date]+[Time] as Gap
FROM qryA;

BTW: both Date and Time are function names and should not be used as field names.

Duane
MS Access MVP

 

[pgoulet]

Duane

Thanks for the quick response. I get the following error message.

Data type mismatch in criteria expression. Any ideas where I should look at the data.

Thanks again.

 

[dhookom]

There was a missing &quot&quot;. Are both the Date and Time fields Date/Time data types? If you look at them in your query, are the both right aligned in the datasheet view?
Try:
SELECT [Date], [Time],
(SELECT Min([Date]+[Time])
FROM qryA A
WHERE A.[Date]+A.[Time] > qryA.[Date]+qryA.[Time])-[Date]+[Time] as Gap
FROM qryA;


Duane
MS Access MVP

 

[PaulBricker]

You can try this
SELECT Table.Date, Table.Time, Min(Table_1.Time) AS NextTime, NextTime - Table.Time As MyDiff
FROM Table, Table AS Table_1
WHERE (((Table_1.Time)>Table.Time))
GROUP BY Table.Date, Table.Time
ORDER BY Table.Date, Table.Time;

Paul

 

[dhookom]

Paul,
Not sure about pgoulet's data but I assume ther may be multiple date values that should be accounted for.

Duane
MS Access MVP

 

[pgoulet]

Duane,

I already picked up on the missing &quot&quot; and took care of that.
Thanks for the tip on looking at the time format. I am trying to get down to distinct minute, so I was formating time &quot;hh:mm&quot;. I took that out and was able to get some output.

I am a lot closer, but ...

Date time Gap
9/15/2003 10:57:29 AM 9:55:03 PM
9/15/2003 10:57:34 AM 9:55:14 PM
9/15/2003 10:57:40 AM 10:40:00 PM
9/15/2003 11:42:20 AM 11:25:58 PM
9/15/2003 11:43:38 AM 11:27:26 PM
9/15/2003 11:43:48 AM 11:27:50 PM

We do a lot of bar code scanning. In fact, we do that 2x per hour. As you can see, someone skipped an hour. In fact, there are several gaps that are very long.

But, as I look at the GAP, between row a and b, the gap should only be 5 seconds.

When I click in a GAP Cell, a leading 12/31/1899 presents it self.

 

[PaulBricker]

Hi Duane. Grouping by the Date and Time should account for that although I did not actually construct any data to verify that. I will go back and test it out for problems.

Thanks

Paul

 

[PaulBricker]

Duane, you are correct. You would need a different Where clause in there. It would look like this
SELECT Table.Date, Table.Time, Min(Table_1.Time) AS NextTime, NextTime - Table.Time As MyDiff
FROM Table, Table AS Table_1
WHERE ((([Table_1].[Date])=

.[Date]) AND (([Table_1].Time)>

.[Time]))

GROUP BY Table.Date, Table.Time
ORDER BY Table.Date, Table.Time;

Hope that's better.

Paul

 

[dhookom]

You need to display your GAP field/column as time rather than date. A value that contains time only (less than 24 hours) will always show a date part of 12/31/1899.

If you are calculating the gap, it would be simpler if your initial query displayed:
DateAndTime
9/1/03 15:45
9/1/03 15:46
9/1/03 16:02
9/1/03 17:08
Rather than:
Date Time
9/1/03 15:45
9/1/03 15:46
9/1/03 16:02
9/1/03 17:08


Duane
MS Access MVP

 

[pgoulet]

Duane,

I took your suggestion to hart and modified the query to represent date+time. Takes a long time, lots of data to go through, but it works great.

Thanks

Paul