Select data from the last hour, MS Access

Question

I am working on doing some data transfers moving data from one database to another and I would like to do this without using to much memory on the computer that I am running this data transfer program on. Currently the program is ran every minute, but I want to alter this so that I am fetching data every hour. Ultimately, I want to change the following query so that I am not grabbing just the most value but that I am grabbing data that is from the past hour:

SELECT Data.[Date / Time],
       DATA.[Hot Strip Mill Total],
       Data.[Basic Oxygen Furnace Total],
       Data.[Electro-Arc Furnace Total],
       Data.[J-9 Shop Total],
       Data.[Levy Maintence Building Total],
       Data.[Ford Body Shop Total],
       Data.[Ford Chiller Building Total],
       Data.[Ford Dearborn W Plant Total],
       Data.[Ford Dearborn E Plant Total], 
       Data.[Ford Dearborn Balcony Total], 
       Data.[Ford Final Assembly Total],
       Data.[Ford Frame Plant Total], 
       Data.[Ford Dearborn N Plant Total],
       Data.[Ford Tool and Die Total], 
       Data.[Ford Paint Plant Total],
       Data.[Ford Glass Plant Total], 
       DATA.[Hot Strip Mill Rate], 
       Data.[Basic Oxygen Furnace Rate], 
       Data.[Electro-Arc Furnace Rate],
       Data.[J-9 Shop Rate],
       Data.[Levy Maintence Building Rate],
       Data.[Ford Body Shop Rate],
       Data.[Ford Chiller Building Rate],
       Data.[Ford Dearborn W Plant Rate],
       Data.[Ford Dearborn E Plant Rate],
       Data.[Ford Dearborn Balcony Rate],
       Data.[Ford Final Assembly Rate],
       Data.[Ford Frame Plant Rate],
       Data.[Ford Dearborn N Plant Rate],
       Data.[Ford Tool and Die Rate],
       Data.[Ford Paint Plant Rate],
       Data.[Ford Glass Plant Rate]
       FROM DATA  
       WHERE Format(Data.[Date / Time], 'mm/dd/yyyy hh:nn:ss') >=
           (select Format(max(Data.[Date / Time]),'mm/dd/yyyy hh:nn:ss') from Data);

As in, it is now 10:51:00AM, my program is now running it would return data from greater than 09:51:00AM until now, Likewise at 11:51:00AM, return data from greater than 10:51:00 until 11:51:00AM. I am not that famililar with Access but I am fairly capable with queries. This one just has me lost. Can anyone help understand how accomplish this? Thanks

score 3 · Accepted Answer · answered Jul 25 '11 at 15:42

3

Since 1 day = 1, therefore 1 hour = 1/24. So -just for fun - you could even express your condition as

WHERE Data.[Date / Time] >= (Now()-1/24)

answered Jul 25 '11 at 15:42

iDevlop

24,841
11
90
149

1

+1 I like that this answer nudges us to think about what a Date/Time value actually is. Seems like there should be a better way to say this, but I'm drawing a blank: "Since 1 day = 1" – HansUp Jul 25 '11 at 16:29
@HansUp - Not sure what I was thinking. I am a little confused at the moment – Aducci Jul 25 '11 at 18:25
Thank you, that is exactly what I was looking for. I didn't know what the equivalent for sysdate was. That is the way that I would handle it in Oracle but I didn't know how to in MS Access. Thanks again – LaDante Riley Jul 25 '11 at 18:32
this answer is really helpful, simple and clear! – Charles Jun 27 '23 at 09:05

score 2 · Answer 2 · answered Jul 25 '11 at 15:23

If the data type of your Data.[Date / Time] is Date/Time, you don't need to apply Format() when using the values in a date-based comparison.

WHERE Data.[Date / Time] >= DateAdd("h",-1, Now())

That approach could be much faster than using Format() with every row of the table, especially if the [Date / Time] field is indexed.

I'm not sure what you meant with this sentence:

"Likewise at 11:51:00AM, return data from greater than 10:51:00 until 11:51:00AM."

If that means at 11:51:00 AM your Data table may include rows which have [Date / Time] values later than 11:51:00 AM, you can add another condition to the WHERE clause to exclude them.

WHERE Data.[Date / Time] >= DateAdd("h",-1, Now()) AND Data.[Date / Time] < Now()

score 0 · Answer 3 · answered Jul 25 '11 at 15:12

0

WHERE Format(Data.[Date / Time], 'mm/dd/yyyy hh:nn:ss') >= DateAdd("h",-2, Now())

answered Jul 25 '11 at 15:12

JeffO

7,957
3
44
53

Select data from the last hour, MS Access

3 Answers3