By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,652 Members | 1,468 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,652 IT Pros & Developers. It's quick & easy.

Totals Group Query

P: 39
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
Feb 5 '08 #1
Share this Question
Share on Google+
3 Replies


Expert 100+
P: 344
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
Expand|Select|Wrap|Line Numbers
  1. SELECT TESTTABLE.Code, TESTTABLE.group, Min(TESTTABLE.price) AS MinOfprice
  2. FROM TESTTABLE
  3. GROUP BY TESTTABLE.Code, TESTTABLE.group;
  4.  
Then create a new query based on TESTTABLE and querytest1 and join minofprice to price to get the test field you want, like this
Expand|Select|Wrap|Line Numbers
  1. SELECT TESTTABLE.Code, TESTTABLE.group, TESTTABLE.test, TESTTABLE.price
  2. FROM TESTTABLE INNER JOIN qryTest1 ON (TESTTABLE.price = qryTest1.MinOfprice) AND (TESTTABLE.group = qryTest1.group) AND (TESTTABLE.Code = qryTest1.Code);
  3.  
  4.  
Feb 5 '08 #2

P: 39
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
Feb 5 '08 #3

NeoPa
Expert Mod 15k+
P: 31,266
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
Expand|Select|Wrap|Line Numbers
  1. ,
  2.          [Group]
Feb 5 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.