By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 985 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

Query max records

P: 2
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.
Feb 29 '08 #1
Share this Question
Share on Google+
3 Replies


Expert Mod 2.5K+
P: 2,545
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
Feb 29 '08 #2

P: 2
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.
Feb 29 '08 #3

NeoPa
Expert Mod 15k+
P: 31,310
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.
Mar 7 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.