Simple group by query

[TheMaramatanga]

Hi guys, propably a simple question for you, but I don't get it. My Table:

pkey1 data1 compare1


pkey2 data2 compare2
pkey3 data3 compare3
pkey4 data1 compare4
...

So for one data entry, there are various data entries, and various compare entries for each data entry. What I need ist a tavle with Distinct data, and its Max(compare) value.
I was pretty sure it works like this: Select data, Max(compare) From table Group by data..., but Access keeps sendinf me error messages.
Thanks for any help
Thorsten

[ArviLaanemets]

SELECT data, MAX(compare) AS MaxCompare FROM YourTable GROUP BY data ORDER BY data

Edit (Edit again! It looks like pbaldy is right about this!)
(replace YourTable with your table name, and data and compare with according field names)

[pbaldy]

It would be helpful to know what the error message is. Your pseudo query looks about right. I don't think you want to include pkey, presuming it's a unique primary key, or you'll get no grouping.