Subtract DateTime Between Rows

  • Thread starter Thread starter Joe
  • Start date Start date
J

Joe

Hi,

I am trying to determine phase times in our operation.
Essentially we have data that looks like this...

STAGEVALUE DATETIME
2 10/23/2003 16:11
4 10/23/2003 17:14
8 10/23/2003 17:49
16 10/24/2003 09:49
32 10/24/2003 10:34
64 10/24/2003 14:16
128 10/24/2003 14:31
2 10/24/2003 14:36
4 10/24/2003 18:04
8 10/24/2003 18:23

I want to be able to calculate the times between stages
in a query. For example, I'd like to know the difference
between 128 and 4. the problem is this data will continue
ad nauseum. Any suggestions?

Thanks in Advance.

-Joe
 
Hi,

I am trying to determine phase times in our operation.
Essentially we have data that looks like this...

STAGEVALUE DATETIME
2 10/23/2003 16:11
4 10/23/2003 17:14
8 10/23/2003 17:49
16 10/24/2003 09:49
32 10/24/2003 10:34
64 10/24/2003 14:16
128 10/24/2003 14:31
2 10/24/2003 14:36
4 10/24/2003 18:04
8 10/24/2003 18:23

I want to be able to calculate the times between stages
in a query. For example, I'd like to know the difference
between 128 and 4. the problem is this data will continue
ad nauseum. Any suggestions?
Click to expand...

Since Tables don't have any order - they're unsorted "bags" of data -
you'll need some criterion to determine "the next record". You can do
a Self Join query linking the table to itself, but (in this case, with
the data you've presented) it's ambiguous: there are two Stage 4
records, how can Access tell which one you mean?
 
-----Original Message-----
Since Tables don't have any order - they're unsorted "bags" of data -
you'll need some criterion to determine "the next record". You can do
a Self Join query linking the table to itself, but (in this case, with
the data you've presented) it's ambiguous: there are two Stage 4
records, how can Access tell which one you mean?


.
Click to expand...
John,

Thanks for the comment. I sorted the data and added a
key field. Any suggestions on what to do at this point?

Thanks,
Joe
Click to expand...
 
John,

Thanks for the comment. I sorted the data and added a
key field. Any suggestions on what to do at this point?
Click to expand...

Sorting the data IS COMPLETELY IRRELEVANT. No matter how you're
*displaying* it - and a table sort is purely a display convenience -
there is no order to the records. Access will store records wherever
it's convenient, not in order by any field in the table, not in data
entry order - just however it wants.

I *think* you'll need to use a Self Join query with a criterion
selecting the maximum date/time value less than the current record's
date/time value, but I confess I'm not at all certain what result you
want to obtain. Could you give an example, with actual fieldnames and
contents?
 
Back
Top