Removing duplicates

[Guest]

im wondering if someone can help me

I have a database with 2 fields a and b. I have 5 records with the following
values

1, 3
1, 5
1, 8
2, 1
2, 5

i want to remove the duplicates in the field a, where the value in b is
maximised
i.e. leaving the records

1, 8 and
2, 5

Any ideas how i would go about doing this??

Thanks in advance

 

[Guest]

You can create a sub query that return that resault

Something like:

Select T1.a , T1.b From TableName As T1
Where T1.b In (Select Top 1 T2.b From TableName as T2
Where T2.a = T1.a Order By T2.b Desc)

You can use this query instead of creating a new table.
Or, create a new table where fields a and b are the keys, append the data
from the query created above, and the key will stop duplicates entered again

 

[Douglas J. Steele]

Create a Totals query based on the existing table. The SQL will be:

SELECT a, Max(b)
FROM MyTable
GROUP BY a

Use that query as the basis of a Make Table query to create a new table.

Once you've created the new table, you can delete the old one.