Closest Timestamp without going over - Relationship?

[Scott Herndon]

I have a timestamp in Table1, call it SomeTime

and I have another field in Table1, call it Temperature

In Table2, I have a timestamp called WeatherTime

and a field called WeatherTemperature.

It is highly unlikely that I can use an exact = relationship between SomeTime in Table1 and WeatherTime in Table2. So I 'know' that my weathertime(s) come ever hour. I created another timestamp field in Table1 called SomeTimePlusHour.

I'm banging my head against a wall trying to use a spiffy relationship to Table2, like ">" SomeTime, but "<" SomeTimePlusHour. So It isn't working for me. Does anyone have advice for looking up tabulated values using a 'closest retail price without going over' algorithm to allow for the fact I'll never have identical timestamps in these two Tables?

Thanks,

Scott

[David Jondreau]

Well, you seem to be on the right track. Are you sure you're adding the hour correctly to the SomeTimePlusHour timestamp field? Timestamp fields calculate by the second. An hour is 3600 seconds.

You could also 'translate' your timestamp fields into two fields, one that captures the date and the other that captures the hour (or 'the half hour' or whatever increment you want).

It's not clear what the purpose of your relationship is so it's hard to give more precise advice.