join table based on Time comparison

[tdes42]

I believe I am looking for some form of Join Query, but my
understanding of Access logic and my logic do not yet click entirely….

I have a table of ocean buoy data, taken every hour over many days
(hopefully a whole year if it doesn’t slow things down too much):
tblBuoy
Date Time Swell Direction Swell Period Swell
Height
5/6/2007 15:00 195
14.2 2.568
5/6/2007 16:00 196
14.0 2.671
etc.

And, tblTide (taken every 15 minutes, also for a whole year)
Date Time Tide Height
5/6/2007 15:00 3.452
5/6/2007 15:15 3.643
etc.

And, tblConditions (taken at somewhat random times throughout a day) –
Surf Conditions
Date Time Rating Wave Height
5/6/2007 15:02 Fair Waist High
5/6/2007 15:49 Poor to Fair Chest High
etc.

I would like to join the three tables, matching the Conditions to the
closest tide and swell information based on Date and Time.
My first solution just cuts the minutes off the tblConditions.Time
field, and takes Tide information every minute (which for a year of
data is I think far too large and far too precise).
I am right at the dangerous knowledge of Access, I can throw some code
from different places around and make it work… but don’t have really
any idea why it works� I really appreciate any thoughts, suggestions,
answers to how this can be tackled, and apologize if this has been
handled somewhere else (I did search for an answer in several places).
Thank you :)
Tim

[Steve]

To begin, you need to change the names of your Date and Time fields. "Date'
and "Time" are reserved words and should not be used for field names or
object names.

How about a different design of your tables ---
TblBuoyData
BuoyDataID
BuoyDataDate
BupyDataTime
SwellDirection
SwellPeriod
SwellHeight

TblTideData
TideDataID
BuoyDataID
TideDataDate
TideDataTime
TideHeight

TblSurfCondition
SurfConditionID
BuoyDataID
SurfConditionDate
SurfConditionTime
SurfConditionRating
WaveHeight

Make BuoyDataID In TblBuoyData, TideDataID in TblTideData and
SurfConditionID in TblSurfCondition autonember data type and Access will
automatically fill these fields when you revise your tables. For BuoyDataID
in TblTideData, create a query that includes TblTideData. Add a calculated
field to the query to combine TideData Date and TidedataTime into one field.
Create a query that includes TblBuoyData. Add a calculated field to the
query to combine TideData Date and TidedataTime into one field. Sort the
calculated field ascending. You now need code that loops through the records
in your TideData query, for each record finds the record in the BuoyData
query where the previous record has a date and time less than the date and
tine in the TideData query and the next record has a date and time more than
the date and tine in the TideData query then updates BuoyDataID in the
TideData query to the BuoyDataID in the record in the BuoyData query. Use
the same process for BuoyDataID in TblSurfCondition.

When the above is completed you will have all TideData records linked to a
BuoyData record and all SurfCondition records linked to a BuoyData record.
You will be able to build a form with two subforms where the main form
displays a BuoyData record, one subform dosplays TideData records related to
the BuoyData record and the other subform displays SurfCondition records
related to the BuoyData record.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
re******@pcdatasheet.com


"tdes42" <ti*******@googlemail.comwrote in message
news:11**********************@n2g2000hse.googlegro ups.com...
I believe I am looking for some form of Join Query, but my
understanding of Access logic and my logic do not yet click entirely..

I have a table of ocean buoy data, taken every hour over many days
(hopefully a whole year if it doesn't slow things down too much):
tblBuoy
Date Time Swell Direction Swell Period Swell
Height
5/6/2007 15:00 195
14.2 2.568
5/6/2007 16:00 196
14.0 2.671
etc.

And, tblTide (taken every 15 minutes, also for a whole year)
Date Time Tide Height
5/6/2007 15:00 3.452
5/6/2007 15:15 3.643
etc.

And, tblConditions (taken at somewhat random times throughout a day) -
Surf Conditions
Date Time Rating Wave Height
5/6/2007 15:02 Fair Waist High
5/6/2007 15:49 Poor to Fair Chest High
etc.

I would like to join the three tables, matching the Conditions to the
closest tide and swell information based on Date and Time.
My first solution just cuts the minutes off the tblConditions.Time
field, and takes Tide information every minute (which for a year of
data is I think far too large and far too precise).
I am right at the dangerous knowledge of Access, I can throw some code
from different places around and make it work. but don't have really
any idea why it works? I really appreciate any thoughts, suggestions,
answers to how this can be tackled, and apologize if this has been
handled somewhere else (I did search for an answer in several places).
Thank you :)
Tim

[tdes42]

Wow!! :) Thank you Steve and Allen for the help, and so quick... I
will need a bit of time to sort through both answers.
I had originally separated the Date and Time fields, because data
entry had repeated Dates but different Times, and I wanted to shorten
the data entry time. But it sounds better if I find a way to combine
these, maybe after they are entered on the form.

If I understand your solutions, I eliminate all but the Tide Data on
the hour. To be a bit difficult (sorry :) ) I can achieve this
simply by asking the internet tide data source to produce data for
each hour... I was hoping to maintain a higher accuracy on the Tide
Data than the Buoy Data. Tides change faster. But it's probably best
to go down to the hour, implement your solutions. The more I think
about it, hourly tides are great!

OK... I have a feeling I'm not done asking questions, but now I have
some work to do implementing your solutions :)

Thanks so much,
Tim

[Steve]

<< I eliminate all but the Tide Data on the hour. >>

You don't need to do this! It's okay to have four points of Tide Data for
every Buoy Data.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
re******@pcdatasheet.com


"tdes42" <ti*******@googlemail.comwrote in message
news:11*********************@57g2000hsv.googlegrou ps.com...

Wow!! :) Thank you Steve and Allen for the help, and so quick... I
will need a bit of time to sort through both answers.
I had originally separated the Date and Time fields, because data
entry had repeated Dates but different Times, and I wanted to shorten
the data entry time. But it sounds better if I find a way to combine
these, maybe after they are entered on the form.

If I understand your solutions, I eliminate all but the Tide Data on
the hour. To be a bit difficult (sorry :) ) I can achieve this
simply by asking the internet tide data source to produce data for
each hour... I was hoping to maintain a higher accuracy on the Tide
Data than the Buoy Data. Tides change faster. But it's probably best
to go down to the hour, implement your solutions. The more I think
about it, hourly tides are great!

OK... I have a feeling I'm not done asking questions, but now I have
some work to do implementing your solutions :)

Thanks so much,
Tim