Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Apr 2001
    Posts
    304
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Concantenating fields in a Lookup field (Access 2003)

    I've got a Technicians table that includes fields for FirstName and LastName. I would like to reference the Technicians table from another table; specifically I have a Technician field in another table and I'd like to grab the technician name from the Technicians table. I know how to setup a Lookup field using the Lookup Wizard and have done that. Only in order to grab the Technician's full name, it has to look at both the FirstName and LastName fields in the Technicians table and, in datasheet view, when you click the down arrow to display the lookup values, it is displaying two columns of information (one for FirstName and one for LastName). Is there anyway to concantenate the FN and LN in the Lookup field so it doesn't look so choppy?

    Hope this makes sense,

    Sat.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Concantenating fields in a Lookup field (Access 2003)

    You can create a query based on the Technicians table. Add the unique identifier (TechnicianID or something like that) and a calculated field:

    FullName: [LastName] & ", " & [FirstName]

    Sort ascending on the FullName field.
    Save this query, and use it as Row Source for the lookup (or for a combo box on a form or report). Set the Column Count to 2 and the Column Widths to 0";1" or something similar.

  3. #3
    3 Star Lounger
    Join Date
    Apr 2001
    Posts
    304
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concantenating fields in a Lookup field (Access 2003)

    Hans - you rule! Are you the only Administrator on this site, cause it seems like you are ALWAYS answering my questions.

    Thanks, as usual.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Concantenating fields in a Lookup field (Access 2003)

    Um, no - see the Moderators List. But I'm on duty the most <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  5. #5
    3 Star Lounger
    Join Date
    Apr 2001
    Posts
    304
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concantenating fields in a Lookup field (Access 2003)

    One more question for you. I created the query, and based my table Lookup field on the query, but I'm concerned with how the information is stored. When you base a lookup field off of a table, Access automatically creates a relationship between those two tables. If I base my look up field on a query that creates a field that wasn't in the table I based the query on, how would I link the two tables. Specifically....I have the Tech table with a FName field and LName field. I created the query to concantenate FName and LName. I created a field in a PO table called "Requestor" and would like the ability to put the technician's full name in there, which I can if I make it a lookup field that's based off the created query. But let's say I wanted to print a report later with PO information and technician info (stuff like tech's email and phone number - info other than just their Full Name), how can I tie the Tech table with the PO table since there is no "Full Name" field in the Tech table (and that would be the information displayed in the PO table).

    Am I going about this the wrong way? Should I create some sort of Full Name field in the Tech table and somehow concantenate the FName and LName fields there?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Concantenating fields in a Lookup field (Access 2003)

    Your Tech table should have a unique ID field that acts as primary key. An AutoNumber field is eminently suitable for this. Let's say it is named TechnicianID.

    The Requestor field in the PO table should not hold the full name of the technician, but the corresponding TechnicianID value (so the Requestor field must be a Long Integer if TechnicianID is an AutoNumber field). You link the Tech and PO tables on the TechnicianID field. You use a combo box (dropdown list) to display the full name corresponding to the TechnicianID field on forms and reports.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •