Filtering by time: looking for max/min within a time interval

[judehamilton]

Hello, Smart Folks--I have a database consisting of water levels (tidal heights) collected every 30 minutes for a year at one station, every six minutes at another. There are two high tides on most days, and two low tides on most days; one high tide is higher than the other ("HH" for "higher high" to distinguish it from the secondary high tide of the day, "H") and one low tide is lower than the other ("LL" for "lower low" to differentiate from the corresponding secondary low tide of a given day, "L"). For the station with six-minute resolution, I already have a table showing the dates and times for HH, LL, H, and L.

I would like to build a query that will look in the 30-minute resolution table and find the maximum or minimum values (and preferably the time associated with each value) that occur within a discrete time interval surrounding the time associated with the HH, LL, H, and L values in the six-minute resolution table. The lag time between the two stations ranges from 6 to 24 minutes, so the time frame in question would be a half-hour or so on either side of the time of HH, LL, H, or L found in the six-minute table.

If anyone can chime in and provide some advice, I would truly appreciate any help I can get. The thought of going through thousands of entries and pulling these out by eye is killing me.

Judy

 

[John Big Booty]

Welcome to the forum.

If you create a select query to interrogate your data, whilst that query is in design view, click on the Sigma (Σ) button, and you will notice that a new row (Total:) will appear in your design grid, two of the options that become available are Max and Min.

 

[judehamilton]

Hello, John--Yes, I am familiar with the "max/min" feature, and have used it to acquire the daily HH and LL (the daily max and min) values in my 30-minute table; the values I seek are the daily secondary highs and lows that are not simply the values on one side or the other of the HH or LL values. I want to design a query that will look for max/min during a discrete time interval, specifically that time interval that includes the time already specified in the 6-minute table as containing H or L (the secondary high and low tide).

Hopefully this clarifies my initial post and doesn't further muddy the waters. Thank you again for your input!

 

[John Big Booty]

You will need to do this using two queries the first you would us the Between operator to select the data that is;

Between [StartTime] And [URL="http://www.techonthenet.com/access/functions/date/dateadd.php"]DateAdd[/URL]("n", 30, [StartTime] )

Then use the results as part of your second query where you select the Max Min values.

 

[Brianwarnock]

I see his requirement as more complex than that.
In Table1 he has for example
HH at 01/01/2011 12:32

He wants from table2 Max/Min between 01/01/2011 1202 and 01/01/2011 13:02 plus the date/time info

But then I'm probably wrong

Brian

 

[John Big Booty]

You may very well be correct Brain

Hopefully they can take what I've given them and modify it to fit their requirements.

 

[judehamilton]

Yes, Brian...that is exactly what I'm after. I have so far gotten very, very close using John's advice, but it has required a rather cluttery pile of queries. I will probably be able to find what I'm after on my present track, but if you have an all-in-one query design I would be most pleased to hear about it so I can clean things up and move on.

Many thanks to both of you for taking the time to bother with me.

 

[Brianwarnock]

Don't worry about a multi cascade of queries it is not unusual, infact when using Max/Min it is normal. I think my simple example would require 3 queries. I would have to have data to play with and perhaps information on the likelihood of duplicate values for heights to come up with a real live solution, if I could!

Brian

 

[judehamilton]

Well, I think I've done it, and with three queries as you estimated. I surely do thank you guys for saving me from a world of hurt!

 

[John Big Booty]

Glad you got it sorted in the end

 

[Harry1089]

nice one

 

[judehamilton]

Weeeelllllll....I'm baaaaack...
Fortunately I've noticed that there's a problem, and even more fortunate is that you guys might be able to help me again (or so I'm hoping). My final query of the series asks the 30 minute table for the max and min of water levels occurring during specific time windows (as designated by the 6 minute table as a time period when a given tidal height has occurred; defined as H or L, as you will recall). This seems to work splendidly EXCEPT when the time interval spans two different days, i.e., the interval "straddles" midnight. Thus the "H" of the day might occur minutes prior to midnight at the site with the 6-minute table, but several minutes after midnight at the 30-minute site being queried. Perhaps my problem is one of formatting: I'm asking for these H and L values by "day," when the tidal day is not quite on the same schedule (the lunar day is something like 50 minutes longer than the solar one). Because I want these water levels by day of the year, I have been using the "short date" format type; I have looked at the other format types and don't see what I'm after. Help?
Another question (which would really help me for other reasons beyond checks of the data...and may help me to fix this manually if necessary) is: how do I get a "time stamp" for each of the H or L values returned by the final query? This seems so simple to do, but for whatever reason it is escaping me.
Thanks again for your input!

 

[Brianwarnock]

Is it possible to attach a sample of your DB , say 2-3 days including the scenario mentioned, plus your current queries?

Not in 2007 format as many of us do not have that.

Brian

 

[judehamilton]

Okay...here's my scaled-down database, with one month of data in the 30-minute table, and the times and heights for H, L, LL and HH for the same time period in the 6-minute table (from whose full glory I will spare you). I verified that the phenomenon I mentioned above (a "midnight straddle" of sorts) occurs several times. I am using Office 2010 but see "Database (Access 2007)" at the top when I open this...hopefully this isn't a game-changer.

You would figure this out quickly enough, but the queries are used in this order: 1. Define the time interval (plus/minus 60 minutes) around the H and L tides for Charleston (originally six-minute-interval data); 2. Find the range of date/time stamps from the VA (30 minute intervals) table that coincide with the plus/minus interval for Charleston defined above (in 1.); and 3. Pick out the specific water level value from VA that coincides with the lowest (L) or highest (H) water level in the range of intervals defined above (in 2.).

Query #3 above would be vastly improved if it would also return the datetimestamp associated with the max or min value. I don't know why I am unsuccessful with this, as it seems very straightforward but hasn't worked yet for me. Also, if it would return these values by interval rather than by day (so that the H or L would be on the right side of midnight) I would be home free (but then I've been wrong before.)

Hopefully this is not too much information...and thank you again for your time.

 

[Brianwarnock]

Sorry but as I said I cannot do 2007. Can it be saved in 2003, its unlikely to alter the logic.

Brian

 

[judehamilton]

Okay, I'm attaching it as a 2003-compatible .mdb file. Sorry about that.

 

[Brianwarnock]

Ok I'm a little confused as you originally asked about +or - 30 mins and your query had 60 mins, plus not sure which timestamp you were talking about as your final query not what I expected.

So, I went back to your original posts and decided to do my own thing, ie I did not alter anything of yours,

I created copies of your tables and wrote 2 queries the second a UNION query, actually I did 4, 2 for 30 mins and 2 for 60.
There appears to be duplicates output but on examination the records aren't dups but where high and low readings are equal or span timestamps.

Have a look and comeback with any scathing comments.

Brian

 

[judehamilton]

With a little tweaking, I have what I need with one "little" exception that I swear will solve all this once and for all!! Ha!

I'm attaching "version 2" of the "Scaled down SWMP" database that includes your queries; I modified one of yours and have added four versions of it (one each for H, L, HH, and LL as defined by Charleston), each starting with the words "This finds." The depth values from the VA table returned by each "this finds" query are correct (I am ecstatic) but I need (oh how I need) it to tell me the datetimestamp during which that depth value was recorded from the VA table. Already included is the datetimestamp from the Charleston table...how the hell do I get the associated datetimestamp from the VA table?

I am including all four only so you might have some clarification regarding the info I'm actually seeking, since it is convoluted and I have made it confusing; certainly I don't expect you to make this "final" modification to each of them. If you can tell me how to do it, or show me with one of them, I will be forever (and ever) grateful.

So close I can taste it...can I make you some cookies or something?

 

[Brianwarnock]

I had both timestamps in my final query.
You do not need a UNION query as you have split the MIN/MAX element.
Checkout the Final query CH H in the attached and let me know if it is what you want.
I will not be on again this evening, but will check tomorrow.

Brian

PS I love cookies

 

[judehamilton]

That's it...I wonder why I couldn't figure that out?

You've saved me, and I mean it about the cookies.

Thanks a million!