Many to Many Issues

[Guest]

I am new to Access and am trying to produce a database for bidding. I have
created a table called "Suppliers" with simple name contact person and
telephone info, and another table called "Product Summary" that will include
a list of common materials that we use for our bids. This latter table
includes that material name and will hopefully include the lowest current
price, and the current price from a "prefered supplier". We purchase many of
these items from different suppliers based on price and availability at any
given time. So at the time we prepare a bid, we simply would like to enter a
list of materials that are needed, and have the database look at the current
prices (that we have entered) for each supplier that that material is
available through, analyze the difference between the lowest of those prices
and the price from the prefered supplier, and insert the appropriate price
into the bid. I have read quite a bit of the tutorial information, but am
unsure how many tables are needed and what should be contained on each. Any
pointers would help out greatly. Thanks much

 

[Larry Daugherty]

You have a good start on a product specification for your prospective
application. But your request for pointers to implement it is too
broad to be addressable except in similarly broad terms.

To do it using Access you'll have to learn Access or rent or buy the
skill from someone who already knows it.

You didn't specify directly but it seems implicit that you already
have some machine readable information from the current process. It
it's at all reasonable to do so, use the existing information in its
current form. Either link to that information or import it as best
meets the needs of your application.

You have to supply the missing details for all of the things that
don't yet exist to get you from where you are to where you want to be.

HTH

 

[Graham Mandeno]

Hi Matt

The simplest setup would be to have three tables. It sounds like you
already have two of them, but you don't mention that they have primary keys,
and these are crucial to the design. A primary key uniquely identifies a
record in that table and is used to link that record to "foreign keys" in
other tables. Your Suppliers and Products tables both require primary keys.
If you don't have a "natural" primary key (such as ProductCode or
SupplierNumber) then just add an AutoNumber field to those tables.

So, you will have three tables as follows:

Suppliers:
SupplierID (primary key)
SupplierName
ContactName
... etc

Products:
ProductID (primary key)
ProductName
... etc
(note that the price does NOT go here)

SupplierProductPrices:
SupplierID (foreign key to Suppliers.SupplierID)
ProductID (foreign key to Products.ProductID)
CurrentPrice
(SupplierID and ProductID should form a composite two-field primary key
to prevent duplicate records for the same supplier/product pair)

Then you should use the relationships window to create relationships between
the two SupplierID fields (one-to-many) and the two ProductID fields
(one-to-many).

For maintenance, you can create a subform based on the SPP table showing a
combo box for the product and a textbox for the price. Add this subform to
your Suppliers form and it will list all the products sold by that supplier,
with their prices. You can easily update this with new prices when you get
a new pricelist from a given supplier.

Similarly, add a subform to your Products form, listing all the suppliers
for a product with their prices.

The minimum current price and matching SupplierID for a given product can
then be ascertained by a simple query:

Select top 1 SupplierID, CurrentPrice from SupplierProductPrices
where ProductID=<some ProductID> order by CurrentPrice