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?
6 10868
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));
-
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
Just subscribing for now - do believe I have a relatively simple solution, but can't work on it right now.
@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:
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
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
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 database (a,b,c,d,e) VALUES ('$a', '$b' ,'$c', '$d',...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
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,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
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...
|
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...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |