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 12:48:30 -0400

Hi,

    Erratum: should be

        ...
AND
     myTable.Level IN( query1.ma , query1. mi)

since query1.ma and query1.mi are Level values, not DateTimeStamp values.

Vandergahst, Access MVP

"Michel Walsh" <vanderghast@VirusAreFunnierThanSpam> wrote in message
news:eSVD%23QcvEHA.716@TK2MSFTNGP10.phx.gbl...
> 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
>
>

Relevant Pages

  • Re: ADO.NET not retrieving results correctly
    ... > (Appointments INNER JOIN Buildings ON ... however the data adapter in my code returns 0 results. ... Is there something buggy with using the LIKE clause to ... > send queries through ADO? ...
    (microsoft.public.dotnet.framework.adonet)
  • 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: Getting the High and Low Tides from a Series of Data
    ... > queries, sub queries and virtual table) will extract all occurrences ... > SELECT ActionID, MINAs mi, MAXAs ma ... > FROM myTable INNER JOIN query1 ... > a WHERE clause, if you prefer: ...
    (microsoft.public.excel.misc)
  • Re: Getting the High and Low Tides from a Series of Data
    ... > queries, sub queries and virtual table) will extract all occurrences ... > SELECT ActionID, MINAs mi, MAXAs ma ... > FROM myTable INNER JOIN query1 ... > a WHERE clause, if you prefer: ...
    (microsoft.public.access.gettingstarted)
  • 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)