compare

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi
if i have a table like
PartNumber WalMart9 Target8 HomeDepot7
123 $10 $12 $11

CDE $5 $4.5 $5.2


is there anyway I could create a colmn called lowest pick the lowest price
for each column... result
PartNumber WalMart9 Target8 HomeDepot7 LowestPrice
123 $10 $12 $11
$10
CDE $5 $4.5 $5.2
$4.5

Thanks
Ed
 
This would be easy if your table was properly normalized:
PartNumber Store Price
123 WM9 $10
123 T8 $12
123 HD7 $11
CDE ....

Is it possible to change your structure? If not, you could create a union
query and then a totals query that identifies the lowest price.
 
Actually, you are right, the original table looks exactly what you described
but i create a crosstab query to display all the stores in one record, then
want to find out who has the lowest price
 
How about
SELECT *
FROM tblA
WHERE Price = (Select Min(Price) From tblA A Where A.PartNumber =
tblA.PartNumber);
 
Back
Top