Query for max value

[Guest]

I need your help on a query. I have a table set up in the following manner

ID percent cover species

10 40 lichen
10 10 bryo
10 30 vascular
20 10 lichen
30 80 lichen
30 20 vascular

What I want is to use the query utility to run a query on this table and for
each ID return the record with the highest % cover. So.....the end result
for this example would be a table looking like:

ID Percent Cover Species

10 40 lichen
20 10 lichen
30 80 lichen


I am a novice user so please explain you answer in as user friendly manner
as possible and then you in advances

Sean

 

[David Lloyd]

Sean:

You will probably need two queries. The first to determine the Max percent
cover and the second to extract the appropriate records from the original
table.

For example (Query1):

SELECT ID, Max([Percent Cover])
FROM TableName
GROUP BY ID

This will give you the first two columns of your desired table. If you then
join this query with the original table on the ID and Percent Cover Fields
you will get the desired result.

For example:

SELECT TableName.ID, TableName.[Percent Cover], TableName.Species
FROM TableName INNER JOIN Query1 on TableName.ID=Query1.ID AND
TableName.[Percent Cover]=Query1.[Percent Cover]

If you have records in the original table that have duplicate value for both
ID and Percent Cover you will get duplicate results. You will have to
decide how to handle this.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


"populating multiple tables"
I need your help on a query. I have a table set up in the following manner

ID percent cover species

10 40 lichen
10 10 bryo
10 30 vascular
20 10 lichen
30 80 lichen
30 20 vascular

What I want is to use the query utility to run a query on this table and for
each ID return the record with the highest % cover. So.....the end result
for this example would be a table looking like:

ID Percent Cover Species

10 40 lichen
20 10 lichen
30 80 lichen


I am a novice user so please explain you answer in as user friendly manner
as possible and then you in advances

Sean