Access 2016 multi-value fields

Question

I'm trying to use a query to concatenate several fields together to create a sentence...

One of the fields I need to use is a multi-value field...

If I put just that field in my query it returns the list of all of the items in the field.

As soon as i try to concatenate anything else on it I get the error about not being able to use a multi-value field...

I don't really care how I accomplish this... I only went with query since I have already written a bunch of them to make sentences

with regular fields and it worked great.

Additional Info...

[Fname] - short text

[Lname] - short text

[ItemsBrought] - Multi-value field (it's an allow multiple selections combo box on my form)

fname - John

lname - Sharp

itemsbrought - chair, table, multi-tool

sql

select [Fname] & " " & [Lname] & " brought these items with him..." & [ItemsBrought]

from mytable

(The above SQL works fine up until I put the [itemsbrought] field into the query)

my intended result is...

John Sharp brought these items with him...chair, table, multi-tool

Answer 1

I've posted an amended copy of your file to as JohnSharp.accdb to my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

You table did not actually include a multi-valued field, so rather than using that, I've modelled the many-to-many relationship type between notes and skills in the conventional way by means of a table which resolves the relationship type into two one-to-many relationship types.  Take a look at the relationships window to  see the model.

In your skills table you'd also use multiple columns for the skill names, where you should have used a single column with multiple rows.  I've corrected this structure.

For data entry I've amended your form by including a subform within the parent notes form.  Multiple skills per note can thus be entered by inserting multiple rows in the subform.  Note how a new skill, not currently repre3sentedin the combo box's drop down list, can be entered into the skills table by typing the new name into the combo box.  Code in the combo box's NotInList event procedure then, subject to user confirmation, inserts a new row into the skills table.

To concatenate the skills into a comma separated list I've added a function, which you'll find in the basGetSkills module.  This is a function specific to this task, rather than generic one like that Daniel referred you to earlier, or that in the Con cat.zip file in my OneDrive folder.  It is similar to that which I posted in my earlier reply, but in this case for use with a conventionally modelled many-to-many relationship type, rather than one modelled by a multi-valued field.

I've included a query which calls the function, and a simple report based upon the query.

Answer 2

The only part I am having trouble with is getting all the values out of the multi-value field.

I can assure you that calling the function I posted in a query does do this.  It should be a simple task for you to amend the function with your own table and column names, provided that the multi-valued field in your case is referencing the surrogate numeric key of a referenced table, as it should do in a well designed database.  If your multi-valued field is unwisely storing the data as a value list, in contravention of Codd's Information Rule, I can amend the function accordingly.

Answer 3

Thank you both for those suggestions.  I had actually already found those two items already and I don't think they will work  Maybe I'm just not seeing it.

The only part I am having trouble with is getting all the values out of the multi-value field.  The suggestions you posted look like they will access all the values in a column (all the values in a single field over many records)...

I'm trying to get at all the values in a single field (multiple items checked in a combo box bound to a field with allow multiple values turned on.

I think Access uses some invisible system table to hold the value(s) for the field but I have no idea how to access it.

Does that make sense?

Thanks again

Answer 4

Take a look at the demo database found at

http://www.rogersaccesslibrary.com/forum/generic-function-to-concatenate-child-records_topic16.html

Answer 5

I think you'll need to use a concatenation function.  The following is one which returns a list of cities for each contact form a table Contacts which contains a multi-valued field named Cities which references the primary key CityID of a table named Cities containing a text column City:

Public Function GetCityList(lngContactID As Long) As String

    Dim strSQL As String

    Dim rst As DAO.Recordset

    strSQL = "SELECT City FROM Contacts " & _

        "INNER JOIN Cities " & _

        "ON Contacts.Cities.Value = Cities.CityID " & _

        "WHERE ContactID = " & lngContactID

    Set rst = CurrentDb.OpenRecordset(strSQL)

    With rst

        If Not (.BOF And .EOF) Then

            .MoveLast

            .MoveFirst

            Do While Not .EOF

               GetCityList = GetCityList & ", " & .Fields("City")

               .MoveNext

            Loop

        End If

    End With

    If Len(GetCityList) > 0 Then

        GetCityList = Mid(GetCityList, 3)

    End If

End Function

It can be called in a query like this:

SELECT FirstName & " " & LastName & ": " & GetCityList(ContactID)

AS ContactLocations

FROM Contacts;