I have 6 suppliers providing the same 1000 products who's cheapes

C

colvin

I have 6 suppliers providing over 1000 products (tropical fish) how can I
find out who is selling a particular fish at the cheapest price. IE I want to
type an order of say 200 items and I want the database to tell me what
suppliers I need to use Thanks
 
A

Allen Browne

Suggest these 3 tables:

Product table: (one record for each product)
ProductID primary key
Product name of the product.

Supplier table: (one record for each supplier)
SupplierID primary key
Supplier Text (name of the supplier)
Priority Number between 1 to 10, where preferred suppliers are
1.

ProductSupplier table
ProductID foreign key to Product.ProductID
SupplierID foreign key to Supplier.SupplierID
SupplierCode This supplier's order code for this product.
UnitPrice Currency (amount each)

Now, if you just want the cheapest price for a product, you could put this
in the Control Source of a text box on the form where the ProductID is
selected:
=DMin("UnitPrice", "ProductSupplier", "ProductID = " &
Nz([ProductID],0))

If you want to know the best price, who supplies it, and what is their order
code, you could use this query:
SELECT UnitPrice, SupplierCode, ProductSupplier.SupplierID
FROM Supplier INNER JOIN ProductSupplier
ON Supplier.SupplierID = ProductSupplier.SupplierID
ORDER BY ProductID, UnitPrice, Priority;

If you want to get this information programmatically, you could
OpenRecordset based on this string:
strSql = "SELECT UnitPrice, SupplierCode " & _
"FROM Supplier INNER JOIN ProductSupplier " & _
"ON Supplier.SupplierID = ProductSupplier.SupplierID " & _
"WHERE ProductID = " & Me.ProductID & _
"ORDER BY ProductSupplier.UnitPrice, Supplier.Priority;"
 
C

colvin

thanks for the reply I will go and try it now

Allen Browne said:
Suggest these 3 tables:

Product table: (one record for each product)
ProductID primary key
Product name of the product.

Supplier table: (one record for each supplier)
SupplierID primary key
Supplier Text (name of the supplier)
Priority Number between 1 to 10, where preferred suppliers are
1.

ProductSupplier table
ProductID foreign key to Product.ProductID
SupplierID foreign key to Supplier.SupplierID
SupplierCode This supplier's order code for this product.
UnitPrice Currency (amount each)

Now, if you just want the cheapest price for a product, you could put this
in the Control Source of a text box on the form where the ProductID is
selected:
=DMin("UnitPrice", "ProductSupplier", "ProductID = " &
Nz([ProductID],0))

If you want to know the best price, who supplies it, and what is their order
code, you could use this query:
SELECT UnitPrice, SupplierCode, ProductSupplier.SupplierID
FROM Supplier INNER JOIN ProductSupplier
ON Supplier.SupplierID = ProductSupplier.SupplierID
ORDER BY ProductID, UnitPrice, Priority;

If you want to get this information programmatically, you could
OpenRecordset based on this string:
strSql = "SELECT UnitPrice, SupplierCode " & _
"FROM Supplier INNER JOIN ProductSupplier " & _
"ON Supplier.SupplierID = ProductSupplier.SupplierID " & _
"WHERE ProductID = " & Me.ProductID & _
"ORDER BY ProductSupplier.UnitPrice, Supplier.Priority;"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

colvin said:
I have 6 suppliers providing over 1000 products (tropical fish) how can I
find out who is selling a particular fish at the cheapest price. IE I want
to
type an order of say 200 items and I want the database to tell me what
suppliers I need to use Thanks
Click to expand...
Click to expand...
 
C

colvin

ahh this is where it gets complicated, some of the fish I will import from
malasia say - so I have shipping costs then, other fish will come direct from
the uk so there will be no shipping costs, what I would like to do is to type
in my order and then I need a list of the suppliers charging the least for
one particular fish ie iwill have a report showg say 3 suppliers with the
fish listed that i need to order - Thanks
 
Top