Results 1 to 3 of 3

Adding all values from multivalued field into single row

  1. #1
    robertluke is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2019
    Posts
    1

    Adding all values from multivalued field into single row

    Hello,

    I am trying to concatenate some field values into a single row.
    Fields from Employees table: FirstName, LastName, Suffix, License
    • The Suffix and License fields can take on multiple values i.e.,
    Suffix = Jr., Sr., II, III, IV, USA (retired), USAF (retired), USN (retired), USMC (retired)
    License = PE, RA, RLA, PhD, ...
    So it is possible for each employee to take on multiple values from each of the Suffix and License fields, or no value at all.



    In my query, I am trying to output a single field that gives a display name for the employee in the format: FirstName LastName Suffix License
    For example I would like the output to be: Robert Jackson, Cara Montgomery (USAF retired), Bob Williams Jr., PhD
    Since not all employees will require a suffix or license, I have used the IIF statement to comb through and output the info if it does appear, otherwise it just gives the FirstName and LastName.
    Here is the statement I currently have:

    Employee Name: IIf(IsNull([Suffix]),IIf(IsNull([License].[Value]),[FirstName] & " " & [LastName],[FirstName] & " " & [LastName] & ", " & [License].[Value]),IIf(IsNull([License].[Value]),[FirstName] & " " & [LastName] & ", " & [Suffix],[FirstName] & " " & [LastName] & ", " & [Suffix] & ", " & [License].[Value]))

    The only problem that I am encountering is that the License.Value returns the value of the multivalued list as seperate rows, where I would like it to return them all in a single row.
    Say Jan Miller has a PE license and a PhD, the output is: row1: Jan Miller PE. row2: Jan Miller Phd. Where I am trying to output row1: Jan Miller PE, PhD.
    Note that I currently have not made the Suffix field a multivalued field, as I wanted to start simple then build up by trying to limit the troubleshooting to just one field, then work with two.
    Any help, commments, or advise is greatly appreciated. Thank you.

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,102
    Many will advise you to avoid multiple valued fields. See this info from isladogs/Colin.
    Better approach is to use explicit tables and relationships. I recommend you tell us more about your application in simple terms, plain English.
    You can concatenate various field values and variables for display.

    Sounds like these tables and relationships could accommodate your needs, but more info
    Code:
    Employee --> EmployeeHasLicense<---License
          +---> EmployeeHasSuffix <--- Suffix
    Good luck.

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,102
    Many will advise you to avoid multiple valued fields. See this info from isladogs/Colin.
    Better approach is to use explicit tables and relationships. I recommend you tell us more about your application in simple terms, plain English.
    You can concatenate various field values and variables for display.See Allen Browne concatRelated

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

Similar Threads

  1. Replies: 2
    Last Post: 06-12-2019, 05:57 PM
  2. Multiple values for a single database field
    By NJH0512 in forum Access
    Replies: 3
    Last Post: 01-30-2018, 07:31 PM
  3. Retrieving Multiple Values into a Single Field
    By Simbiose in forum Queries
    Replies: 6
    Last Post: 08-23-2016, 07:54 AM
  4. Replies: 3
    Last Post: 06-25-2013, 10:57 AM
  5. Replies: 5
    Last Post: 02-20-2013, 03:21 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
  •  
Tech Forums: Microsoft Office Forums