Several tables with one common field 1

[Rmcta]

I need to create a database. One main table with field Client_ID. All other tables will always refer to the main table through the Client_ID column.
I created the first table and made <b>Client_ID</b> the primary key.
When creating all other tables:
1) May I create a column Client_ID / Use lookup wizard in datatype to make users pick the client id from the main table? If yes, would this Client_id column (in my other tables) be the primary key of the table they belong to?
If not, do I need to create a primary key column for each table (example an autonumber column?)
2) Do I then need to manually create the relationships?
From MainTable.Client_ID to all other tables' Client_ID?
Thank you!

 

[JeremyNYC]

I would stay away from the lookup function in your tables. What you want to do is have the ClientID (do you really want to type that underscore character all those times?) field in the other tables, but it will not be the PK. Think of it this way: if you have tblClient and tblHat, there will be a HatID that will be the PK of tblHat, and the field ClientID in tblHat will be a foreign key, a way to link to tblClient.

Yes, you should build the relationships manually, in the relationship window.

There's a long article on my website by one of the best in the business, Paul Litwin, about data normalization, which is the topic that will explain all of this to you.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.


Remember to reward helpful tips with the stars they deserve.

 

[Rmcta]

Thank you Jeremy.
I went to your web site to read the article and found it very useful.
I also removed the lookup function from the table as well as the underscore
Thank you for all your comments.
Rmcta