Re: Group By, Max and Min

What happen if info for the record with the min value differs from info from
the record for the max? which info value will we keep? If info is the same
for all records GIVEN field1, field2 and field3, then use:

SELECT field1, field2, field3, LAST(info), MIN(time), MAX(time)
FROM query
GROUP BY field1, field2, field3
ORDER BY field1, field2, field3


If 'info' is not the same for all record, you are likely having two
different values (one associated to min, one to max) and that is why your
actual design cannot be further reduced (in number of rows), unless you
carry info_min and info_max, two info values, in the final result, as two
fields (un-normalized result look ).




Hoping it may help,
Vanderghast, Access MVP



"Aino" <aij@xxxxxxxxx> wrote in message
news:5fb5ed80-603f-4f0d-80e6-1e6f5b5f7b41@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I am struggling with a query, and need help

I have (from a previous saved query) these fields:
Field1, Field2, Field3, Time, Info

What I want to return is the maximum and minimum time along with the
corresponding infos, grouped by the first 3 fields:
Field1, Field2, Field3, Min(Time), Max(Time), InfoForMinTime,
InforForMaxTime

Sofar I have
SELECT T1.Field1, T1.Field2, T1.Field3, T1.Info, Min, Max
FROM [Query] AS T1 INNER JOIN
(SELECT Field1, Field2, Field3, Max(Time) AS Max, Min(Time) AS Min
FROM [Query]
GROUP BY Field1, Field2, Field3) as T2
ON T1.Field1= T2.Field1 AND T1.Field2=T2.Field2 AND
T1.Field3=T2.Field3 AND (T1.Time = T2.Max OR T1.Time=T2.Min)
ORDER BY Field1, Field2, Field3;

Obviously, I only get one Info field from this, and twice as many
records as I want, identical 2 and 2 apart from the Info field.


.

Relevant Pages

  • Re: Transpose data
    ... If all of the fields are of the same datatype, you could create a Union ... SELECT Field1, ... SELECT Field2, 2 FROM MyTable ... SELECT Field3, 3 FROM MyTable ...
    (microsoft.public.access.macros)
  • Re: Using flexible query criteria
    ... criteria for the query is based on the user's input in the main form. ... One of the user inputs is a field called "Vendor_Name" (in the form of ... and said that Field1 is the primary key and is an autonumber field, and Field2 is the field shown in the combo box, you must modify it to a UNION select, similar to ...
    (comp.databases.ms-access)
  • Re: flip this query
    ... select field1, field2, field3 from myTable ... tall-thin table than to normalize a wide-flat spreadsheet! ...
    (microsoft.public.access.queries)
  • Re: need to delete record having two different fields with same identical value
    ... Assuming you're always comparing the same two fields (Field1 and Field2) ... Run the query to check it, and then turn it into a delete query. ... which might be a lot safer (I hate deleting data unless I ...
    (microsoft.public.access.gettingstarted)
  • Re: Count Problem
    ... Access MVP 2002-2005, 2007-2008 ... Query as below: ... But after running, the result of field2 is correct, while field1 is the total records number of table x, not what I wanted. ...
    (microsoft.public.access.queries)
Loading