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

[Guest]

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

 

[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;"

 

[Joseph Meehan]

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

Supplier or suppliers? In other words do you want to know what supplier
can fill the entire order at the cheapest price or do you want a list of
what supplies to order which products from for the cheapest price allowing
for orders from more than one supplier? Is there some sort of additional
cost that might need to be factored in if you are using more than one
supplier? How about shipping cost?

 

[Guest]

thanks for the reply I will go and try it now

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.

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

 

[Guest]

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