Select query help

[King_kamikaze]

Hi,

I have tried a fee different methods but none of which are giving me exactly what i need, i was hoping some one out there might be able to suggest a good approach.

I have the following data.

Source
ID , Product , Price , Count
1 , F2 , 10 , 6
1 , F3 , 20 , 7
1 , F4 , 30 , 5
2 , F2 , 10 , 6
2 , F3 , 20 , 5
3 , F4 , 30 , 5

From this i want to be able to get the rows per ID with the highest price (count is inrelevant i am just indicating that there are other fields in the table)

Result

ID , Product , Price , Count
1 , F4 , 30 , 5
2 , F3 , 20 , 5
3 , F4 , 30 , 5

I have tried various combinations of the Max command and it works fine if i just select the ID & Price but i need it to display the product related to that highest price.

Anyone got any suggestions for me, they will be greatly received.

Many thanks
Tim

 

[MStef]

Hello Tim!

Look at "DemoHighPriceA2000.mdb"

 

[King_kamikaze]

Excellent, thanks for your help there MStef, i did not think of using two inner joins.

I have now created the query i needed, you saved me from the work around i was going to attempt and i really did not want to do that.

Thanks for all your help.

Kind regards
Tim