How to use relationships to mirror a mutual field

[HeyYouYesYouNoNotYouYou]

Hi,

I am creating an inventory for producing orders. I have a supplier table and a product table at the moment. I would like the product table to show the supplier name based on the supplier code in the supplier table. I have created a relationship between the primary key in the supplier table and the supplier code field in the product table, but i am unaware how to mirror the mutual records into the product table. Could somebody please help me out? Sorry for the complicated question.

Thanks in advance,

HeyYouYesYouNoNotYouYou

 

[jackd]

I'm not sure of what you mean by mutual records.

Here is a free data model that has more info than you have described, but it should help with your tables and relationships.

Products, Orders and Deliveries Data Model

 

[HeyYouYesYouNoNotYouYou]

Thanks for the link. I have tried to think of an easier way to explain this; basically what i want is field data to change/update based on another field data. For example say i have a table which records suppliers and stored in there is supplier "A" with ID 1 and supplier "B" with ID 2. I would want the data for supplier to update or change in the products table based on the code which is entered into the supplier ID code field in the product table. So say a vase is supplied by supplier A, i would enter the number one into the supplier ID field and the database would then enter "A" into the supplier name field.

Thanks

 

[jackd]

If you have multiple Suppliers who can "supply" multiple Products, I think you have a Many to Many relationship.
To resolve this set up, you use a junction table.
You create relationships along these lines:
Supplier--->SupplierProduct<-----Product

see this video Creating a Many-to-Many Relationship in an Access Database - YouTube

 

[HeyYouYesYouNoNotYouYou]

Thanks for the help

 

[jackd]

You're welcome. Getting your tables and relationships set up to support your business is key to a useful database application.
Good luck.

 

[Kyle123]

This isn't a many to many relationship. I don't use access, but you don't have the supplier data in the the products table. The only thing that should be in the products table supplier related, would be the supplier primary key.

If you want to see the supplier info then you run a query to join the tables.

 

[jackd]

Kyle123,

Probably, but if the Supplier supplies many Products, and If a Product can be supplied by many Suppliers, then it's possible that ProductA was supplied by SupplierX and other ProductA was supplied by SupplierQ. I was suggesting a general case, since I don't know the specifics. I recommended a video for the original poster to review. He/she can determine if the many to many is real.

See the Manufacturers and Products in this model.
Products and Manufacturers Data Model

I set up relationships in the relationships window for referential integrity.

 

[jackd]

Here's another example of the many Suppliers to many Products where a junction table Suppliers_Products is used to resolve the M:M.
see the model at Data Model for Supply Chains

 

[HiTechCoach]

Thanks for the link. I have tried to think of an easier way to explain this; basically what i want is field data to change/update based on another field data. For example say i have a table which records suppliers and stored in there is supplier "A" with ID 1 and supplier "B" with ID 2. I would want the data for supplier to update or change in the products table based on the code which is entered into the supplier ID code field in the product table. So say a vase is supplied by supplier A, i would enter the number one into the supplier ID field and the database would then enter "A" into the supplier name field.

Use a combo box control.

Note: You only store the supplier's name in the suppliers table. In all other table you only store just the primary key as a foreign key. You use the Primary key to look up the name as needed. By doing this you are using the power of a relational database to avoid storing duplicate data.

I would also recommend taking a look at the Northwind sample database and the the Orders template.


Highly recommended reading: Inventory Control: Quantity on Hand