Comparing a field in consecutive records

  • Thread starter Thread starter Paul Sutton
  • Start date Start date
P

Paul Sutton

I have a table of datestamped transactions on a system for
the various people using the system (each record also
references the user).
What I want to do is, for each user, calculate the elapse
time between each transaction. I will probably then
filter for excessive elapse times.

What is my best (and fastest, 10s of thousands of records)
method of doing this?

Thank you
 
Paul said:
I have a table of datestamped transactions on a system for
the various people using the system (each record also
references the user).
What I want to do is, for each user, calculate the elapse
time between each transaction. I will probably then
filter for excessive elapse times.

What is my best (and fastest, 10s of thousands of records)
method of doing this?
Click to expand...


You can use a subquery to find the previous transaction
date.

SELECT table.transID, table.datestamp, table.personID,
(SELECT Max(X.datestamp)
FROM table AS X
WHERE X.datestamp < table.datestamp
AND X.personID = table.personID
) AS PrevDateStamp
FROM table

Each record in the above query has both the current
transaction's datestamp and the previous datestamp and
calculating the difference can then be done in the usual
ways.
 
-----Original Message-----



You can use a subquery to find the previous transaction
date.

SELECT table.transID, table.datestamp, table.personID,
(SELECT Max(X.datestamp)
FROM table AS X
WHERE X.datestamp < table.datestamp
AND X.personID = table.personID
) AS PrevDateStamp
FROM table

Each record in the above query has both the current
transaction's datestamp and the previous datestamp and
calculating the difference can then be done in the usual
ways.
Click to expand...
This looks great - thank you. It is similar to something
I tried, however I just linked two copies of the same
table. Problem is, if filtered for one employee it works
nicely, otherwise seems to take forever (there can be
30,000 records in a day). Is there something i can do to
make it faster?
How might I do this in VBA - how do you return or use the
results of a query from VBA so it can be further queried
or used in a report?
 
This looks great - thank you. It is similar to something
I tried, however I just linked two copies of the same
table. Problem is, if filtered for one employee it works
nicely, otherwise seems to take forever (there can be
30,000 records in a day). Is there something i can do to
make it faster?
Click to expand...

A query with subquery is definitely going to be slower than
a query without one. The best thing to do to speed it up is
to make sure you have an index for the fields in the Where
clause. In the above it would be the DateStamp and PersonID
fields.

How might I do this in VBA - how do you return or use the
results of a query from VBA so it can be further queried
or used in a report?
Click to expand...

Trying to do this in VBA will be slower than doing it in a
query.

A report should be based on a query. The results of doing
something in VBA that can be used in a report is, at best, a
messy affair.
 
Back
Top