Combining multiple rows/records into one

[Jeremy Goodman]

Access 2007; Merging records containing multivalue drop down lists.

I have a database showing legislation information divided by State/territory. The database needs to be able to show the info from each legislation and then an overall picture for the state. There is no problem with merging some of the info using the steps in:

http://bytes.com/topic/access/answers/589999-combining-multiple-rows-one-field-into-one-result

The problem is that most of the more valuable items are in multivalue lookup fields (like how programs are funded). When I ran the test query it gave me the following error message:
The multi-valued field 'FundingSource' is not valid in the expression 'ConcatFS([LEGISLATION].[State],FundingSource) '.
Am I right in now thinking that merging multivalue is impossible? Any suggestions on how to modify the table or code so it can be done short of having data entry be a complete hassle?

[Jeremy Goodman]

I've run through various methods of fixing this and the most likely fix I saw was the use of Y/N check boxes for each item. This seemed to work in regards to it allowing me to concatenate the records for that item even though it means having way more columns in the tables. Unfortunately, when I was testing it for queries and sorts, I hit a snag. When sorting by "Yes", each new record that is a compilation of 2+ records is filtered as being yes, regardless of what the actual check boxes are (Y,Y; Y,N; N,N all sort as "Yes"). Code for function and query below.

'ConcatTest returns composite list of State Legislations
Public Function ConcatTest(strState As String, _
                       strTest As String) As String
    Static strLastState As String
    Static strTests As String
  
    If strState = strLastState Then
        strTests = strTests & ", " & strTest
    Else
        strLastState = strState
        strTests = strTest
    End If
    ConcatTest = strTests
End Function

Query, sort for States with Test = Yes

SELECT LEGISLATION.State, Max(ConcatTest([State],[Test])) AS Tests INTO Query
FROM LEGISLATION
GROUP BY LEGISLATION.State
HAVING (((Max(ConcatTest([State],[Test])))=Yes));

[Jeremy Goodman]

Additionally, sorting by "No" only yields the record with a single no, not the concatenated records for the state that has two instances of no (0,0). Basically is there any way to modify my code for the function or query so that sorting by "Yes" gives any concatenated record where at least one check box is yes? Thank you

[ADezii]

Just subscribing for now - do believe I have a relatively simple solution, but can't work on it right now.

[ADezii]

@Jeremy - I didn't have the opportunity to test the following Code with a Multi-Value Field in Access 2007, but it works fine in 2003. I used the Data from your Link only because of its simplicity. I'll post the SQL and the Function Definition below, as well as an Attachment. Let me know what you think. If it doesn't work in 2007, I'm sure that we can make some minor Code adjustments to arrive at a solution.

SELECT tblProducts.CompanyName, tblProducts.Category, fConcatProduct([CompanyName],[Category]) AS [Products/Category]
FROM tblProducts
WHERE (((tblProducts.Grower)=True))
GROUP BY tblProducts.CompanyName, tblProducts.Category
ORDER BY tblProducts.CompanyName, tblProducts.Category;

Public Function fConcatProduct(strCompany As String, strCategory As String) As String
Dim strSQL As String
Dim strBuild As String
Dim MyDB As DAO.Database
Dim rstProd As DAO.Recordset

strSQL = "SELECT * FROM tblProducts WHERE [CompanyName] = '" & strCompany & "' AND " & _
         "[Category] = '" & strCategory & "' AND [Grower] = True;"

Set MyDB = CurrentDb
Set rstProd = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly)

With rstProd
  Do While Not .EOF
    strBuild = strBuild & ![Product] & ","
      .MoveNext
  Loop
End With

strBuild = Left$(strBuild, Len(strBuild) - 1)

rstProd.Close
Set rstProd = Nothing

fConcatProduct = strBuild
End Function

Sample OUTPUT:

CompanyName	Category	   Products/Category
Acme Farms	 Vegetables	 Carrots,Vege1,Vege2,Vege3
ADezii Farms   Fruits	     Pears,Tangarines,Oranges
B Farms	    Fruits	     Blackberries,Melons,Watermelons
B Farms	    Vegetables	 Beans,Broccoli,Cabbage
B Orchar       Fruits	     Apples,Blackberries
B Orchard      Vegetables	 Beans,Other,Peas
B Star Acres   Vegetables	 Asparagus,Beans,Beets

ATTACHMENT:

[Jeremy Goodman]

Since Access uses -1 for yes and 0 for no, would it be possible to add the numerical represenations together when concatenating or instead of merging them? Then the sort could work by using <>0

[Jeremy Goodman]

Thank you for the effort, but that really doesn't answer either of my questions. For clarity's sake, the database table consists of the following:

1) State/Territory, Legislation general info (title, date, link to text), specific Legislation information composed of 28 different multiple value comboboxes for various portions of the legislation

2) There are a total of 240 choices from the boxes, usually 5-15 per. Converting to Y/N format means that all 240 would need their own field in the table.

3) Each state has one to five legislations for the subject with each containing information covering multiple comboboxes

4) Primary key is combination of State, Legislation Title, and Legislation Date

The merger would be for a table where all 1-5 legislations would be merged to give all the information for a state as one single record