none
Concatenate a display value from a multivalued lookup field with text fields

    Question

  • Hi,

    I am just starting to use Access 2007 and I have recently discovered the joys of multivalued (lookup) fields.

    I am trying to build a simple Query that concatenates the display value of a multivalued (lookup) field with other Text fields from the same Table.

    The Table contains four fields:

    • PersonID (AutoNumber);
    • MainName (Text);
    • FirstName (Text);
    • Title (a combobox table/query - "SELECT [PersonalTitles].[ID], [PersonalTitles].[Title] FROM PersonalTitles;")

    If my Query is based on the following SQL:

             "SELECT People.PersonID, People.Title, [people].[firstname] & " " & [Mainname] AS FullName FROM People;"

    I get three fields containing the information I want (i.e. the second field contains the display value not the lookup value) but this is not how I actually want to see it.

    So, I reckoned that the following SQL should do the trick:

            "SELECT People.PersonID, [People].[Title] & " " & [People].[FirstName] & " " & [MainName] AS FullName FROM People;"

    However, the [People].[Title] contribution to the concatenation part of the formula now shows up as the numeric value of the lookup field (not its display value counterpart as I hoped).
    Clearly I am doing something wrong.
    Can anybody help, please?
    Paul J
    Tuesday, November 22, 2011 4:50 PM

Answers

  • I assume by enquiry you mean query.

    • is it OK to reference more than one Table in an Enquiry?

    Yes.  In fact the ability to join tables in a query like this is one of the fundamental operations in a relational database, whose underlying basis is that each item of data is stored once and once only in a table which models the entity type of which the item of data is an attribute.  This avoids redundancy and the risk of update anomalies which could otherwise occur.  Queries are the mechanisms by which the data from the separate tables are brought together.

    • wouldn't an OUTER JOIN always return People fields even if there were no match with the PersonalTitle table?

    That's very true.  I'd assumed that each person would have a title, i.e. the Required property of the Title column in the People table had been set to True (Yes), but if that isn't the case a LEFT OUTER JOIN should be used.  This does mean that, in the absence of a title the string would have a leading space character, but this can be eliminated by using the arithmetical + operator rather than the ampersand concatenation operator, so allowing for absent first names also:

    SELECT People.PersonID,
    (PersonalTitles.Title + " ") & (People.FirstName + " ") & MainName AS FullName
    FROM People  LEFT  JOIN PersonalTitles
    ON People.Title = PersonalTitles.ID;

    This will suppress the redundant space in the event of a title or first name being Null.  The reason for this is that in arithmetical operations Null propagates, i.e. Null + anything = Null.

    • can you be sure that the ON People.Title statement will provide the lookup field value not the display value (I will check this tomorrow)

    On the basis of your original post, yes, as it seems that the Title column in people contains a numeric foreign key which references the primary key of PersonalTitles.  This is how a 'lookup field' works.  Relating table in this way is fine per se, and how it would be done if the relationship was created independently of the table design.  The problem is that while a 'lookup field' creates the relationship it introduces other problems, amongst which is the fact that the true value of the field is hidden, causing confusion amongst inexperienced users as to what values it actually contains.  For more information on the horrors of 'lookup fields' see:

    http://www.mvps.org/access/lookupfields.htm

    BTW when you refer to a 'multivalue field' I don't think you in fact mean such.  A multivalue field is something introduced into Access principally for interfacing with SharePoint and allows multiple values to apparently be stored in one column in a row of a table (in reality it doesn't work like that under the skin).  From your description it sounds like you have a conventional single value 'lookup field'.


    Ken Sheridan, Stafford, England
    Tuesday, November 22, 2011 10:37 PM

All replies

  • Are you saying that a person can have more than one title?  If so, what do you want to return if a person has two or more titles?  Multiple records, one with each title, or one record with all the titles strung together, or one record with an arbitrarily chosen title from among the titles the person holds?


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html
    Tuesday, November 22, 2011 6:41 PM
  • I think you want:

    SELECT People.PersonID,
    PersonalTitles.Title & " " & People.FirstName & " " & MainName AS FullName
    FROM People  INNER JOIN PersonalTitles
    ON People.Title = PersonalTitles.ID;

    Ken Sheridan, Stafford, England
    Tuesday, November 22, 2011 6:50 PM
  • To Dirk Goldar,

    I bet this is a dumb way to do it but, my People table has Title as its fourth field.  It is drop-down box that presents you with a choice of a Title from one of the list held in the PersonalTitles table.  Oh, and you have to make that choice.

    So what I am looking for in my Enquiry is to concatenate the chosen Title with the FirstName and MainName fields.

    I did read somewhere that it is better to update tables (like my People table) using a Form so that you have more control over what is input/amended (is this right?) - perhaps I should have used a Form with a drop-down box linked to my PersonalTitles table?

    Thanks for your interest.

    Tuesday, November 22, 2011 8:44 PM
  • To Ken Sheridan,

    I can see where your SQL is leading and I shall certainly try this in the office tomorrow.

    A couple of points, though:

    • is it OK to reference more than one Table in an Enquiry?
    • wouldn't an OUTER JOIN always return People fields even if there were no match with the PersonalTitle table?
    • can you be sure that the ON People.Title statement will provide the lookup field value not the display value (I will check this tomorrow)

    Thanks for your response

     

    Paul J

    Tuesday, November 22, 2011 8:52 PM
  • I bet this is a dumb way to do it but, my People table has Title as its fourth field.  It is drop-down box that presents you with a choice of a Title from one of the list held in the PersonalTitles table.  Oh, and you have to make that choice.

    So what I am looking for in my Enquiry is to concatenate the chosen Title with the FirstName and MainName fields.

    I did read somewhere that it is better to update tables (like my People table) using a Form so that you have more control over what is input/amended (is this right?) - perhaps I should have used a Form with a drop-down box linked to my PersonalTitles table?

    Thanks for your interest.


    My question wasn't really about using a Lookup field in the table, though I agree that on the whole it's better to avoid both lookup fields and data entry directly in tables.  But that all depends on how you want to use your database -- for a personal database to be used only by its designer, it's not a problem.

    What I was asking about is whether the lookup field is really designated as a multivalue field, as you stated.  A lookup field is not the same as a multivalue field.  A multivalue field allows multiple selections to be made for the field value in the same record, so that (for example) one person could have multiple titles.  In general, a multivalue field will be represented by a special combo box that allows multiple selections to be checked.  Is that what you have, or do you just have a normal combo box that lets you have only one selected value at a time?  If it's the latter, then a query such as the one Ken Sheridan posted is probably what you need.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html
    Tuesday, November 22, 2011 9:00 PM
  • I assume by enquiry you mean query.

    • is it OK to reference more than one Table in an Enquiry?

    Yes.  In fact the ability to join tables in a query like this is one of the fundamental operations in a relational database, whose underlying basis is that each item of data is stored once and once only in a table which models the entity type of which the item of data is an attribute.  This avoids redundancy and the risk of update anomalies which could otherwise occur.  Queries are the mechanisms by which the data from the separate tables are brought together.

    • wouldn't an OUTER JOIN always return People fields even if there were no match with the PersonalTitle table?

    That's very true.  I'd assumed that each person would have a title, i.e. the Required property of the Title column in the People table had been set to True (Yes), but if that isn't the case a LEFT OUTER JOIN should be used.  This does mean that, in the absence of a title the string would have a leading space character, but this can be eliminated by using the arithmetical + operator rather than the ampersand concatenation operator, so allowing for absent first names also:

    SELECT People.PersonID,
    (PersonalTitles.Title + " ") & (People.FirstName + " ") & MainName AS FullName
    FROM People  LEFT  JOIN PersonalTitles
    ON People.Title = PersonalTitles.ID;

    This will suppress the redundant space in the event of a title or first name being Null.  The reason for this is that in arithmetical operations Null propagates, i.e. Null + anything = Null.

    • can you be sure that the ON People.Title statement will provide the lookup field value not the display value (I will check this tomorrow)

    On the basis of your original post, yes, as it seems that the Title column in people contains a numeric foreign key which references the primary key of PersonalTitles.  This is how a 'lookup field' works.  Relating table in this way is fine per se, and how it would be done if the relationship was created independently of the table design.  The problem is that while a 'lookup field' creates the relationship it introduces other problems, amongst which is the fact that the true value of the field is hidden, causing confusion amongst inexperienced users as to what values it actually contains.  For more information on the horrors of 'lookup fields' see:

    http://www.mvps.org/access/lookupfields.htm

    BTW when you refer to a 'multivalue field' I don't think you in fact mean such.  A multivalue field is something introduced into Access principally for interfacing with SharePoint and allows multiple values to apparently be stored in one column in a row of a table (in reality it doesn't work like that under the skin).  From your description it sounds like you have a conventional single value 'lookup field'.


    Ken Sheridan, Stafford, England
    Tuesday, November 22, 2011 10:37 PM
  • My sincere thanks to both Dick Goldgar and Ken Sheridan.

    Your swift responses were both informative and very useful.

    Paul J

    Wednesday, November 23, 2011 8:12 AM