Foreign Key Addition

[Guest]

I'm planning to use the Microsoft Inventory Control template database to
track supplies for my office. For reference, it can be found here:

http://office.microsoft.com/en-us/templates/TC010184581033.aspx?CategoryID=CT061995421033

To be able to sort, input and find products by supplier, would it cause any
problems to add the foreign key "SupplierID" to the Products table? Would I
be able to create forms and reports with products related to suppliers this
way? If it is not the most efficient way to do it, is there another way to
accomplish this?

Thank you

 

[Jeff Boyce]

Jill

Each situation is different. In your situation, could a product (the same
product) be offered by more than one supplier? Could a given supplier have
more than one product? If yes to both, you'll need a "resolver" (or
junction) table to show valid combinations of product and supplier.

Good luck

Jeff Boyce
<Access MVP>

 

[Guest]

Jeff,
Thanks for your response. The same product could be offered by different
suppliers, but for re-ordering purposes, I want to store the product number
(different for each supplier) with each product. Would it be better to just
store each product for each supplier as a different one since those numbers
will be different? Yes, every supplier will have more than one product.
What I would like to use this for is to keep track of inventory, but also
generate a "shopping list" so I can call and re-order, with supplier info,
items needed (with their product numbers) and quantity. How would a
"resolver" table be set up?
Thank you,
Jill

 

[Jeff Boyce]

Jill

tblProduct
ProductID
... (details about the product)

tblSupplier
SupplierID
... (details about the supplier)

trelProductSupplier
ProductSupplierID
ProductID
SupplierID
...(details about this combination, including what the supplier's ID is
for this product)

Good luck

Jeff Boyce
<Access MVP>

 

[Guest]

Thank you, Jeff. I'll start with this and if I run into any more
questions, I'll come right back.
Thank you again,

Jill