Results 1 to 2 of 2
  1. #1
    jhoff is offline Novice
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    1

    Lookup table combining 2 fields

    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?

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    you don't really need a lookup table

    let's say you have a table

    Code:
    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Combining Redundant Fields
    By igendreau in forum Queries
    Replies: 3
    Last Post: 07-19-2011, 11:35 AM
  2. Combining Table Fields
    By jsimard in forum Access
    Replies: 2
    Last Post: 02-22-2011, 04:05 PM
  3. Combining fields - iif??
    By annaisakiwi in forum Queries
    Replies: 10
    Last Post: 12-22-2010, 07:49 PM
  4. Combining fields on a report
    By Alaska1 in forum Access
    Replies: 1
    Last Post: 12-15-2010, 12:14 PM
  5. Combining fields
    By cotri in forum Forms
    Replies: 2
    Last Post: 01-18-2010, 12:06 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums