DateTime Calculation from previous record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need a query that will filter records with more than 8 hours elapsed
between entries. The DateTime field is stamped on each record using Now().
I would appreciate any help you could offer.

Thanks.
 
Sherri,

Kind of vague. Without your data structure, I'll just have to throw
something out and you can see if it meets your needs.

One waya that I have used in the past is similiar to:

SELECT T.*
FROM yourTable T
WHERE DateDiff("h",
DMAX("TimeField", "yourTable", "[TimeField] < #" & T.TimeField & "#"),
T.TimeField) > 8

HTH
Dale
 
Dale,

What I have is a table named "Plant Emissions". My employees are required by
the state to enter a reading within certain ranges at least every 8 hrs.
Each time we violate the permit, we have to report it to the state. I am
attempting to pull some type of report that filters out the exceptions.
Quite frankly, I am not sure how to go about doing this. Without the filter,
I am looking at over 42k records. Any help that you can give is much
appreciated.

Dale Fye said:
Sherri,

Kind of vague. Without your data structure, I'll just have to throw
something out and you can see if it meets your needs.

One waya that I have used in the past is similiar to:

SELECT T.*
FROM yourTable T
WHERE DateDiff("h",
DMAX("TimeField", "yourTable", "[TimeField] < #" & T.TimeField & "#"),
T.TimeField) > 8

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.


SherriK said:
I need a query that will filter records with more than 8 hours elapsed
between entries. The DateTime field is stamped on each record using Now().
I would appreciate any help you could offer.

Thanks.
Click to expand...
Click to expand...
 
Sherri,

Did you try the query I gave you? If not try it, making sure to change the
yourTable and TimeField to the appropriate values.

If I knew the structure of your table, (field names and types) it would be
easier. Are you only looking at running this query for a short period of
time, or for the entire database of 42K records?

Dale
--
Email address is not valid.
Please reply to newsgroup only.


SherriK said:
Dale,

What I have is a table named "Plant Emissions". My employees are required by
the state to enter a reading within certain ranges at least every 8 hrs.
Each time we violate the permit, we have to report it to the state. I am
attempting to pull some type of report that filters out the exceptions.
Quite frankly, I am not sure how to go about doing this. Without the filter,
I am looking at over 42k records. Any help that you can give is much
appreciated.

Dale Fye said:
Sherri,

Kind of vague. Without your data structure, I'll just have to throw
something out and you can see if it meets your needs.

One waya that I have used in the past is similiar to:

SELECT T.*
FROM yourTable T
WHERE DateDiff("h",
DMAX("TimeField", "yourTable", "[TimeField] < #" & T.TimeField & "#"),
T.TimeField) > 8

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.


SherriK said:
I need a query that will filter records with more than 8 hours elapsed
between entries. The DateTime field is stamped on each record using Now().
I would appreciate any help you could offer.

Thanks.
Click to expand...
Click to expand...
Click to expand...
 
Dale,

What I have is a table named "Plant Emissions". My employees are required by
the state to enter a reading within certain ranges at least every 8 hrs.
Each time we violate the permit, we have to report it to the state. I am
attempting to pull some type of report that filters out the exceptions.
Quite frankly, I am not sure how to go about doing this. Without the filter,
I am looking at over 42k records. Any help that you can give is much
appreciated.
Click to expand...

A "Self Join" query can do this - add the table to the query grid
*twice*.

You chose not to post any information about the structure of the
table, or I'd suggest how you could create the query and the join; but
the basic idea is that you would put a criterion on the second
instance of the table to find the most recent previous date/time
value. You could then use DateDiff() to calculate the time between the
two dates.

John W. Vinson [MVP]
 
Back
Top