Select min cost item from many with same item number 2

[kmarr]

I have two tables


Tbl_MarItems (Items that are used in my business)

Auto_MarItemID (primary key)
Str_AltCode
Str_ProdDesc
Str_UOM

Tbl_VendorPrice (prices from suppliers for items that are used in my business)
Str_VendorID
Num_MarItemID (many side of primary key relationship)
Str_VendorItemID
Str_VendorUOM
Cur_VendorPrice
Date_QuoteDate

How can I produce the following:

Select the lowest price (Cur_VendorPrice) vendor for a particular item (Num_MarItemID) and show the following fields in the result:



Str_ProdDesc
Str_UOM
Str_VendorItemID
Str_VendorID
Num_MarItemID
Cur_VendorPrice
Date_QuoteDate

Thank you!

 

[jrbarnett]

Hi,

The following SQL code will do it for you:

Select Tbl_MarItems.tr_ProdDesc, Str_UOM.Str_UOM, Tbl_VendorPrice.Str_VendorItemID, 
Tbl_VendorPrice.Str_VendorID, Tbl_VendorPrice.Num_MarItemID, 
Min (Tbl_VendorPrice.Cur_VendorPrice), Tbl_VendorPrice.Date_QuoteDate
From Tbl_MarItems Inner Join Tbl_VendorPrice Inner Join Tbl_MarItems.Auto_MarItemID On Tbl_VendorPrice.Num_MarItemID
Group By Tbl_MarItems.tr_ProdDesc, Str_UOM.Str_UOM, Tbl_VendorPrice.Str_VendorItemID, Tbl_VendorPrice.Str_VendorID, Tbl_VendorPrice.Num_MarItemID, Tbl_VendorPrice.Date_QuoteDate

Create a new query, move to SQL view, then copy and paste the code into it.

John

 

[Golom]

Try this
[blue][tt]
Select M.tr_ProdDesc, M.Str_UOM, P.Str_VendorItemID,
P.Str_VendorID, P.Num_MarItemID,
P.Cur_VendorPrice, P.Date_QuoteDate

From Tbl_MarItems M Inner Join Tbl_VendorPrice P
ON M.Auto_MarItemID = P.Num_MarItemID


WHERE P.Cur_VendorPrice =

(Select MIN(X.Cur_VendorPrice) From Tbl_VendorPrice X
Where X.Num_MarItemID = M.Auto_MarItemID )

[/tt][/blue]