DateDiff between Rows

[hyrican]

Is it possible to have a Datediff function compare the minutes between consecutive rows in a Time column?

Example:

Primary____________Time____________TimeStep
1_________________0100____________N/a
2_________________0102____________2
3_________________0108____________6
4_________________0139____________31


What I have so far:
Timestep: datediff(n, Time, ????)

Thanks,

 

[The_Doc_Man]

No, because you misunderstand Access structure. A column doesn't exist. Each row is an independent entity called a RECORD, for which the order of record appearance is not determined quite so easily as you might think. A record contains fields, but a field in record A is independent of the same field in record B.

You can write a query that sorts on something but here's the funny part. SQL and queries are based on a model in which everything that happens to records happens as though it happened in parallel. (OK, it really ISN'T parallel - but the model treats it that way.) So the order of something becomes an issue.

There are a couple of strange ways to do this in effect if not in fact. However, that field that has "Primary", 1, 2, 3, 4, ... is going to eat your lunch for you.

The only absolutely correct way to do this is to build a query and use VBA to traverse the recordset, remembering the previous record's contents in a context outside of SQL. Because in theory this is not a "legal" operation in SQL alone. You need help

If you changed "Primary" to 0 and that leftmost field was all numeric, you could do a DLookup of the value you wanted for the record whose leftmost field was the maximum value of all such records with that va less than that of the record you are examining. Make sense? (I didn't think so.)

Here is your problem. Access recordsets are unordered unless/until you sort them with a query. But then, context from one record to the next is not meaningful because in the normal sense, "next" and "previous" have no meaning either.

You can search this forum for various solutions to your problem, but they are all going to involve one or more of the "Domain Aggregate" functions. Try searching for keywords for "next record" & "previous record " to see how others approach this problem.

 

[redneckgeek]

I'm going to assume you want to do this in a query and not a table, since you posted in the query forum.

As long as you can put your records in the correct order in a query, then this could be done with a self join (or two) and a MAX.

SELECT c.aID, c.bID, Table1.Time AS aTime, c.bTime, 
DateDiff("n",[time],[btime]) AS Diff
FROM (SELECT max(a.id) AS aID, b.id as bID, b.time as bTime
           FROM table1 b INNER JOIN 
               (SELECT id
                FROM Table1 
                order by id) a ON b.id > a.id
           GROUP BY b.id, b.time
           order by b.id) c 
INNER JOIN Table1 ON c.aID = Table1.id

You might be able to shorten that up some, and it's not perfect, but it should give you an idea of what to do.

 

[jal]

I would propose a simpler solution, but then I'm probably missing something, since everything said here so far has been over my head. I am assuing you know that the Primary column is numbered consecutively. An autonumber column is pretty good about consecutive numbers - until a record is deleted from the table. Then it may try to reuse the old numbers out of sequence.

UPDATE table1 as A
INNER JOIN table1 as B
ON A.Primary = B.Primary - 1
SET TimeStep = datediff("n", A.Time, B.Time)


If you get negative times, try flipping A and B like this:


SET TimeStep = datediff("n", B.Time, A.Time)


Also you might need plus instead of minus:

ON A.Primary = B.Primary + 1


Maybe I'm missing something here?