473,574 Members | 2,634 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Combining multiple rows/records into one

8 New Member
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/answer...nto-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?
Feb 10 '11 #1
6 10893
Jeremy Goodman
8 New Member
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
  6.  
  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
  2. FROM LEGISLATION
  3. GROUP BY LEGISLATION.State
  4. HAVING (((Max(ConcatTest([State],[Test])))=Yes));
  5.  
Feb 14 '11 #2
Jeremy Goodman
8 New Member
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
ADezii
8,834 Recognized Expert Expert
Just subscribing for now - do believe I have a relatively simple solution, but can't work on it right now.
Feb 14 '11 #4
ADezii
8,834 Recognized Expert Expert
@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
  6.  
  7. strSQL = "SELECT * FROM tblProducts WHERE [CompanyName] = '" & strCompany & "' AND " & _
  8.          "[Category] = '" & strCategory & "' AND [Grower] = True;"
  9.  
  10. Set MyDB = CurrentDb
  11. Set rstProd = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly)
  12.  
  13. With rstProd
  14.   Do While Not .EOF
  15.     strBuild = strBuild & ![Product] & ","
  16.       .MoveNext
  17.   Loop
  18. End With
  19.  
  20. strBuild = Left$(strBuild, Len(strBuild) - 1)
  21.  
  22. rstProd.Close
  23. Set rstProd = Nothing
  24.  
  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
ATTACHMENT:
Attached Files
File Type: zip Combining Multiple Rows.zip (19.7 KB, 456 views)
Feb 14 '11 #5
Jeremy Goodman
8 New Member
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
Jeremy Goodman
8 New Member
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

7
33290
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 database (a,b,c,d,e) VALUES ('$a', '$b' ,'$c', '$d', '$e')"; I want to insert 5 rows at a time in the database, but it only inserts every 5th record. For example: 1. AA
4
4971
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 take place to create employee "load" balance. Each day needs to be inserted as a separate row on the table. Right now the users enter each date as a separate row. They want to just be able to enter a...
2
13548
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 averaged values over a years time. What I have is weather data. There is a new record every 5 minutes, every day. So. What I want to do with one SQL statement is figure out the Average of...
5
1515
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 follows Policy Number DateOfTrans TransType BenefitAmt 1234 12/1/2006 Received $12,000 1234 12/3/2006 Approved $5,000 1234 ...
14
2604
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 daily. I have written simple scripts but nothing to write home about. I followed the previous thread and my situation is very similar. I have an application with users. These users have access to...
16
2524
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 the next page, i now want them to click order and each flower is inserted into the table. username - flowerid - qty - total user1 ------------ 2 ------- 1----- 1.99 user1 ------------ 3 -------...
1
6276
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 fields I am trying to return the single record for is Res_code. There can be multiple records with the same Res_code. Some of the matching Res_code records can have different values in any of the 28...
2
5326
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 the selected rows in a single stroke? just like what hotmail web UI is doing now (having the option of selecting multiple rows (using the checkbox provided) and perform a set of operations on them)
3
3911
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, but it's not working properly, the output count is messing up. Problems:
6
4129
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 to do it would be helpful as well). I want to have my first datagridview to have a multi-select (which i have currently), but what i want to happen when multiple rows are selected is different. I...
0
7815
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
8257
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7828
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8113
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
5316
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3765
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2253
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1358
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1076
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

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.