Compare price fields in same column and select the lowest price.

[Marijn]

Okay, I am totally stuck here, and I hope that you can help me.

I am working with Access 2010.

My table looks like this:

id || Product || Price || Store || Storeno. ||
1 || Hammer || 5,99 || Gamma || 1001 ||
1 || Hammer || 6,99 || Karwei || 1002 ||
2 || Nails || 3,99 || Gamma || 1001 ||
2 || Nails || 3,49 || Karwei || 1002 ||
2 || Nails || 2,99 || Praxis || 1003 ||
3 || Saw || 8,99 || Karwei || 1002 ||
3 || Saw || 6,99 || Praxis || 1003 ||


What I need is a query or code who compares the prices from various products and gives me the cheapest product.

At the end, my table has to look like this:

id || Product || Price || Store || Storeno. ||
1 || Hammer || 5,99 || Gamma || 1001 ||
2 || Nails || 2,99 || Praxis || 1003 ||
3 || Saw || 6,99 || Praxis || 1003 ||

I hope you guys can help me.

Thanks.

 

[arnelgp]

SELECT DISTINCT T1.ID, T1.PRODUCT, (SELECT TOP 1 T2.store FROM store AS T2 WHERE T2.id = T1.ID ORDER BY 1 DESC) AS STORE, (SELECT TOP 1 T3.price FROM store AS T3 WHERE T3.id = T1.ID ORDER BY 1 DESC) AS PRICE FROM store AS T1;


replace table name (store) with correct table name.

 

[Marijn]

Fixed it! Thanks

 

[arnelgp]

update, the order by should be ASC (ascending) and not DESC.

 

[boerbende]

Praxis is cheaper than Karwei?

 

[Marijn]

Lol

Now I need to fix my price tables. XD

 

[Marijn]

I fixed the problem using 2 queries btw.

qry1:
Select Id, Min(Price) As Minprice
From tbl_products
Group by Id;

qry2:
Select qry1.id, qry1.Minprice, tbl_products.Winkel
From qry1 as qry1 Inner Join tbl_products As tbl_products On qry1.id = tbl_products.id and qry1.MinPrijs = tbl_products.price;


Probably not the best way to fix it, but it gets the job done

 

[arnelgp]

sorry about that, just to correct, it's ORDER BY price ASC on my query.