Ideas on how to do a calculation

[Ethvia]

Thanks for reading this.

I have a task that requires me to calculate what level of alert our business is at each hour. The data is stored as follows:

Time Alert Level
1/1/11 5:00pm 3
1/1/11 8:00pm 4
1/7/11 7:00am 3

What I want to get out of this is:

Alert Level Hour
3 1/1/11 5pm
3 1/1/11 6pm
3 1/1/11 7pm
4 1/1/11 8pm
4 1/1/11 9pm
... .... (this would continue by hour until it hits the next change)
3 1/7/11 7am

Does anyone have any idea on how to accomplish this?

thanks!

 

[ChrisChuffy]

Well, to me the obvious solution is to use VLOOKUP() in Excel.

Just use 'True' as the last parameter and it will match the highest date that doesn't exceed the date that you're after.

 

[Ethvia]

Thanks Chris, but I'm trying to do this completely in Access so it can run when I'm not at work. I appreciate you taking the time to answer.