Lookup table combining 2 fields

[jhoff]

I am working on a database that has a "People" table with a unique key, and separate fields for last name and first name. I would like to create a lookup table that allows a search by both last name and first name to return the key value ID field.



When I tried to link both fields in the relationship table with a left outer join, so that I won't 'lose' any records that don't have an exact match, I get a message that 'the SQL statement cannot be executed because it has ambiguous outer joins'.

Is there a way to combine 2 fields into a single lookup without modifying the tables?

[rpeare]

you don't really need a lookup table

let's say you have a table

tbl_People
PeopleID PeopleFN PeopleLN
1        Mickey   Mouse
2        Donald   Duck

If you create this query

SELECT * FROM tbl_PEOPLE WHERE ((PeopleFN) = [Enter FN] and (PeopleLN) = [Enter LN])

this will show you the results (this assumes it's possible to have multiple people with the same fn/ln which you have to provide for in any database).

From there you can modify the query if you are lookup up based on the contents of a form etc by exchanging the [ENTER FN] with something like forms!<formname>!<fieldname>.

I just want to caution that any time you look up an ID on your table you have to provide for the possibility that you could have multiple people with the same FN/LN, even if you don't currently have that situation it may happen and if you don't program for it your database will not perform as expected if it does happen.