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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Concatenating Rows Through A Query, Access 2016    
 
   
lex
post Oct 10 2017, 02:11 PM
Post#1



Posts: 665
Joined: 20-October 05



There are MANY ways out there discussing a way to do this.

An example is shown here

Visit My Website

but can't seem to get it to work. The discussion above references the "GetList" code shown here

Visit My Website

So, basically, the GetList code runs, returns a string which is used by a query to display.

Has anyone done this before? Could you help me debug it?

Thanks

Lex
Go to the top of the page
 
Doug Steele
post Oct 10 2017, 02:56 PM
Post#2


UtterAccess VIP
Posts: 21,357
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Pretty difficult to help debug when you don't mention what problem(s) you're having. smile.gif

--------------------
Go to the top of the page
 
doctor9
post Oct 10 2017, 03:01 PM
Post#3


UtterAccess Editor
Posts: 17,736
Joined: 29-March 05
From: Wisconsin


lex,

Is there a reason you want to do this with a query rather than a simple VBA function?

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
lex
post Oct 10 2017, 03:19 PM
Post#4



Posts: 665
Joined: 20-October 05



Sorry, Doug ... I should have been more clear. I was thinking that someone might be aware of a solution that was more "elegant" (for a lack of better term) than that shown on the site.

So, rather than starting with explaining the tables (since I think the problem I have is the code), I'll start with the query/code.

I have the below query (below) that calls GetList (code referenced above)

QUOTE
SELECT qryInnovationID_UserID.InnovationID, GetList("Select Name From qryInnovationID_UserID As T1 Where T1.InnovationID = """ & [qryInnovationID_UserID].[InnovationID] & """","",", ") AS Expr1
FROM qryInnovationID_UserID
GROUP BY qryInnovationID_UserID.InnovationID;


When I put breakpoint in the code, see picture below, the code jumps from the line "Set oRS=..." to "Set oConn = ..." and back again. It never moves to "sResult=". There is no loop here. I don't know why.



Attached File  2017_10_10_16_15_24.png ( 149.15K )Number of downloads: 4



If I let the code run, Expr1 is blank. I would try to debug - but the behavior makes no sense.

doctor9, I know this might be "simple" to you... but I'm struggling smile.gif . if you have a simple function, please share. What I have doesn't seem that simple.

THANKS TO BOTH OF YOU FOR TAKING THE TIME!!

If I need to give you sample data, or db ... please let me know.


Lex



Go to the top of the page
 
doctor9
post Oct 10 2017, 03:44 PM
Post#5


UtterAccess Editor
Posts: 17,736
Joined: 29-March 05
From: Wisconsin


Lex,

No offense, I just wanted to make sure that VBA was on the table.

This function is by our own theDBGuy

If you can't get that to work, you can explain your table structure and we can help you figure out the issue.

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
lex
post Oct 10 2017, 09:27 PM
Post#6



Posts: 665
Joined: 20-October 05



No offense taken. Text doesn't do communication justice. I appreciate your looking at this.


I tried it and couldn't get it to work. sorry.

I'd like to get the children table UserInfo.First_name concatenated such as
"Lex, John, Steve" while showing the id for the parent as follows:

ID Names
1 "Lex, John, Steve"
2 "Joe, Sharon, Bob"

One table is InnovationCentral which has a lookup as follows

Attached File  2017_10_10_22_23_19.png ( 70.29K )Number of downloads: 6


The other table is UserInfo, which has the names in it and looks like this

Attached File  2017_10_10_22_24_27.png ( 46.23K )Number of downloads: 3


Note that these tables were not created by me, and I don't have any control of them (ie ability to change the structure).

Do you have any thoughts?

Thank you

I think my problem is escaping the quotes, but I'm not sure. I got the below error

Attached File  2017_10_10_22_21_43.png ( 30.69K )Number of downloads: 5


Go to the top of the page
 
doctor9
post Oct 11 2017, 08:27 AM
Post#7


UtterAccess Editor
Posts: 17,736
Joined: 29-March 05
From: Wisconsin


lex,

It's really hard to work with screenshots. Is it possible to post a stripped-down copy of your database? I'd rather not re-create tables and queries from scratch. Besides, you are using a query called "qryInnovationID_UserID" in your expression, but I don't know the SQL for that query, so I can't reproduce the problem.

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
Doug Steele
post Oct 11 2017, 08:29 AM
Post#8


UtterAccess VIP
Posts: 21,357
Joined: 8-January 07
From: St. Catharines, ON (Canada)


I would agree that your quotes are wrong. Also, you need to use . not ! in SQL. See whether this works any better:

CODE
SELECT U.InnovationID, SimpleCSV("SELECT [UserInfo].[First name] & ' ' & [UserInfo].[Last name] AS Innovators FROM UserInfo INNER JOIN InnovationCentral ON UserInfo.ID = InnovationCentral.[Who's idea is This?] WHERE InnovationCentral.ID = " & U.InnovationID)
FROM qryInnovationID_UserID AS U
GROUP BY U.InnovationID

(BTW, see if you can talk the powers that be to rename that [Who's idea is This?] field!)

--------------------
Go to the top of the page
 
Minty
post Oct 11 2017, 08:32 AM
Post#9



Posts: 64
Joined: 5-July 16



Isn't Allen Browne's concatenate function the "defacto" way to do this ?
http://allenbrowne.com/func-concat.html
This post has been edited by Minty: Oct 11 2017, 08:32 AM
Go to the top of the page
 
Doug Steele
post Oct 11 2017, 08:42 AM
Post#10


UtterAccess VIP
Posts: 21,357
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Minty: Allen's code works against a table. Lex is trying to join two tables, so I don't think Allen's code would work.

--------------------
Go to the top of the page
 
Minty
post Oct 11 2017, 08:50 AM
Post#11



Posts: 64
Joined: 5-July 16



It will work against a table or a query - so if the tables can be joined in a query I think it will work.
Go to the top of the page
 
doctor9
post Oct 11 2017, 08:59 AM
Post#12


UtterAccess Editor
Posts: 17,736
Joined: 29-March 05
From: Wisconsin


I think theDBGuy's function is a nice, simple solution. I'm pretty sure Doug's suggested fix to the SQL is all we need to move forward.

Here's an example of an SQL expression that uses the function successfully.

SimpleCSV("SELECT [strFirstName] & "" "" & [strLastName] AS FullName, tblPeople.lngProjectID FROM tblPeople WHERE lngProjectID=" & [ProjectID] & ";") AS NameList

Notice how the only place where double quotes are the ones between the first and last name?

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
Doug Steele
post Oct 11 2017, 09:33 AM
Post#13


UtterAccess VIP
Posts: 21,357
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Dennis: Or you can use single quotes inside the SQL statement to avoid needing to double them up, like I did. smile.gif

Minty: While it's true that Allen's code will work with either a table or a query, the query must be saved, since the function builds the SQL. Since theDBGuy's function doesn't rely on a saved query, it's a bit more flexible.

--------------------
Go to the top of the page
 
doctor9
post Oct 11 2017, 09:38 AM
Post#14


UtterAccess Editor
Posts: 17,736
Joined: 29-March 05
From: Wisconsin


Doug,

> Or you can use single quotes inside the SQL statement to avoid needing to double them up, like I did.

I avoid using single quotes with names because of names like O'Reilly.

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
Doug Steele
post Oct 11 2017, 10:04 AM
Post#15


UtterAccess VIP
Posts: 21,357
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Is that the O'Reilly who runs Ye "Olde" Malte Shoppe? smile.gif

--------------------
Go to the top of the page
 
Minty
post Oct 11 2017, 11:01 AM
Post#16



Posts: 64
Joined: 5-July 16



@Doug - Fair point - you could use a querydef with it I guess, but it all gets a bit more involved.
Go to the top of the page
 
lex
post Oct 11 2017, 04:11 PM
Post#17



Posts: 665
Joined: 20-October 05



I'm always amazed at the support I get. I'm sorry, but today I got wrapped up being a road program manager (jack of all trades - master of none). I'll try your ideas and get back soon.

Thank you again, Minty, Doug and Doctor9!!

Lex
Go to the top of the page
 
lex
post Oct 12 2017, 04:05 PM
Post#18



Posts: 665
Joined: 20-October 05



Alright, Gang! I got it!

The table setup by others (by the way, Doug - I've asked them to change things with tables - but they don't get good design) is a lookup. This, I believe, is why I THOUGHT what Doug provided did not work. After much effort (using a subquery that worked), I realized that the field

InnovationCentral.[Who's idea is This?]

should have ".value" after it ... which then made it work. I've heard lookups are bad. I had no idea to use ".value" as it's not apparent in the GUI for the table.

Anyhow - thanks to all for your help.

Lex


Go to the top of the page
 
doctor9
post Oct 12 2017, 04:25 PM
Post#19


UtterAccess Editor
Posts: 17,736
Joined: 29-March 05
From: Wisconsin


Lex,

Glad you figured it out!

If they're willing, feel free to send them here. Just based on the size of the scrollbar on the InnovationCentral table, it looks like there's a TON of fields in there. Besides avoiding lookup fields and names like "field1", "F19" and "Who's Idea is This?", one of the goals of a good relational database table is to be tall and thin, i.e. few fields, lots of records, rather than short and fat.

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
lex
post Oct 12 2017, 07:44 PM
Post#20



Posts: 665
Joined: 20-October 05



Will do, doctor9! Thanks!
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    19th October 2017 - 09:46 AM