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

Relevant Pages

  • Re: Weekly crosstab query
    ... queries for the other information and then linking the queries together by ... ensure that the queries return data for the same set of employees. ... Query Two uses query one in place of the OvertimeTracking table ... INNER JOIN qPrior ON ...
    (microsoft.public.access.queries)
  • Re: Query Error Message
    ... Are you building a UNION query? ... You can simplify it by removing the ORDER BY in each of the three queries ... > Single_Insured.) INNER JOIN (Single_Commissions INNER JOIN ...
    (microsoft.public.access.queries)
  • RE: Aggregates, Joins, I am totally stuck on this issue
    ... a left outer join it makes it an inner join? ... I have two aggregate queries which are grouped on ... >> query and then create an append query which puts the results of this master ... >> a LEFT OUTER JOIN and run it and what do it get? ...
    (microsoft.public.access.queries)
  • Re: Getting the High and Low Tides from a Series of Data
    ... queries, sub queries and virtual table) will extract all occurrences ... of an extremum. ... I prefer method 3, a total query, because it ... FROM myTable INNER JOIN query1 ...
    (microsoft.public.access.queries)
  • Re: Getting the High and Low Tides from a Series of Data
    ... queries, sub queries and virtual table) will extract all occurrences ... of an extremum. ... I prefer method 3, a total query, because it ... FROM myTable INNER JOIN query1 ...
    (microsoft.public.excel.misc)
Loading