Querying last record prior to a specified time on each day?

[Tim]

Hi,

I have a table of records that represent sampled values in a time series.
For example,

SampleDateTime SampleValue
03/10/02 08:59:28 0.00234
03/10/02 08:59:40 0.00323
03/10/02 09:01:07 0.00374
03/11/03 08:57:54 0.00293
03/11/03 08:58:35 0.00385
03/11/03 09:59:22 0.00483
03/12/03 07:32:43 0.00263
03/13/03 06:34:23 0.00232
....

I need a query that will retrieve the most recent record prior to a
specified time for each day.

For example, using the above example data, if I wanted the most recent
record prior to 09:00:00 for each day, I would get the following records:

03/10/02 08:59:40 0.00323
03/11/03 08:58:35 0.00385
03/12/03 07:32:43 0.00263
....

I've tried all sorts of sub-queries, the TOP keyword, and lots of date
manipulation, but I've reached the limits of my humble knowledge. Any help
or pointers on the magic SQL incantation that I need to use would be greatly
appreciated.

Thanks. Tim

 

[Steve Schapel]

Tim,

I think you need a two-step process here. First make a query like
this...

SELECT DateValue([SampleDateTime]) AS EachDay, Max([SampleDateTime])
AS WhatsTheLatest
FROM Samples
WHERE ([SampleDateTime]-Int([SampleDateTime]))<#9:00:00#
GROUP BY DateValue([SampleDateTime])

Let's say you name this query LastSample, then make another query to
retrieve the SampleValue, like this...

SELECT LastSample.EachDay, LastSample.WhatsTheLatest,
Samples.SampleValue
FROM Samples INNER JOIN LastSample ON Samples.SampleDateTime =
LastSample.WhatsTheLatest

- Steve Schapel, Microsoft Access MVP

 

[Tim]

Excellent. Thanks, Steve.

I think I've been able to simplify things further a little, and combine the
two queries in to one by using the Last() function on the "SampleDateTime"
and "SampleValue" fields. Thanks for the head start.

Cheers, Tim

Tim,

I think you need a two-step process here. First make a query like
this...

SELECT DateValue([SampleDateTime]) AS EachDay, Max([SampleDateTime])
AS WhatsTheLatest
FROM Samples
WHERE ([SampleDateTime]-Int([SampleDateTime]))<#9:00:00#
GROUP BY DateValue([SampleDateTime])

Let's say you name this query LastSample, then make another query to
retrieve the SampleValue, like this...

SELECT LastSample.EachDay, LastSample.WhatsTheLatest,
Samples.SampleValue
FROM Samples INNER JOIN LastSample ON Samples.SampleDateTime =
LastSample.WhatsTheLatest

- Steve Schapel, Microsoft Access MVP

Hi,

I have a table of records that represent sampled values in a time series.
For example,

SampleDateTime SampleValue
03/10/02 08:59:28 0.00234
03/10/02 08:59:40 0.00323
03/10/02 09:01:07 0.00374
03/11/03 08:57:54 0.00293
03/11/03 08:58:35 0.00385
03/11/03 09:59:22 0.00483
03/12/03 07:32:43 0.00263
03/13/03 06:34:23 0.00232
...

I need a query that will retrieve the most recent record prior to a
specified time for each day.

For example, using the above example data, if I wanted the most recent
record prior to 09:00:00 for each day, I would get the following records:

03/10/02 08:59:40 0.00323
03/11/03 08:58:35 0.00385
03/12/03 07:32:43 0.00263
...

I've tried all sorts of sub-queries, the TOP keyword, and lots of date
manipulation, but I've reached the limits of my humble knowledge. Any help
or pointers on the magic SQL incantation that I need to use would be greatly
appreciated.

Thanks. Tim

 

[Steve Schapel]

Good thought, Tim, but I wouldn't rely on this method to give you the
correct results. Access will evaluate the Last for each of the fields
independently, so I don't think it's guaranteed that the Value
returned will always be the one that's associated with the SampleDate
returned. First and Last functions return a random value. In
practice, you may find it seems to work correctly, but I wouldn't do
it this way myself.

- Steve Schapel, Microsoft Access MVP

 

[Tim]

Thanks, Steve.

Good point. The Access (2000) documentation doesn't say much specifically on
the how Last() operates, and I couldn't find much about it on the net
either.

I'll stick to your original approach which works well. Thanks again.

Cheers, Tim