Table Relationship Question

[nborck]

I have two tables, table 1 with client contact data and table 2 with client needs, both are auto numbered. I have two fields that are titled LastName & FirstName that appear in both tables. When I enter a new client in table 1 I want table 2 to automatically be updated with the clients first and last name. For example: If both tables have 4 records and I enter a new client in table 1 I want table 2 to be updated with the new client first and last name information,so both tables would now have 5 records. How do I create this relationship? Any help would be appreciated. If I haven't explained this well please let me know. Thanks.

 

[trucktime]

Relationship

I think the way you set up the tables is incorrect.
First- and Lastname fields should not appear in 2 tables because it creates overhead and violates the rules of normalization

Probably the best way is to have 3 tables:
Table 1: basic client info
Table 2: contact info (phone, fax, email etc.)
Table 3: client needs

Then you need to create one-to-many relationship between Table1 and Table2 and between Table1 and Table3.

Hope this helps

 

[RV]

Trucktime,

why would you set up a seperate table to hold the client info?
No practical need for that...

nborck,

What you need is to define a 1 to many relationship between your tables.
You need to include a foreign key in tblClientNeed to establish this relationship.
Create a form based on your client data and create a subform in this form based on your client needs.
Link the two forms.

If you need further help, search the forum and review sample databases such as the NorthWind sample database.

RV

 

[trucktime]

Relationship

RV,

I usually have a seperate table for contact info since some clients have several departments, phone numbers, extensions, email addresses etc.
That way I can store unlimited info without having to create fields in the main table that may not be used for clients that only have one of each or may be none at all.