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
> Query For Comma-separated Info In A Field?, Access 2016    
 
   
KristaC
post Dec 1 2017, 11:35 PM
Post#1



Posts: 70
Joined: 5-February 15



I posted this in the Tables forum...maybe should have posted here instead. Sorry if you're seeing this twice!

Anyone wanna take a crack at this? See attached picture. Try as I may, try as I might...I cannot figure out how to do it. I followed two tutorials and I can't get it to work frown.gif

I'm trying to write a query that will fill a listbox on a Song form. There can be many songs by different groups, and many writers in many groups. I have one writing group that has written more than 100 songs! The list of songs is already long, but considering there are 4 writers, the list for that song becomes 400+ lines long. Rather than showing the same song name many times (for each writer in the group) I want to show the song once and the writer names comma-separated, single row for each song.

Help is more-than-appreciated!

Ohhhhh how these things can drive us novices crazy!!!!
Attached File(s)
Attached File  Screen_Shot_2017_12_01_at_11.19.55_PM.png ( 46.94K )Number of downloads: 8
 
Go to the top of the page
 
RJD
post Dec 1 2017, 11:54 PM
Post#2


UtterAccess VIP
Posts: 7,847
Joined: 25-October 10
From: Gulf South USA


Hi Krista: Actually, using a custom function from our own theDBguy (SimpleCSV), this is very straightforward.

Just create a DISTINCT query to get the un-repeated list of GroupNumbers and use that in a query along with the SimpleCSV function (which you must add to your db).

The final query looks like this ...

SELECT GroupNumber, SimpleCSV("SELECT WGMemberNickname from tblMyRecords WHERE GroupNumber =" & [GroupNumber],", ") AS Writers
FROM qryGroupNumbers;

qryGroupNumbers was created like this ...

SELECT DISTINCT GroupNumber
FROM tblMyRecords;

See the demo attached.

HTH
Joe

Krista: I had an Admin delete your other, identical, thread. Double-posting of the same post is a no-no.
Attached File(s)
Attached File  QueryForCSInfo.zip ( 20.72K )Number of downloads: 3
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
KristaC
post Dec 2 2017, 12:47 AM
Post#3



Posts: 70
Joined: 5-February 15



@RJD -- holy cow! How'd you do that so fast????

Here are shortened versions of the real, real tables (removed private data and a bunch of other fields). I replaced your query qryGroupedData with what I thought would work from the real tables. Basically, there is a "split" table that records for each song each writer's split (so I have a unique contact id in there for the writer but also their "group" or "entity" id on the same row -- it's called posttoentitydeal_fk).

I have to go to the split table, then to the entity or group table, then to the junction table that ties people to groups, then to the ppl table to get their names. Whew.

So, nope. Didn't work -- had to hit "end" on an error 818 times frown.gif Yikes. The error said there were too few parameters... or I think it said parameters as I was too busy clicking "end" to read it. LOL

Thoughts on how to adjust?
This post has been edited by KristaC: Dec 2 2017, 12:49 AM
Attached File(s)
Attached File  Query2ForCSInfo.zip ( 1.96MB )Number of downloads: 2
 
Go to the top of the page
 
RJD
post Dec 2 2017, 01:25 AM
Post#4


UtterAccess VIP
Posts: 7,847
Joined: 25-October 10
From: Gulf South USA


Hi again, Krista: Several problems. First, you did not make the list of numbers DISTINCT. Second, you did not set up the number-to-nickname connection in a query before calling the SimpleCSV.

That done, see qryGroupedNames. Then work backwards to how this was set up.

Some of the numbers do not have associated nicknames (it looks like), so I will let you decide what to do with those.

HTH
Joe

Oh, and "holy cow! How'd you do that so fast????" ...

I actually take a long time to do these demos, then use my time machine to travel back in time to post it and make it look like I was working fast! ohyeah.gif

Really, the demo is something I have done many times before. I just used your data and copied the SimpleCSV, clicked some keys, and ....
Attached File(s)
Attached File  Query2ForCSInfo_Rev1.zip ( 481.8K )Number of downloads: 2
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
KristaC
post Dec 2 2017, 02:25 AM
Post#5



Posts: 70
Joined: 5-February 15



Well, Joe. You didn't have to give up your secret!!

You're all still stars to me -- I'm consistently lost, stuck trying to fix up four consecutive db's that have gotten mauled by various freelancers...and I'm nothing but a music executive working for a boutique start up that helps young artists! Without a clue, but determined, I have ventured into your world. It's been a heck of a journey so far!!

Thanks much...headed to look at all my mistakes now smile.gif

Have a wonderful night and I'll be back here in the morning begging if I can't seem to get it to work! All the gratitude you can imagine...
Go to the top of the page
 
RJD
post Dec 2 2017, 07:30 AM
Post#6


UtterAccess VIP
Posts: 7,847
Joined: 25-October 10
From: Gulf South USA


You are very welcome, Krista. Let us know if this is what you are trying to do, and if you have more issues.

Good luck with the db.

Regards,
Joe

from phone

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
KristaC
post Dec 2 2017, 02:53 PM
Post#7



Posts: 70
Joined: 5-February 15



Worked like a charm!!

I'm fighting with two other issues now....and so goes my Saturday. LOL

Have a wonderful afternoon, Joe!
Go to the top of the page
 
RJD
post Dec 2 2017, 04:50 PM
Post#8


UtterAccess VIP
Posts: 7,847
Joined: 25-October 10
From: Gulf South USA


Glad that worked for you, Krista.

Let us know if we can assist with other issues.

And you have a good afternoon as well. I'm watching the Auburn-Georgia game, but have no dog in that fight. But should be a good one.

Regards
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
GroverParkGeorge
post Dec 3 2017, 08:50 AM
Post#9


UA Admin
Posts: 31,243
Joined: 20-June 02
From: Newcastle, WA


Not a Georgia fan then, eh joe? <grin>

--------------------
Go to the top of the page
 
RJD
post Dec 3 2017, 12:06 PM
Post#10


UtterAccess VIP
Posts: 7,847
Joined: 25-October 10
From: Gulf South USA


Hi George: As I said, I really didn't really have a favorite ("Dog in that fight" was, indeed, a tongue-in-cheek expression!). smirk.gif

I do have family members and friends who are Auburn alums (who are a bit bummed today), but I went to MS State and to AZ State (grad school) and consider myself just a bystander on this one.

But it was a very good game that I watched at a friend's house (OLE Miss alum).

Have a great day ...

Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    17th December 2017 - 02:40 PM