Trouble with a multivalued field

[dafne]

Hello,

I’m having some trouble trying to create a query for a multivalued field. I've read the official tutorials but, as a beginner, I'm not sure the things I'm trying to do are feasible. I hope you can help me.

I work in Linguistics, and I want to describe the elements that appear in a sentence. For instance, John smiled has a SUBJECT (ID = 1), John washed the car has a SUBJECT and a DIRECT OBJECT (ID = 2), John gave me the book has a SUBJECT, a DIRECT OBJECT and an INDIRECT OBJECT (ID = 3). I thought a multivalued field was an efficient way to classify the argument structure of the verbs used in the examples. I also thought it would allow me to see, for example, how many times an element appears overall (alone (e.g. “1”) or with other elements (e.g. “1;2”, “1;2;3”…) and how many times a specific construction appears (for example, “1;3”). Are those things at all possible?

I have another question for you. My database is pretty big and complex, and I’m actually studying different kinds of sentences. To make things simpler, let's call them DECLARATIVE (ID = 1), IMPERATIVE (ID = 2) and INTERROGATIVE (ID =3). Building on my previous question, could I create a query that
1) tells me how many times an element appears (alone or coupled with other elements) in a specific type of sentence? (E.g.: “1” appears 95 times overall in DECLARATIVE sentences (54 times alone, 41 times with other elements))
2) tells me which constructions occur for each type of sentence, and how often they appear? Something like this:

IMPERATIVE (ID = 2) (total: 75)
1 = occurs 26 times
1;2 = occurs 49 times
INTERROGATIVE (ID = 3) (total: 62)
1;2 = occurs 48 times
1;2;3 = occurs 14 times
… etc.​

Thank you for your help,
Dafne

P.S.: I have multiple tables containing my options (for example, tbl_SENTENCE has SENTENCE_ID and SENTENCE_label, tbl_ELEMENT has ELEMENT_ID and ELEMENT_label...). They're linked to controls in a form that populates another table called tbl_DATA.

 

[Pat Hartman]

You will eventually find that experts do not use multi-value fields for several reasons. In my case, almost all my BE's are SQL Server or have the potential of being upsized and since the multi-value field is an Access-only data type, I never use it. There are other issues as well. Mostly, I dislike the implementation and the fact that it requires custom SQL to use.

Someone who does use them might be able to help you but if you would consider just giving them up, it might be easier in the long run.

To implement this the old-fashioned way, requires three tables.
tblPartsOfSpeach where you define all the possible values. You can use a single column table with just the description or preferably, a two column table with an ID as the PK and the description as data. If you use the two field option, you will need to create a unique index on the description field to avoid having someone enter the same value more than once.
tblSentence where you store the data you want to analyze. I don't know what columns this table needs so I can't offer any suggestions.
tblSentencePartsOfSpeach This is called a junction table and is used to implement the many-to-many relationship between the first two tables. In the Multi-Value fields implementation, this table is hidden and that is what causes all the convoluted workarounds and confusion. This table typically contains two fields, the foreign key to tblPartsOfSpeach and the foreign key to tblSentence. The primary key is created by selecting both columns and pressing the key icon to make a compound PK. Occassionally (but probably not in this case), there are other data elements required for this table in addition to the relationship. In that case, you would use an autonumber as the PK and make a unique index on the two foreign keys.

I've attached a database so you can see how a m-m relationship works. It of course has no relationship with what you need to do so I hope you can make the mental translation.

 

[arnelgp]

you can create a Union query
to accomplish this. first create
small queries like this:

qry1:
select "1" As Data, Count(MultiValueFieldName.Value) As CountOfData From yourTable Where MultiValueFieldName.Value=1 Group By "1"

qry2:
select "2" As Data, Count(MultiValueFieldName.Value) As CountOfData From yourTable Where MultiValueFieldName.Value=2 Group By "2"

qry3
select "3" As Data, Count(MultiValueFieldName.Value) As CountOfData From yourTable Where MultiValueFieldName.Value=3 Group By "3"

qry1_2:
select "1;2" As Data, Count(MultiValueFieldName.Value) As CountOfData From yourTable Where MultiValueFieldName.Value=1 Or MultivalueFieldName.Value=2 Group By "1;2"

qry1_3:
select "1;3" As Data, Count(MultiValueFieldName.Value) As CountOfData From yourTable Where MultiValueFieldName.Value=1 Or MultivalueFieldName.Value=3 Group By "1;3"

qry2_3:
select "2;3" As Data, Count(MultiValueFieldName.Value) As CountOfData From yourTable Where MultiValueFieldName.Value=2 Or MultivalueFieldName.Value=3 Group By "2;3"

qry123:
select "1;2;3" As Data, Count(MultiValueFieldName.Value) As CountOfData From yourTable Where MultiValueFieldName.Value=1 Or MultivalueFieldName.Value=2 Or MultivalueFieldName.Value=3 Group By "1;3"


add them in the final union query:

SELECT Data, CountOfData FROM qry1
UNION ALL
SELECT Data, CountOfData FROM qry2
UNION ALL
SELECT Data, CountOfData FROM qry3
UNION ALL
SELECT Data, CountOfData FROM qry1_2
UNION ALL
SELECT Data, CountOfData FROM qry1_2
UNION ALL
SELECT Data, CountOfData FROM qry2_3
UNION ALL
SELECT Data, CountOfData FROM qry123

 

[dafne]

Thank you, Pat, for taking the time to give me great advice. I won't be working with multivalued fields next time, and if none of the workarounds suggested in this thread work for me, I'll do what you said.

 

[dafne]

you can create a Union query
to accomplish this. first create
small queries like this:

qry1:
select "1" As Data, Count(MultiValueFieldName.Value) As CountOfData From yourTable Where MultiValueFieldName.Value=1 Group By "1"

qry2:
select "2" As Data, Count(MultiValueFieldName.Value) As CountOfData From yourTable Where MultiValueFieldName.Value=2 Group By "2"

qry3
select "3" As Data, Count(MultiValueFieldName.Value) As CountOfData From yourTable Where MultiValueFieldName.Value=3 Group By "3"

qry1_2:
select "1;2" As Data, Count(MultiValueFieldName.Value) As CountOfData From yourTable Where MultiValueFieldName.Value=1 Or MultivalueFieldName.Value=2 Group By "1;2"

qry1_3:
select "1;3" As Data, Count(MultiValueFieldName.Value) As CountOfData From yourTable Where MultiValueFieldName.Value=1 Or MultivalueFieldName.Value=3 Group By "1;3"

qry2_3:
select "2;3" As Data, Count(MultiValueFieldName.Value) As CountOfData From yourTable Where MultiValueFieldName.Value=2 Or MultivalueFieldName.Value=3 Group By "2;3"

qry123:
select "1;2;3" As Data, Count(MultiValueFieldName.Value) As CountOfData From yourTable Where MultiValueFieldName.Value=1 Or MultivalueFieldName.Value=2 Or MultivalueFieldName.Value=3 Group By "1;3"


add them in the final union query:

SELECT Data, CountOfData FROM qry1
UNION ALL
SELECT Data, CountOfData FROM qry2
UNION ALL
SELECT Data, CountOfData FROM qry3
UNION ALL
SELECT Data, CountOfData FROM qry1_2
UNION ALL
SELECT Data, CountOfData FROM qry1_2
UNION ALL
SELECT Data, CountOfData FROM qry2_3
UNION ALL
SELECT Data, CountOfData FROM qry123

Hi, arnelgp! I'll try this today. Thank you for answering!

 

[dafne]

Hello again, arnelgp. I'm running into a small problem, I hope you can help me. Here it goes:

I want to see how many times element number 6 appears in my database (alone or couple with something else). I created a small query, as you suggested, and it kind of works - except for this:
I know element number 6 appears 4 times in my DB. It's always coupled with another element (e.g. "1;6"), so I think that's why I'm getting "8" instead of "4" in my results.

What I wanted to do was:

• to see how many times an item appears overall;
• to see how many times a specific construction appears (e.g."1;2" as a whole);
• later, to see the distribution of specific constructions according to different types of sentences (e.g. just "1", just "1;2", etc)

I know I ask a lot of questions, I just don't know if what I want to do is possible. :banghead:
Thank you for your help,
Dafne

 

[arnelgp]

Ms.Dafne,

view the queries you have in design view.
one by one ofcourse.
now, on the Criteria portion, instead
of OR, use AND.
OR means either of the two or three.
AND means all must be present for it to
count.

and please, stop banging the head on the
wall.

 

[arnelgp]

ok, i think i was wrong about my
solution. in fact, there is much
easier one.
copy and paste the code in Standard Module (VBA).

Option Compare Database
Option Explicit

' USAGE:
' SELECT CompanyName,  ConcatRelated("OrderDate", "tblOrders", "CompanyID = " & [CompanyID])
' FROM tblCompany;
Public Function ConcatRelated(strField As String, _
    strTable As String, _
    Optional strWhere As String = "", _
    Optional strOrderBy As String = "", _
    Optional strSeparator = ", ") As Variant
On Error GoTo err_handler
    'Purpose:   Generate a concatenated string of related records.
    'Return:    String variant, or Null if no matches.
    'Arguments: strField = name of field to get results from and concatenate.
    '           strTable = name of a table or query.
    '           strWhere = WHERE clause to choose the right values.
    '           strOrderBy = ORDER BY clause, for sorting the values.
    '           strSeparator = characters to use between the concatenated values.
    'Notes:     1. Use square brackets around field/table names with spaces or odd characters.
    '           2. strField can be a Multi-valued field (A2007 and later), but strOrderBy cannot.
    '           3. Nulls are omitted, zero-length strings (ZLSs) are returned as ZLSs.
    '           4. Returning more than 255 characters to a recordset triggers this Access bug:
    '               http://allenbrowne.com/bug-16.html
    Dim rs As DAO.Recordset2         'Related records
    Dim rsMV As DAO.Recordset2       'Multi-valued field recordset
    Dim strSQL As String            'SQL statement
    Dim strOut As String            'Output string to concatenate to.
    Dim lngLen As Long              'Length of string.
    Dim bIsMultiValue As Boolean    'Flag if strField is a multi-valued field.
    
    Dim var As Variant
    'Initialize to Null
    ConcatRelated = Null
    'Build SQL string, and get the records.
    strSQL = "SELECT " & strField & " FROM " & strTable
    If strWhere <> vbNullString Then
        strSQL = strSQL & " WHERE " & strWhere
    End If
    If strOrderBy <> vbNullString Then
        strSQL = strSQL & " ORDER BY " & strOrderBy
    End If
    Set rs = DBEngine(0)(0).OpenRecordset(strSQL, dbOpenDynaset)
    'Determine if the requested field is multi-valued (Type is above 100.)
    bIsMultiValue = (rs(0).Type > 100)
    
    'Loop through the matching records
    Do While Not rs.EOF
        If bIsMultiValue Then
            'For multi-valued field, loop through the values
            Set rsMV = rs(0).Value
            Do While Not rsMV.EOF
                If Not IsNull(rsMV(0)) Then
                    strOut = strOut & rsMV(0) & strSeparator
                End If
                rsMV.MoveNext
            Loop
            Set rsMV = Nothing
        ElseIf Not IsNull(rs(0)) Then
            strOut = strOut & rs(0) & strSeparator
        End If
        rs.MoveNext
    Loop
    rs.Close
    
    'Return the string without the trailing separator.
    lngLen = Len(strOut) - Len(strSeparator)
    If lngLen > 0 Then
        ConcatRelated = Left(strOut, lngLen)
    End If

Exit_Handler:
    'Clean up
    Set rsMV = Nothing
    Set rs = Nothing
    Exit Function

err_handler:
    
    
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "ConcatRelated()"
    Resume Exit_Handler
End Function

you only need to create 1 query.
you also NEED to add AutoNumber Field to
your Table (the one with MultiValue Field).
in my query I used ID.

remember to replaced with the correct
tablename, multivalue field name, and
autonumber field name on the
following Query.
you may copy the query in SQL view then
edit the tablenames, field names, etc.

SELECT ConcatRelated("MultiValueFieldName.Value","yourTable","ID=" & [ID]) AS Data, Count(ConcatRelated("MultiValueFieldName.Value","yourTable","ID=" & [ID])) AS CountOfData
FROM yourTable
GROUP BY ConcatRelated("MultiValueFieldName.Value","yourTable","ID=" & [ID]);

 

[dafne]

Hello, arnelgp! Thank you, again, for taking your time to help me.

This is what I did:
• I copied and pasted the code you suggested in Standard Module (VBA). I didn’t make any changes to it – should I?
• I created the query but I’m getting the error that says that “the function ‘ConcatRelated’ is not defined in the expression” (this is a literal translation from Portuguese)

Perhaps you can help me find out what I'm doing wrong.
I have a table called tbl_VERB_structure, with two columns: the first, containing the primary key, is VERB_structure_ID. The second, VERB_structure_label, contains the description (e.g. VERB_structure_ID = "1" corresponds to VERB_structure_label = "subject").

This table "feeds" the multivalued control VERB_structure in my form with the available options to choose from. In turn, my choices populate the field VERB_structure in a table containing all my data, called tbl_RECORDS (each record has been assigned a primary key, named record_ID). That is why I'm not quite sure how to replace the items in the query code below:

you only need to create 1 query.
you also NEED to add AutoNumber Field to
your Table (the one with MultiValue Field).
in my query I used ID.

remember to replaced with the correct
tablename, multivalue field name, and
autonumber field name on the
following Query.
you may copy the query in SQL view then
edit the tablenames, field names, etc.

SELECT ConcatRelated("MultiValueFieldName.Value","yourTable","ID=" & [ID]) AS Data, Count(ConcatRelated("MultiValueFieldName.Value","yourTable","ID=" & [ID])) AS CountOfData
FROM yourTable
GROUP BY ConcatRelated("MultiValueFieldName.Value","yourTable","ID=" & [ID]);

 

[arnelgp]

is it possible for you to Upload
a sample db. only include
tbl_VER_structure and 4 records from
tbl_RECORDS. i want to take a look.

 

[dafne]

Hi,
Here's the sample DB you've asked for. Thank you very much for your time!

 

[arnelgp]

Here again. Take a look at query1

 

[arnelgp]

See query3

 

[dafne]

arnelgp, you're an absolute hero. Thank you, thank you for this! Can't tell you how much you've helped me today. it's perfect!

 

[arnelgp]

Anytime and do test it on bigger dataset.