472,344 Members | 2,307 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,344 software developers and data experts.

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 10715
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,832 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,832 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, 454 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

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

Similar topics

by: RotterdamStudents | last post by:
Hello there, i have a strange problem. I can't get php to insert multiple rows at once in a MySQL database. I use the $sql = "INSERT INTO...
by: Joanie | last post by:
I have a form that records dates of unavailability for a worker. Based on what is entered in the simple table behind the form, many calculations...
by: chrisale | last post by:
Hi All, I've been racking my brain trying to figure out some sort of Sub-Select mySQL statement that will create a result with multiple rows of...
by: BerkshireGuy | last post by:
I was searching the threads about combing multiple rows into one and found some good stuff, but need your help to expand on it. I have a table as...
by: jackiefm | last post by:
I realize the thread I am responding to was posted in January but I am basically having the same issue. I am not familiar with VBA but use Access...
by: jasone | last post by:
Hi all, The system im working on currently allows the user to select a number of flowers.. click submit and whatever they clicked is passed onto...
by: tjm0713 | last post by:
Not sure this can be done but here is my current situation. I have a table containing millions of records. Each record has 28 fields. One of the...
by: Michael | last post by:
It seems that a gridview allows us to delete only a single row at a time. How to extend this functionality to select multiple rows and delete all of...
by: nigelesquire | last post by:
Please help! I'm trying to clone and delete multiple rows with JavaScript. I need two delete buttons that work...! I only have one for now,...
by: Gun Slinger | last post by:
Hi guys, I have a quick question which i have pondered for a little while. I'm not sure if its even a good idea (so maybe advice on whether or not...
by: teenabhardwaj | last post by:
How would one discover a valid source for learning news, comfort, and help for engineering designs? Covering through piles of books takes a lot of...
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and...
by: Matthew3360 | last post by:
Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function. Here is my code. ...
by: Matthew3360 | last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it...
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable...
by: Arjunsri | last post by:
I have a Redshift database that I need to use as an import data source. I have configured the DSN connection using the server, port, database, and...
by: WisdomUfot | last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific...
by: Matthew3360 | last post by:
Hi, I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web...
by: Oralloy | last post by:
Hello Folks, I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA. My problem (spelled failure) is with the...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.