Syntax help: If the value of Field1 is the same, then max value of Field2

[Kirsti]

Hi there,

Hoping someone can help me, as I'm having a brain freeze this morning. I have moved teams at work and am now working with totally different data and I can't figure this out, although I'm sure it isn't hard.

For example:

Field1 Field2
John 10
John 8
John 12

I need a query to bring through only Line3. i.e. if the value in Field 1 is not distinct, then I only want the line where the value in Field2 is the highest (in this case 12).

Any help much appreciated.



Kirsti

[Bob Fitz]

Create a "Totals" query. GroupBy Field1. Max Field2.

[Kirsti]

I knew it was something simple... couldn't see the wood for the trees. Thanks, worked perfectly.

Kirsti

[Bob Fitz]

Your welcome.