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

1. ## 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. ## 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.

3. ## 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. ## 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.

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

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

See the attached version.

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

8. ## 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. ## 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!

10. ## 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. ## Re: 200/XP (find nearest high or low tide)

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
•