Joining fields

[Phil_L]

Hello,

How do I join several fields in one table to be looked up by one fields in another?

For example, there are 2 fields, 'First Name' and 'Surname', in one table and they are to be looked up by one field called 'Name' in another table.

Cheers,

Phil_L

 

[Mile-O]

For example, there are 2 fields, 'First Name' and 'Surname', in one table and they are to be looked up by one field called 'Name' in another table.

No, no, no...

You need, in your table with the forename and surname, a primary key, such as PersonID - an AutoNumber.

In your other table, you should also have a field called PersonID and this should be a number. You can build the relationship by selecting Lookup Wizard in the Date Type section of teable design.

 

[Phil_L]

Does this select both names? I need the full name to be shown on the second table.

 

[Mile-O]

You are not getting the concept of a relational database. Storing the name in two places is wrong.

Please look at the example attached and note how the name only appears in one table. (tblPersons)

The name in the second table (tblData) is actually a number; the AutoNumber from tblPersons.

 

[Phil_L]

What im trying to achieve is a program which has a database of customers names and an invoice creating sytem which looks up these names to put on the invoice for when they're used again. Any ideas?

 

[Mile-O]

If you store the PersonID in the Invoice table then you can use a query to pull the person's name over.

 

[Mile-O]

 

[Phil_L]

Does this mean instead of selecting the customers name, I'll have to select their PersonID?

 

[Mile-O]

Yes, but with a combobox you can hide the ID so that to all extents and purposes it looks as though the user is selecting the person's name.

For more information, I suggest you do a search on Normalisation.