UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Concatenate A Reference Table Rows Into A String, Any Version    
 
   
maclarkson
post Oct 5 2019, 04:16 PM
Post#1



Posts: 6
Joined: 22-September 19



Hi there,

Part 1

I want a query that can concatenate the rows in a reference table into string.

Fruit
Apples
Bananas
Oranges
Mandarins

Into
"Apples;Bananas;Oranges;Mandarins"

I found some forums where they concatenate given a where condition, but I just want a simple concatenation of all row in a table.

Part 2

I have another table where I want this data to be inserted into all rows like so
Person Likes Concat
Joe Apples "Apples;Bananas;Oranges;Mandarins"
Jane Oranges "Apples;Bananas;Oranges;Mandarins"
Kate Apples "Apples;Bananas;Oranges;Mandarins"

For those interested in why I would want to do this. I am using visio pro and connecting up a database. in visio the concatenated field populates the drop-down list of each object.
Go to the top of the page
 
June7
post Oct 5 2019, 04:29 PM
Post#2



Posts: 957
Joined: 25-January 16



Review http://allenbrowne.com/func-concat.html

As you can see, WHERE criteria is optional.

How many records do you want to concatenate?

This post has been edited by June7: Oct 5 2019, 04:32 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
Larry Larsen
post Oct 5 2019, 05:53 PM
Post#3


UA Editor + Utterly Certified
Posts: 24,375
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


welcome2UA.gif
thumbup.gif

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
ADezii
post Oct 5 2019, 06:09 PM
Post#4



Posts: 2,688
Joined: 4-February 07
From: USA, Florida, Delray Beach


QUOTE
but I just want a simple concatenation of all row in a table.

  1. This is about as simple as you can get.
  2. Reference Table (tblReferences):
    Fruit
    Apples
    Bananas
    Oranges
    Mandarins
    Grapes
    Peaches
    Tomatoes
    Watermelon
  3. Code Definition:
    CODE
    Dim MyDb As DAO.Database
    Dim rst As DAO.Recordset
    Dim strBuild As String

    Set rst = CurrentDb.OpenRecordset("tblReferences", dbOpenSnapshot, dbOpenForwardOnly)

    With rst
      Do While Not .EOF
        strBuild = strBuild & ![Fruit] & ";"
          .MoveNext
      Loop
    End With

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

    Debug.Print strBuild

    rst.Close
    Set rst = Nothing
    Set MyDb = Nothing
  4. OUTPUT:
    CODE
    Apples;Bananas;Oranges;Mandarins;Grapes;Peaches;Tomatoes;Watermelon
Go to the top of the page
 
theDBguy
post Oct 5 2019, 06:35 PM
Post#5


Access Wiki and Forums Moderator
Posts: 76,484
Joined: 19-June 07
From: SunnySandyEggo


Hi. Welcome to UA! welcome2UA.gif

Just another option for you. Cheers!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
maclarkson
post Oct 6 2019, 06:19 AM
Post#6



Posts: 6
Joined: 22-September 19



Whatever is in the list as the list may grow. So I need it to update the other field as an automated action when the list is updated. I was going to run an update query using a macro that would be triggered upon update of the table
Go to the top of the page
 
maclarkson
post Oct 6 2019, 06:22 AM
Post#7



Posts: 6
Joined: 22-September 19



Do you know how I would get this into the field as explained in part 2
Go to the top of the page
 
arnelgp
post Oct 6 2019, 06:31 AM
Post#8



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


the update query (using Allen Brownes' ConcatRelated() function):

Update table2, set Concat = ConcatRelated("Fruit", "tblFruit");

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
GroverParkGeorge
post Oct 6 2019, 08:21 AM
Post#9


UA Admin
Posts: 36,017
Joined: 20-June 02
From: Newcastle, WA


Hmm. I think we don't want to store a calculated field and then have to continually update it, do we?

Don't we want to use the concatenation function in a query instead? And use that query whereever you need that particular concatenation for a report? In other words, while you CAN store calculated values and set up some routine that tries to keep it current, good database design principles would suggest you not do that. And that is particularly true, again, because you obviously need to constantly update it.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
projecttoday
post Oct 6 2019, 09:55 AM
Post#10


UtterAccess VIP
Posts: 11,176
Joined: 10-February 04
From: South Charleston, WV


Does it have to be in a table? Would a report work?
What are likes and why are Joe and Kate's apples and Jane's Oranges? Does this have to be in the output?
What have you tried so far? Have you tried any of these functions? What is the code you used? What happened?
Last-record logic would work well here.

--------------------
Robert Crouser
Go to the top of the page
 
June7
post Oct 6 2019, 02:12 PM
Post#11



Posts: 957
Joined: 25-January 16



How does Visio Pro get data? It links to Access? Can it pull from a query that references a function? If so, use 'other' table that has names in query:

SELECT [people names], ConcatRelated("fruit", "Fruits", "" , "", ";") FROM Fruits AS AllFruit FROM OtherTable;

If Visio cannot handle query referencing custom function, which is a distinct possibility, use UPDATE action.

UPDATE OtherTable SET AllFruit = ConcatRelated("Fruit", "Fruits", "" , "", ";");

This post has been edited by June7: Oct 6 2019, 02:12 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
maclarkson
post Oct 7 2019, 07:59 AM
Post#12



Posts: 6
Joined: 22-September 19



Yes it needs to get into the table. I know it’s bad design but I’m all out of options. Visio can use a report.
Go to the top of the page
 
maclarkson
post Oct 7 2019, 08:11 AM
Post#13



Posts: 6
Joined: 22-September 19



Visio pro version has the ability to source data from an access database. When you understand Visio you’ll discover that against each object is a shapesheet. A sort of shape properties sheet. Turn on dev in Visio and you’ll see in the dev tab. Anyway if you know anything about shapes in Visio you might know that you can record data against each object. It has all the different types such as bollean, string and it also has list. This list when you set it up is in the form of “item1;item2;....etc. When you go to the shape sheet it stores these preperties to provide the drop down list. The only two way to supply this data is to. For each object, manually edit each list on each object. Or I can supply that concatenated string from the database using special functions that the shape sheet provides.

Any way so long as I can get that string from the database I should be ok. So which is the solution I should use.
Go to the top of the page
 
GroverParkGeorge
post Oct 7 2019, 09:58 AM
Post#14


UA Admin
Posts: 36,017
Joined: 20-June 02
From: Newcastle, WA


I strongly suspect storing the calculations in a table may not be optimal, but if that's how you need to do it, just use any one of the available VBA functions offered in several previous posts.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
maclarkson
post Oct 8 2019, 07:36 AM
Post#15



Posts: 6
Joined: 22-September 19



Attached File  Capture.PNG ( 8.63K )Number of downloads: 0


Yay, I got it to work. Thanks, everyone. Is it possible to make this a calculated field within the table or am I stuck with running it as an update query?
Go to the top of the page
 
theDBguy
post Oct 8 2019, 10:09 AM
Post#16


Access Wiki and Forums Moderator
Posts: 76,484
Joined: 19-June 07
From: SunnySandyEggo


Hi. Congratulations! Sorry, no. You can't do this as a Calculated Field because calculated fields cannot reference other records, let alone another table. If you can't use a query and must store the result in the table, then I'm afraid you may be stuck with using an UPDATE query. Good luck!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
projecttoday
post Oct 8 2019, 11:08 AM
Post#17


UtterAccess VIP
Posts: 11,176
Joined: 10-February 04
From: South Charleston, WV


In my earlier response I should have said last-record logic in recordset code.

Which you might not need if you got it working with a query. Does the Dbguy mean INSERT? Insert the records into a table.

--------------------
Robert Crouser
Go to the top of the page
 
theDBguy
post Oct 8 2019, 12:07 PM
Post#18


Access Wiki and Forums Moderator
Posts: 76,484
Joined: 19-June 07
From: SunnySandyEggo


QUOTE (projecttoday)
Does the Dbguy mean INSERT? Insert the records into a table.
No, I think I meant UPDATE because I am referring to updating the calculated column's value. For instance, let's say all records now has the following "Apples;Bananas;Oranges" and the table with the list of fruit got updated to include Peach and Pear, then all the records will have to be "updated" to replace "Apples;Bananas;Oranges" with "Apples;Bananas;Oranges;Pear;Peach" Right?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
projecttoday
post Oct 8 2019, 12:39 PM
Post#19


UtterAccess VIP
Posts: 11,176
Joined: 10-February 04
From: South Charleston, WV


QUOTE
now has the following "Apples;Bananas;Oranges"

Not according to post #1, which says there is only 1 fruit per record in the table. Are we talking about the same thing?

--------------------
Robert Crouser
Go to the top of the page
 
theDBguy
post Oct 8 2019, 12:49 PM
Post#20


Access Wiki and Forums Moderator
Posts: 76,484
Joined: 19-June 07
From: SunnySandyEggo


QUOTE (projecttoday)
Not according to post #1, which says there is only 1 fruit per record in the table. Are we talking about the same thing?
Did you see Part 2 of Post #1? The additional requirement was to attach/store the concatenated fruit values into every record in another table.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    12th November 2019 - 04:27 AM