Totals Group Query

[JC2710]

Hi

This question should be really easy but I just cant do it!! I have a table below:

Code Group Test Price
56 1 1 2298
56 1 2 1215
56 1 3 1378
56 2 1 492
56 2 2 600

All I want to do is to select the Lowest Price per Group: eg

Code Group Test Price
56 1 2 1215
56 2 1 492

But this seems impossible if I want to include the 'Test' field. So i can get

Code Group Price
56 1 1215
56 2 492

But i wont know what the value of 'Test' is!

Can anyone help?

Thanks

[Lysander]

Hi

This question should be really easy but I just cant do it!! I have a table below:

Code Group Test Price
56 1 1 2298
56 1 2 1215
56 1 3 1378
56 2 1 492
56 2 2 600

All I want to do is to select the Lowest Price per Group: eg

Code Group Test Price
56 1 2 1215
56 2 1 492

But this seems impossible if I want to include the 'Test' field. So i can get

Code Group Price
56 1 1215
56 2 492

But i wont know what the value of 'Test' is!

Can anyone help?

Thanks

You can only do this if there is only 1 lowest price obviously. If this is the case then make 2 queries.

The first I called querytest1

SELECT TESTTABLE.Code, TESTTABLE.group, Min(TESTTABLE.price) AS MinOfprice
FROM TESTTABLE
GROUP BY TESTTABLE.Code, TESTTABLE.group;
 

Then create a new query based on TESTTABLE and querytest1 and join minofprice to price to get the test field you want, like this

SELECT TESTTABLE.Code, TESTTABLE.group, TESTTABLE.test, TESTTABLE.price
FROM TESTTABLE INNER JOIN qryTest1 ON (TESTTABLE.price = qryTest1.MinOfprice) AND (TESTTABLE.group = qryTest1.group) AND (TESTTABLE.Code = qryTest1.Code);
 
 

[JC2710]

Thanks Alot!! This seems to work!!

Seems a long way round but I am very grateful for the quick reply!!

Have been struggling with this for ages!!

Cheers

[NeoPa]

From your test data, I assume you want to group on the Code AND Group fields.
[code]SELECT [code],
[Group],
Mid(Min(Format([Price],'0000000.00') & [Test]),11,99),
Min([Price])
FROM [YourTable]
GROUP BY

,
         [Group]