Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    200/XP (find nearest high or low tide)

    I have a table of high and low tides, containing the date/time of the event, the tide height (metres) and a text field stating whether it is High Tide or Low Tide.

    We have some survey data where the surveyors should have recorded whether it was high low or mid tide. Typically they forgot to write this down.

    We have the date and time of each survey.

    Is there any way to use the tide table to lookup the nearest event and pull across the tide status into the surveys table?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 200/XP (find nearest high or low tide)

    See the attached database. I've used three queries to determine the "nearest" high/low tide from the tides table. In the unlikely event that a survey is exactly halfway between high tide and low tide, both are returned.
    Attached Files Attached Files

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 200/XP (find nearest high or low tide)

    Thanks Hans

    I'm struggling a bit today - it's rather hot in the office (and nearly home time!). Can you elaborate on the queries a bit - i.e what's the reason for the <0.3 in qryDiff

    I may also need (or rather the surveyors will need) to complicate matters.

    I think they may want to know the following

    time to nearest High
    time to near Low

    if either of these are say more than 1.5 hours away it'd be mid tidal, otherwise, obtain the nearest high/low as you example.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 200/XP (find nearest high or low tide)

    Sorry, had to go away for a bit (and it's very hot and humid here too).
    The 0.3 is 0.3 days - the nearest high/low tide is never more than 0.3 * 24 = 7.2 hours away.

    I have attached a different version that returns Low if Low tide is 1.5 hours away or less, High if High tide is 1.5 hours away or less, Mid otherwise.
    Attached Files Attached Files

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 200/XP (find nearest high or low tide)

    That's exactly what I'm after <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

    Sorry for the delay in replying, I've just returned from my holiday.

    I'm beginning to wonder if there's a problem you can't solve.

    As always, many thanks

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 200/XP (find nearest high or low tide)

    Hans

    I'm wondering if you could help me take this further. Attached is a sample database (actual data) with the original queries you wrote.

    I've been asked to extract more information, namely the tidal phase (Phase) and whether the tide is rising or falling (falling - nearest tide event is HIGH and in the past OR low and in the future, rising - nearest tide event is low and in the past or high and in the future)

    tblTides is real tide data, the date time column is [dt_bst], the tidal phase is [Phase]
    tblSurveys are the actual survey dates - I have used the survey start date [SURV_DT_START] but may need to derive a halfway time between a survey's start and end (surveys were carried out over a few hours).
    Attached Files Attached Files

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 200/XP (find nearest high or low tide)

    See the attached version.

    I have added several queries:
    - A query qryLoHi that lists the time intervals from low tide to high tide.
    - A query qryRising that lists the survey IDs whose start time falls within one of the qryLoHi intervals, with "Rising" as phase.
    - A query qryHiLo that lists the time intervals from high tide to low tide.
    - A query qryFalling that lists the survey IDs whose start time falls within one of the qryHiLo intervals, with "Falling" as phase.
    - A union query qryRisingFalling that combines qryRising and qryFalling.

    Finally, I added qryRisingFalling to qryTides, linked to the other queries on the survey ID field, and added the phase field to the query grid.
    Attached Files Attached Files

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 200/XP (find nearest high or low tide)

    Wow, that took me a while to figure out what's going on there.

    I also need to pull out whether the time is a spring or neap tide (listed as the phase attribute in the tide table), the cutoff was decided as being neap if the absolute height difference was 3.7m or greater.

    I can't figure out whether to try to use the attribute field or to try to work it out in as similar way to your qryHiLo and qryLoHigh by pulling in the same table twice and used the id + 1

    edit: I'm just trying something now which looks sensible - I'll post it here when I'm done

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 200/XP (find nearest high or low tide)

    Hans

    1. I've modified the querys qryHiLo and qryLoHi to include the height values from adjacent rows, calculating the height difference (using abs when going from Low to High - I suppose I could have just swapped the order of the subtraction).

    2. The height_diff field is pulled through into queries qryRising and qryFalling

    3. In qryTide I've used an IIF statement with the criteria height_diff >=3.7 = 'Spring' otherwise Neap (just noticed this is different to th atrributes in tblTides but it doesn't matter, I'll just have to check the cut-off value)

    I've re-labelled the headings to Tidal_Cycle (high, mid, low) , Tidal_Flow for (rising, falling) and Tidal_Phase (spring, neap)

    Can you check my logic on this, particularly the first step - I've confused everyone in the office!
    Attached Files Attached Files

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 200/XP (find nearest high or low tide)

    Looks good to me!

    (From the tides table, I get the impression that spring tide is a height difference >= 3.9)

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 200/XP (find nearest high or low tide)

    Glad to hear that!

    I didn't realise you could bring the same table into a query twice and use an incrementing ID to get data from the next record - I've always used recordset loops.

    I've got 3.7 in my head so the table could be wrong or I could be wrong - it 's only something someone decided and they may well have changed their mind anyway.

    Thanks again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •