I have an Access application that I am building to create some reporting. I have all of my lists linked however I have a hiccup when it comes to the people fields. All of the people picker fields are set to multiselect.
Looking at the linked table data with a query it looks like:
This makes sense so I joined to the UserInfo table and I thought I would get:
Last, First; Last, First
Instead I got two records. For reporting, two records would be bad because it would artificially inflate any counts or sums. When you export to Excel straight from the SharePoint list you get the data the way I'd need it.
The question I have is how do I get my query to display as one record with the names instead of the UserInfo IDs?
You cannot use a make table query because Access tells you that a multi-valued field cannot be part of a SELECT INTO statement.
I tried to create a custom function whereby if I find a ";" then I need split the names and look them up individually. I get the same basic error. The multi-valued field is not a valid argument. I tried setting the parameter as a string, a variant and not defined and I get the same thing every time.