Return Lowest Value Only?

[DeanRowe]

hi,

I have a table that contains itemcodes and prices. An example of the records would be:

<Item Code> <Price>
Product001 £34.56
Product001 £49.23
Product001 £23.22
Product001 £98.43
Product002 £12.45
Product002 £54.34
Product002 £25.51
Product002 £76.84
Product002 £14.97

I would like to run a query that returns the lowest value price for each Item code. So in this example it would return:

<Item Code> <Price>
Product001 £23.22
Product002 £12.45

Does anyone know the criteria code in the query design view for this problem?

I know it sounds silly, but there are unique codes in another field, i just need to query against these fields.

Thank you for your help.

Dean

 

[RuralGuy]

Have you tried DISTINCT and TOP 1?

 

[DeanRowe]

In complete honesty I don't understand the terms or context so I'll have to say no on that one, would you mind explaining a little please?

thank you rural guy

 

[RuralGuy]

Sorry Dean,
In SQL, DISTINCT says you want unique values for the field and TOP 1 says return the first record only. Actually now that I think about it further sorting ascending on the ItemCode and Price fields and then using DISTINCT on the ItemCode field should give you what you want.