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.