Interpolate Nearest Date

[Tomas C]

Howdy Folks,

I have two tables in Access. One table contains barometric pressure
measurements taken every 10 minutes. The other table has depth to well
water measurements taken at different time intervals (some are 30
seconds apart, some are longer) and different locations. I would like to
associate the less frequent (barometric pressure) data with the
generally more frequent (water level) data.

Ultimately, I would like the barometric pressure reading closest in time
to the water level reading to be used. If this showed up in a 3rd table,
that would be fine, too.

The problem is that I cannot figure out how to start this. I'm not even
sure what Google terms to search for. I've come across some mention of
subqueries, but I'm not sure if that's the right way to go. This
afternoon was the first I heard of a subquery. Any advice or ideas are
welcomed. Thanks in advance.

Here are the table structures in case you need them. I have date and
time concatenated in one field and also as separate fields in each table.

[tbl_baro]
Date and Time
BaroDate
BaroTime
BaroValue

[tbl_water_levels]
LocationID
WaterDateTime
WaterDate
WaterTime
WaterDepth

 

[Allen Browne]

The subquery would look something like this:

SELECT tbl_water_levels.*,
(SELECT TOP 1 BaroValue
FROM tbl_baro
ORDER BY Abs(tbl_water_levels.WaterDateTime
- tbl_baro.[Date and Time], tbl_baro.[Date and Time])
FROM tbl_water_levels

The idea is that you are choosing the record with the smallest absolute
difference between the date/time fields in the 2 tables.

More info about subqueries:
http://allenbrowne.com/subquery-01.html

 

[Guest]

Allen,

You are missing a ")" in there somewhere, I think it is after the first
tbl_baro.[Date and Time].

Dale

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

The subquery would look something like this:

SELECT tbl_water_levels.*,
(SELECT TOP 1 BaroValue
FROM tbl_baro
ORDER BY Abs(tbl_water_levels.WaterDateTime
- tbl_baro.[Date and Time], tbl_baro.[Date and Time])
FROM tbl_water_levels

The idea is that you are choosing the record with the smallest absolute
difference between the date/time fields in the 2 tables.

More info about subqueries:
http://allenbrowne.com/subquery-01.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Howdy Folks,

I have two tables in Access. One table contains barometric pressure
measurements taken every 10 minutes. The other table has depth to well
water measurements taken at different time intervals (some are 30 seconds
apart, some are longer) and different locations. I would like to associate
the less frequent (barometric pressure) data with the generally more
frequent (water level) data.

Ultimately, I would like the barometric pressure reading closest in time
to the water level reading to be used. If this showed up in a 3rd table,
that would be fine, too.

The problem is that I cannot figure out how to start this. I'm not even
sure what Google terms to search for. I've come across some mention of
subqueries, but I'm not sure if that's the right way to go. This afternoon
was the first I heard of a subquery. Any advice or ideas are welcomed.
Thanks in advance.

Here are the table structures in case you need them. I have date and time
concatenated in one field and also as separate fields in each table.

[tbl_baro]
Date and Time
BaroDate
BaroTime
BaroValue

[tbl_water_levels]
LocationID
WaterDateTime
WaterDate
WaterTime
WaterDepth

 

[Tomas C]

Many thanks. That worked beautifully once the syntax was corrected. I
don't really understand why or how it worked, but it did it. I sincerely
appreciate it.

Tom

 

[Jamie Collins]

The subquery would look something like this:

SELECT tbl_water_levels.*,
(SELECT TOP 1 BaroValue
FROM tbl_baro
ORDER BY Abs(tbl_water_levels.WaterDateTime
- tbl_baro.[Dateand Time], tbl_baro.[Dateand Time])
FROM tbl_water_levels

The idea is that you are choosing the record with the smallest absolute
difference between thedate/timefields in the 2 tables.

FWIW it can be done without a subquery e.g.

SELECT W1.LocationID, W1.WaterDateTime, B2.[Date and Time],
B2.BaroValue
FROM tbl_water_levels AS W1, tbl_baro AS B1, tbl_baro AS B2
GROUP BY W1.LocationID, W1.WaterDateTime, B2.[Date and Time],
B2.BaroValue
HAVING ABS(DATEDIFF('S', W1.WaterDateTime, B2.[Date and Time])) =
MIN(ABS(DATEDIFF('S', W1.WaterDateTime, B1.[Date and Time])));

The above leaves it for the OP to decide how to resolve ties i.e.
whether to use the earlier or later baro date and time value when the
interval between them and the WaterDateTime value is equal.

Jamie.

--