• 200/XP (find nearest high or low tide)

    Home » Forums » AskWoody support » Productivity software by function » MS Access and database help » 200/XP (find nearest high or low tide)

    Author
    Topic
    WSDarsha
    AskWoody Lounger
    July 30, 2008 at 2:55 am #452815

    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?

    Reply | Quote
    Viewing 0 reply threads
    Author
    Replies
    • WSHansV
      AskWoody Lounger
      July 30, 2008 at 3:20 am #1118930

      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.

      Reply | Quote
      • WSDarsha
        AskWoody Lounger
        July 30, 2008 at 3:33 am #1118933

        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.

        Reply | Quote
        • WSHansV
          AskWoody Lounger
          July 30, 2008 at 8:34 am #1118964

          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.

          Reply | Quote
          • WSDarsha
            AskWoody Lounger
            August 11, 2008 at 1:41 am #1120554

            That’s exactly what I’m after bravo

            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

            Reply | Quote
          • WSDarsha
            AskWoody Lounger
            October 8, 2008 at 3:10 am #1128380

            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).

            Reply | Quote
            • WSHansV
              AskWoody Lounger
              October 8, 2008 at 3:50 am #1128382

              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.

              Reply | Quote
            • WSDarsha
              AskWoody Lounger
              October 9, 2008 at 1:56 am #1128494

              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

              Reply | Quote
            • WSDarsha
              AskWoody Lounger
              October 9, 2008 at 2:17 am #1128506

              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!

              Reply | Quote
            • WSHansV
              AskWoody Lounger
              October 9, 2008 at 2:29 am #1128515

              Looks good to me!

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

              Reply | Quote
            • WSDarsha
              AskWoody Lounger
              October 9, 2008 at 2:47 am #1128519

              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.

              Reply | Quote
    Viewing 0 reply threads
    Reply To: 200/XP (find nearest high or low tide)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information:




DON'T MISS OUT!
Subscribe to the Free Newsletter
We promise not to spam you. Unsubscribe at any time.
Invalid email address

View the Forum

  • Recent Replies
  • My Replies
  • My Active Topics
  • New Posts in the Last day
  • Private Messages
  • Knowledge Base
  • How to use the Forums
  • All Forums

    • Recent Topics

    My Profile

    May 2025
    S M T W T F S
     123
    45678910
    11121314151617
    18192021222324
    25262728293031

    Remembering Woody

     

    Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.

    Mastodon profile for DefConPatch
    Mastodon profile for AskWoody

     

    Home • About • FAQ • Posts & Privacy • Forums • My Account
    Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts

    Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.