Query selects low value item 1

[kmarr]

Two tables:
Table one (Tbl_Items) has following fields:
Auto_ItemId (the one side of the one to many relation)
Str_Description

Table Two has (Tbl_VendorPrice) following fields:
Num_ItemId (the many side of the 1 to many relation)
Str_VendorPartNumber
Cur_VendorPrice
Str_VendorId (the many side of another table)

If multiple vendors provide the same product (Item_Id), how do I create a query to return the lowest price record?

Hope I phrased all this correctly - I am newbie

Thanks, Ken

 

[Golom]

Try
[blue][tt]
Select Auto_ItemId, Str_Description,
Str_VendorId , Str_VendorPartNumber,
Cur_VendorPrice

From Tbl_Items I INNER JOIN Tbl_VendorPrice P
ON I.Auto_ItemId = P.Num_ItemId

Where P.Cur_VendorPrice =

(Select MIN(Cur_VendorPrice) From Tbl_VendorPrice X
Where X.Num_ItemID = I.Auto_ItemId)

[/tt][/blue]

 

[kmarr]

Thanks a lot! I will try it over the weekend