Having trouble linking

[giulian]

I am still having trouble doing this particular task.

I have one table (Table 1) which contains the Surnames and First Names of people in two different fields (Surnames and First Names).

In another table (Table 2) I have one field which contains the field Full Name. What I need to do is when Table 1 is updated with a Surname and First Name I need Table 2 to update with a Full Name.

E.g.
When I creat a record with the
Surname: Jones
First Name: Tom
In Table 1 I need Table 2 to update:
Full Name: Tom Jones

Thanks in advance

 

[DanJR]

why do you have a table that contains the full name? you should attempt to store the information only once. use a query to display the full name e.g.

select trim$([first name]) & " " & trim$([surname]) as FullName
  from Table1

cheers,
dan

 

[willir]

giulian

Dan is absolutely correct - there is no reason to have a second table, and no reason to even add a field on Table1 to capture the First + Last name.

If this is an excercise in link tables, more specifically creating relationships, it is best to have a unique identifier for each record.

Consider reading...
Fundamentals of Relational Database Design by Paul Litwin
Download document
Read on-line (HTML)


Micro$oft's answer...
283878 - Description of the database normalization basics
304467 - ACC2000 Defining Relationships Between Tables in a Microsoft Access Database

In your Table1, you do not have an obvious unique identifier. LastName may work for a bit, but eventually, you will run into problems when more than one person has the same last name. Eg. Smith, Jones, Lee.

A more typical way of defining the Table1 is to add a unique field. Eg.

tblContact
ContactID - primary key, autonumber
SurName
FirstName

The sole purpose of ContactID is to identify the specific records so you can Smith, John who lives at 123 Lotus Lane and Smith, John who lives at 123 Main Street.

In your case, linking Table1 to Table2 would be pretty difficult with the names you provided. You can not easily create a relationship between [SurName], [FirstName] and [FirstName SurName] -- the only way to guess would be to use code to perform text string comparisons -- not an easy process for new users, and it would still be guessing.

Now instead of using Table2, let say you have a table with phone numbers. This makes sense since a person can more than one phone number...

tblPhone
PhoneID - primary key
ContactID - foreign key to tblContact, ContactID
PhoneType - text, type of phone number
PhoneNo - text field

Note that ContactID is used to link the phone numbers to a specific person.

How to do this.
From you main menu, select "Tools" -> "Relationships". Add your tables. In the example I provided, I would add tblContact and tblPhone. Then click on the primary key in the one table and drag it to the foreign key in the other table. In my example, drag ContactID to ContactID. A window pops up to specify the relationship type - it is a good idea to "Enforce Referential Integrity". This means that there has to be corresponding data in the one table before you can create the related record. You have to have a Contact persion before you create a phone number.

Read the documentation via the links provided.

Richard

In your example,