Basic question - aggregate query

[fastrunr]

Hi,

I know this is a really basic question...

I have a table of results for locations. Each record is assoc. with a date, time, and depth. Some locations have multiple results.

How do I select the record (including relevant depth, date, time, etc.) for each location at which the result is the max value??

Thanks!
Liz

 

[steven290]

i beleive its

select max(fieldname) from tblname

 

[fastrunr]

Yes, I realize that, but how do I select other fields associated with the max value *without* grouping by those fields?

For example, if samples were taken from Point A at 3,5, and 7 feet. and the maximum is 100 ppm at 7 feet. I want the query to select:

Point A - 100 ppm - 7 ft

 

[lespaul]

depending on what version of access you are using, this may work, if it doesn't, the second solution will (this is assuming that you want the latest date for each record):

SELECT IDField, depth, date, time FROM tableName INNER JOIN (SELECT IDField, Max(Date) As MaxDate FROM tableName) A on tablename.Idfield = A.IDfield and TableName.Date = A.MaxDate

Save this query:  qryMaxDate
SELECT IDField, Max(Date) As MaxDate FROM tableName

SELECT IDField, depth, date, time FROM tableName INNER JOIN qryMaxDate on tablename.Idfield = qryMaxDate.IDfield and TableName.Date = qryMaxDate.MaxDate

[aside]next time if you will post your table name, field names and perhaps some sample data, someone will probably write a query that you can directly paste to the SQL veiw and have work, since you haven't provided any of that information, you will have to decipher the SQL above and tweak it for it to work[/aside]

Leslie

 

[fastrunr]

This is helpful - I'm almost there. I have the following typed in:

SELECT CopperTable.location, CopperTable.result, CopperTable.units, CopperTable.depthfrom, CopperTable.depthto, CopperTable.date_sampled, CopperTable.time_sampled
FROM CopperTable INNER JOIN (SELECT CopperTable.location, Max(CopperTable.result) AS MaxOfresult
FROM CopperTable) A ON (CopperTable.location = A.location) AND (CopperTable.result = A.MaxOfresult);

I'm getting the following error:
You tried to execute a query that does not include the specified expression 'location' as part of an aggregate function.

Any ideas?

Thanks!

 

[fastrunr]

Never mind - I got it - Woo-Hoo! I needed to do this:

SELECT CopperQuery.location, CopperQuery.result, CopperQuery.units, CopperQuery.depthfrom, CopperQuery.depthto, CopperQuery.date_sampled, CopperQuery.time_sampled
FROM CopperQuery INNER JOIN (SELECT CopperQuery.location, Max(CopperQuery.result) AS MaxOfresult
FROM CopperQuery
Group By CopperQuery.location) A ON (CopperQuery.location = A.location) AND (CopperQuery.result = A.MaxOfresult)
GROUP BY CopperQuery.location, CopperQuery.result, CopperQuery.units, CopperQuery.depthfrom, CopperQuery.depthto, CopperQuery.date_sampled, CopperQuery.time_sampled;


Thanks for your help!!

 

[lespaul]

my bad, forgot the group by clause:

SELECT C.location, C.result, C.units, C.depthfrom, C.depthto, C.date_sampled, C.time_sampled
FROM CopperTable C INNER JOIN (SELECT CopperTable.location, Max(CopperTable.result) AS MaxOfresult
FROM CopperTable GROUP BY Location) A ON (C.location = A.location) AND (C.result = A.MaxOfresult);

Leslie