Query max records

[Inferno]

Hi all,

I'm new to Access and am seeking advice on a query. I have a table of observed hourly tide data. I would like to query out the high tides for each day (2X/day). Each record is one hours data (24 records/day). Any suggestions on comparing each record to the previous and following records to determine the high points?

Any help is appreciated.

[Stewart Ross]

Hi all,

I'm new to Access and am seeking advice on a query. I have a table of observed hourly tide data. I would like to query out the high tides for each day (2X/day). Each record is one hours data (24 records/day). Any suggestions on comparing each record to the previous and following records to determine the high points?

Any help is appreciated.

Hi Inferno. Excel is much better suited to the task than Access. Although it is possible to generate an SQL query which will give the kind of next and last comparison you require it is a distinctly non-trivial task, involving self-joining two copies of the hourly table to itself where the joins are on the day ID and (hour-1) in one case, and the day ID and (hour+1) for the other.

It is much, much easier to perform such analysis in Excel instead, where it is just a few minutes work to set up this kind of last-and-next comparison.

-Stewart

[Inferno]

Hi Inferno. Excel is much better suited to the task than Access. Although it is possible to generate an SQL query which will give the kind of next and last comparison you require it is a distinctly non-trivial task, involving self-joining two copies of the hourly table to itself where the joins are on the day ID and (hour-1) in one case, and the day ID and (hour+1) for the other.

It is much, much easier to perform such analysis in Excel instead, where it is just a few minutes work to set up this kind of last-and-next comparison.

-Stewart

Thanks for the insight Stewart. Unfortunately, the file is far too large for Excel to handle (15 years worth of hourly data). I'll give self-joining the tables in Access a try.

[NeoPa]

One of the fundamental principles of SQL is that the records are in no particular order. They can be sorted by various keys, but there are no relative processing keywords available. Aggregate functions (Min(), Max(), First(), Last() etc) are supplied, but that is the limit of the ability to access progressive records.

VBA recordset coding may well be a more profitable line for you to explore here. This can do what you need, fairly straightforwardly. It's not as efficient (processing or time wise) as basic SQL though, so be warned.