Multiple companies, same product, different product codes

[Guest]

Is it possible to set up in Access a table with different companies
connecting to a table with products that have different names and codes
depending on the company?

 

[John Vinson]

Is it possible to set up in Access a table with different companies
connecting to a table with products that have different names and codes
depending on the company?

Sure. Sounds like you've just described the table structures.

Are you having some particular problem doing so?

John W. Vinson[MVP]

 

[Guest]

so does this mean that i have to do a table for the company (this I did know)
then a table for the product and then a table for the product codes relating
them by the ID number? - I haven't used Access since 3.1 and it's changed a
bit

 

[John Vinson]

so does this mean that i have to do a table for the company (this I did know)
then a table for the product and then a table for the product codes relating
them by the ID number? - I haven't used Access since 3.1 and it's changed a
bit

There never was an Access 3.1 (it went from 2.0 to 95), but that part
of it hasn't changed from Codd and Date in the 70's...

Yep. You have a many (products) to many (companies) relationship; this
needs three tables - Companies, primary key CompanyID; Products,
primary key ProductID; ProductCodes, primary key probably a two-field
key consisting of CompanyID and ProductID, with a separate field for
Product Code. Note that two different companies might use the same
code for a given product (or, even, alas - the same code for two
different products) so you don't want to make the product code the PK.

John W. Vinson[MVP]

 

[Guest]

Thanks for clarify that John.

There never was an Access 3.1 (it went from 2.0 to 95), but that part
of it hasn't changed from Codd and Date in the 70's...

Yep. You have a many (products) to many (companies) relationship; this
needs three tables - Companies, primary key CompanyID; Products,
primary key ProductID; ProductCodes, primary key probably a two-field
key consisting of CompanyID and ProductID, with a separate field for
Product Code. Note that two different companies might use the same
code for a given product (or, even, alas - the same code for two
different products) so you don't want to make the product code the PK.

John W. Vinson[MVP]