Lookup Problem with same values

[jdunne]

I'm doing a lookup to pull information from a Contacts Database into the current database.

The matching fields for the relationship are the contact names.

When I enter a contact name it pulls in the persons email, mobile, phone and fax numbers.

Everything works fine for the most part but when there are a number of contact names the exact same (e.g two John Smiths) the lookup always picks the first John Smith it encounters in the Contact names database and pulls in their information.

How can I get the lookup to pull in the information from the correct record in the Contact Names Database

[RalphL]

This is why we use Key fields. A key should be unique, it should not have any meaning other than being the key. You could add an auto-entered serial number and use the replace command to populate it. Or you could make a calculated field = Name & Address and use this a the key.

Do you really want lookups are do you want related data?

[jdunne]

Thanks RalphL

Your suggestion solved my Problem.

I created a new calculation field (CombineKey) in both tables that combined our unique "Contact Code" for each company listed with their "contact name"

I then setup a relationship between these two new matching fields and now using the lookup it pulls in data from the correct record

Again Thank You for your help