Multiple companies, same product, different product codes

P

Pointless

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?
 
J

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?
Click to expand...

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

Are you having some particular problem doing so?

John W. Vinson[MVP]
 
P

Pointless

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:)
 
J

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:)
Click to expand...

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]
 
P

Pointless

Thanks for clarify that John.

John Vinson said:
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]
Click to expand...
 
Top