Access Query- Group By 1

[bernie10]

Hello all,
I have a table that holds different manufacturer products. The fields include manufacturer, price, item type, item description, and comments.

Here is some sample data:

Manufacturer Price ItemType Description Comments
Company1 $5.00 1 desc1
Company2 $4.00 2 desc2
Company1 $5.50 2 desc3 3yr warr.
Company1 $6.00 2 desc4
Company2 $3.00 1 desc5
Company2 $4.50 1 desc6 overstock

Okay, what I then need is to get the lowest price for each item type for each manufacturer. In other words my query should show each company's lowest price on each particular item type. I do this by using the "Group By" and "Min" expressions in the Access design view for my query: I group by Company and Item Type and then find the min price. This does more or less give me the data I need but I would also like to include the Description and Comments field in my query. However if I include those two fields in the Access design grid it tries to "group by" them as well and this gives me improper results. In other words the problem is that when I imput the Group By and the Min in the "totals" field in the design grid for the fields I need, Access then requires me to have a function in the totals row of ALL of the fields I use in my query. This is a problem because I do not want to group by description or comments, I simply want to display them. If I remove the "Group By" function in the totals field for the two fields, comments and description, I get an error: "The query you tried to execute does not include the specified expression as part of an aggregate function."

So basically I want the result of my query to be as follows based on the above sample data:

Manufacturer Price ItemType Description Comments
Company1 $5.00 1 desc1
Company2 $3.00 1 desc5
Company1 $5.50 2 desc3 3yr warr.
Company2 $4.00 2 desc2

I hope my problem makes sense; any suggestions would be appreciated. Please let me know if more clarification is needed.

 

[PHV]

I group by Company and Item Type and then find the min price
Save this query.
Create a new query joining your table with this saved query.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SecondToNone]

Try this:

SELECT t1.Manufacturer, t1.Price,t1.ItemType,t1.Description,t1.Comments from mytable t1 Where t1.Price IN (Select top 2 t2.Price from mytable t2 WHERE t1.ItemType=t2.ItemType AND t1.Manufacturer=t2.Manufacturer ORDER BY DESC)

-SecondToNone

 

[bernie10]

Thank you both for responding... it looks like PH's solution is going to be the one that will accomplish exactly what I need.

Thanks,
CM