group by query problem

  • Thread starter Thread starter Marty
  • Start date Start date
M

Marty

Hello,
TIA for your expertise and knowledge. I'm trying to do a
query that will output the max value of Amt for each group
of Lat/Lon pairs. The example below shows why I can not
consider only Lat or only Lon in the group by function; it
has to be both and that's where I run into trouble. My
result returns every record in the table. Any help would
be greatly appreciated.

date Seq Amt Lat Lon
20030911 130 69 30 18.03 -66.572
20030911 132 67 33 18.03 -66.572
20030911 139 64 54 18.07 -66.572
20030911 132 63 33 18.07 -66.572
20030911 139 64 54 18.07 -66.573
20030911 132 63 33 18.07 -66.573

Desired Result:

date Seq Amt Lat Lon
20030911 132 67 33 18.03 -66.572
20030911 132 63 33 18.07 -66.572
20030911 132 63 33 18.07 -66.573
 
Hi,


SELECT Max(Amt) As Maxof, Lat, Lon
FROM tableName
GROUP BY lat, lon


saved it as a query, q1.


SELECT tableName.*
FROM tableName INNER JOIN q1
ON tableName.lat =q1.lat
AND tableName.lon = q1.lon
AND tableName.amt = q1.maxOf


would produce the final result.



Hoping it may help,
Vanderghast, Access MVP
 
I received an error: You tried to execute a query that
does not include the specified expression 'Field1' as part
of an aggregate function. I did exactly as you sugested,
I think. The example I gave was a simplfied version of
what I was doing. This is the query I wrote.

SELECT Max(PueroRico.field3) AS Maxof,
PuertoRicoGSM.Field1, PuertoRicoGSM.Field2,
PuertoRicoGSM.Field4, PuertoRicoGSM.Field5,
PuertoRicoGSM.Field6
FROM PuertoRicoGSM
GROUP BY PuertoRicoGSM.Field5, PuertoRicoGSM.Field6;

Saved query as q1 then:

SELECT PuertoRicoGSM.*
FROM PuertoRicoGSM INNER JOIN q1
ON PuertoRicoGSM.field5 =q1.field5
AND PuertoRicoGSM.field6 = q1.field6
AND PuertoRicoGSM.field3 = q1.maxOf

Any suggestions?

Thank you, Marty
 
Hi,


The first query is wrong. Remove Field1 , Field2 and Field4, to just
keep field3, on which we max( ) on it, and field5 and Field6 on which we
group by on them.

Vanderghast, Access MVP
 
Back
Top