Maximum Values only returned

[lynxbci]

I have a query that produces 3 fields

Style code
colour
order qty

data sample
ABCD123 RED 1100
ABCD123 BLUE 900
ABCD123 GREEN 450
ADDD222 BLACK 1000
BABA444 GREY 1000
BABA444 BLUE 1000

I only want to see the max of each style code
ABCD123 RED 1100
ADDD222 BLACK 1000
BABA444 GREY 1000 (if 2 are the same either will do)

How do i do this in a query

Thanks very much

 

[Dennisk]

You require a totals query. Create a normal Select query, Add the style field and the order quantitiy to the grid, then convert to a totals query. I do this by selecting View/Totals from the menu but it may differ from version to version. Then set the type of total for the style to GroupBy and for the Order Quantity set this to Max.

 

[lynxbci]

This works but doesn't bring the colour. I need the colour returned as well as the max value it is against

 

[Atomic Shrimp]

In the query grid, change the totalling property on the colour column to 'first'

 

[lynxbci]

Brilliant, thankyou very much