Re: Getting the High and Low Tides from a Series of Data

From: Michel Walsh (vanderghast_at_VirusAreFunnierThanSpam)
Date: 10/29/04 

Date: Fri, 29 Oct 2004 10:32:41 -0400

Hi,

    Sure. In the reference I supplied, methods 1, 2, and 4 (cascading
queries, sub queries and virtual table) will extract all occurrences
(dateTimeStamp) of an extremum. I prefer method 3, a total query, because it
is the only one to be 100% "graphically" compatible with the query editor,
but its limitation is to "naturally" retrieve just one occurrence of the
extremum. Method 1, cascading queries, is probably the next simpler method.
The idea is to make a query that find the min/max value for Level, per
ActionID, in query1:

SELECT ActionID, MIN(Level) As mi, MAX(Level) As ma
FROM myTable
GROUP BY ActionID

and then, used it as virtual table:

SELECT myTable.*
FROM myTable INNER JOIN query1
        ON myTable.ActionID=query1.ActionID
            AND
                myTable.DateTimeStamp IN( query1.ma , query1. mi)

You can change the inner join to a cross join, and bring the ON clause in a
WHERE clause, if you prefer:

SELECT myTable.*
FROM myTable , query1
WHERE myTable.ActionID=query1.ActionID
            AND
                myTable.DateTimeStamp IN( query1.ma , query1. mi)

Hoping it may help,
Vanderghast, Access MVP

"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:OBpyo%23bvEHA.1260@TK2MSFTNGP12.phx.gbl...
> Michael,
>
> Can you extract all of the minimum values from the series using Access?
>
> Bernie
> MS Excel MVP
>
> "Michel Walsh" <vanderghast@VirusAreFunnierThanSpam> wrote in message
> news:umaNk3bvEHA.3728@TK2MSFTNGP12.phx.gbl...
>> Hi,
>>
>> I assume you want the Hi-Low "by ActionID" rather than for the whole
>> table:
>>
>>
>> SELECT a.ActionID, MAX(a.DateTimeStamp), a.Level
>> FROM myTable As a INNER JOIN myTable As b
>> ON a.ActionID = b.ActionID
>> GROUP BY a.ActionID, a.Level
>> HAVING a.Level IN ( MIN(b.Level), MAX(b.level) )
>>
>>
>> That returns just the latest dateTimeStamp having the Hi, or the Low
>> mark,
>> for each ActionID. See http://www.mvps.org/access/queries/qry0020.htm for
>> alternatives.
>>
>>
>>
>>
>>
>> Hoping it may help,
>> Vanderghast, Access MVP
>>
>>
>>
>>
>> "Herbert Chan" <Herbert@Chan.com> wrote in message
>> news:uPAkHKbvEHA.1564@TK2MSFTNGP09.phx.gbl...
>> > Hello,
>> >
>> > I have a long list of data for tide levels which is like as follows:
>> >
>> > Date/Time Tide Level
>> >
>> > Now, I want to extract from this long series of data all the high tides
>> > and
>> > low tides, which in mathematical terms is to extract all the local
>> > maximums
>> > and local minimums, together with the associated Date/Time from the
> data.
>> >
>> > I have another programme that generates this series of data basing on a
>> > set
>> > of parameters, but I don't know what the function is, so I can't really
>> > find
>> > the 1st order derivative and set it to zero to get all the roots, etc.
>> >
>> > How should I go about doing this? Using Excel or Access?
>> >
>> > Thanks.
>> >
>> > Herbert
>> >
>> >
>>
>>
>
>