Anyway to concatenate junction table records for report?

[Montez659]

In my db, there are about 5 different items that require junction tables. One is miscellaneous and doesn't pertain to the report. One of them is "Issues" regarding the parent record, and is the second grouping level only to "Reps." One of the junction tables is for Reps assigned to that record. The other two junction tables (where my question pertains to at the moment) are pretty much just lists of 5 or 6 items that really don't hold much value in the report except that the powers that be want to see them for each record.

I originally had these two as m-v fields, but after running into an apparent collision, it was suggested that this was a possible cause. The beauty of the m-v field is that they would display all selected values on one line merely seperated by a comma. Is it possible to do the same with the new junction table that took the place of the m-v field? Meaning, I would like for it to display on the same line (in the same text box on the report)?

Any suggestions or links are always appreciated!

(BTW, it is 4:30pm on Friday. If I don't get an answer prior to leaving for the day, it may be Monday morning before I am able to try whatever is suggested or before I even log onto MrExcel again.)

 

[boblarson]

I think you want this:

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

 

[Montez659]

That looks like what I need. Thanks Bob! I will give it a go and let you know, probably Monday morning.

 

[Montez659]

Bob, it works great, but I do have one question. This is what my control source expression looks like:

=ConcatRelated("provTypeID","JUNCtblSiteVisits_ProvType","visitID = " & [tblSiteVisits_visitID])

Basically, the three elements here are provTypeID, JUNCtblSiteVisits_ProvType, and visitID. provTypeID is just a fk from tblProvType. So essentially, this code and expression returns the fk and I am wanting to get to the actually text behind the fk. I am getting bit confused, though, as to how to modify the code to display the text without (and I shoudln't have too) storing the actual name in the Junction table.

This is an awesome tool! I appreciate the link.

 

[boblarson]

I am getting bit confused, though, as to how to modify the code to display the text without (and I shoudln't have too) storing the actual name in the Junction table.

Create a query which pulls the name in as well as the ID and then use this function and specify the query name instead of the junction table name and specify the name field instead of the provTypeID.

 

[Montez659]

Ahh, duh! Lol, thanks for the memory jog!