Dynamic fields?

[graedus]

Hi,

I apologize if this is a redux.

I have two tables, Products and Vendors. I would like to create a third table with a field for each vendor so I can specify price per product per vendor, similar to the example below. Is there a way to have a table draw its fields dynamically from an existing table, or will I have to update the third table's fields every time I add or remove a vendor?

Here's what I'm looking to do:

                 Vendor1        Vendor2        Vendor3
Product1          $5.00          $6.00          $5.50
Product2          $2.00          $1.20          $1.80

Thank you very much.

 

[FoFa]

Typically there are 2 more tables, but 1 more can be done. It is a Vendor Product table and contains a tie to the vendor and to your product. That way you have the vendor's product number and your own. You can keep any data relating to the vendor in this table.

 

[graedus]

How would I go about creating that table? Are there certain keywords I can look up in Access' help file?

 

[Pat Hartman]

You most definitely do NOT want a column for each vendor in your product table. You need a third table that contains a row for each combination of product and vendor with the vendor's price for the product. This structure allows you to have as many vendors as you need without modify your table structure. This type of table is sometimes referred to as a Relation since it provides the link between products and vendors to complete the many-to-many relationship.

tblProductVendorPrice
ProductID (pk field 1)
VendorID (pk field 2)
Price

 

[graedus]

Ah, I get it. Okay, thank you very much.