469,323 Members | 1,567 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,323 developers. It's quick & easy.

Combining multiple rows/records into one

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:

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?
Feb 10 '11 #1
6 10417
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.

Expand|Select|Wrap|Line Numbers
  1. 'ConcatTest returns composite list of State Legislations
  2. Public Function ConcatTest(strState As String, _
  3.                        strTest As String) As String
  4.     Static strLastState As String
  5.     Static strTests As String
  7.     If strState = strLastState Then
  8.         strTests = strTests & ", " & strTest
  9.     Else
  10.         strLastState = strState
  11.         strTests = strTest
  12.     End If
  13.     ConcatTest = strTests
  14. End Function
Query, sort for States with Test = Yes
Expand|Select|Wrap|Line Numbers
  1. SELECT LEGISLATION.State, Max(ConcatTest([State],[Test])) AS Tests INTO Query
  4. HAVING (((Max(ConcatTest([State],[Test])))=Yes));
Feb 14 '11 #2
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
Feb 14 '11 #3
8,800 Expert 8TB
Just subscribing for now - do believe I have a relatively simple solution, but can't work on it right now.
Feb 14 '11 #4
8,800 Expert 8TB
@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.
Expand|Select|Wrap|Line Numbers
  1. SELECT tblProducts.CompanyName, tblProducts.Category, fConcatProduct([CompanyName],[Category]) AS [Products/Category]
  2. FROM tblProducts
  3. WHERE (((tblProducts.Grower)=True))
  4. GROUP BY tblProducts.CompanyName, tblProducts.Category
  5. ORDER BY tblProducts.CompanyName, tblProducts.Category;
Expand|Select|Wrap|Line Numbers
  1. Public Function fConcatProduct(strCompany As String, strCategory As String) As String
  2. Dim strSQL As String
  3. Dim strBuild As String
  4. Dim MyDB As DAO.Database
  5. Dim rstProd As DAO.Recordset
  7. strSQL = "SELECT * FROM tblProducts WHERE [CompanyName] = '" & strCompany & "' AND " & _
  8.          "[Category] = '" & strCategory & "' AND [Grower] = True;"
  10. Set MyDB = CurrentDb
  11. Set rstProd = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly)
  13. With rstProd
  14.   Do While Not .EOF
  15.     strBuild = strBuild & ![Product] & ","
  16.       .MoveNext
  17.   Loop
  18. End With
  20. strBuild = Left$(strBuild, Len(strBuild) - 1)
  22. rstProd.Close
  23. Set rstProd = Nothing
  25. fConcatProduct = strBuild
  26. End Function
Sample OUTPUT:
Expand|Select|Wrap|Line Numbers
  1. CompanyName    Category       Products/Category
  2. Acme Farms     Vegetables     Carrots,Vege1,Vege2,Vege3
  3. ADezii Farms   Fruits         Pears,Tangarines,Oranges
  4. B Farms        Fruits         Blackberries,Melons,Watermelons
  5. B Farms        Vegetables     Beans,Broccoli,Cabbage
  6. B Orchar       Fruits         Apples,Blackberries
  7. B Orchard      Vegetables     Beans,Other,Peas
  8. B Star Acres   Vegetables     Asparagus,Beans,Beets
Attached Files
File Type: zip Combining Multiple Rows.zip (19.7 KB, 442 views)
Feb 14 '11 #5
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
Feb 14 '11 #6
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
Feb 14 '11 #7

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

7 posts views Thread by RotterdamStudents | last post: by
5 posts views Thread by BerkshireGuy | last post: by
2 posts views Thread by Michael | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by mdpf | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.