Multiple entries

[parsok]

I have two tables. A printer inventory table and a printer model table. The printer model table has the company name and printer model. How can I set this table up to have one entry of the company name with many models under the company?

Then when users enter a new printer into the printer inventory and they choose a model; I would like the company name and the model to appear in the table.

Your assistance is greatly appreciated.

 

[petersdaniel]

Strictly speaking, you would not want to replicate the Company Name to appear in the table since this would be unecessary replication of data. What you want to do is make your database somewhat normalized and use referential integrity to tie pertinent pieces of information together.

My recommendation:

tblCompanies: Contains Companies
CompanyID - AutoNumber
CompanyName - Text (75)
...what ever other data you want

tblModels
ModelID - AutoNumber
CompanyID - Integer / Foregin Key from tblCompanies
PrinterDesc. - Text Description of Printer

tblInventory
ModelID - Integer / InForegin Key from tblModels
Quantity - Integer
Lot Data, Location Data, etc as needed

With this structure, if you created maintenance forms, you could see the manufacturer of a given printer model and inventory by simply joining the Models, Companies and inventory into a single query view







petersdaniel@hotmail.com
"If A equals success, then the formula is: A=X+Y+Z. X is work. Y is play. Z is keep your mouth shut." --Albert Einstein

 

[parsok]

Under the tblModels you have CompanyID - Integer/Foregin Key from tblCompanies. What is a Foregin Key and what option under access do you use to create it? Do you mean to use the look up wizard?

Thanks.

 

[petersdaniel]

A foreign Key is a number that is carried down from the primary table. You identify the primary key / foreign key relationship by going to the Relationship designer up in the toolbar under tools.

The Lookup wizard at the table level does a similar action. petersdaniel@hotmail.com
"If A equals success, then the formula is: A=X+Y+Z. X is work. Y is play. Z is keep your mouth shut." --Albert Einstein

 

[parsok]

Thanks, the light bulb finally came on. I really appreciate the instructions.