Re-Post of Grouping Records Issue 1

[happybunnyj]

I posted this earlier and received a response to try dhookom's "Concatenate" function, but I have to say, that I cannot get the code to work (I followed direction on uncommenting where applicable, etc.).

Does anyone have another solution I can try? Thanks.

I have an Access report that lists DVD titles and the speakers associated with each. I'm grouping by each DVD and listing each speaker underneath. My problem is each speaker is starting on a new row. I've TRIED columns, but I can only fit 2 on a page and it looks crazy with the names spaced so far apart. Can someone please help me with produce a report using the following as an example?

DVD Title One

Doe Jane, Doe John, Smith Jane, Smith John, (etc...)

What I CURRENTLY get is:

DVD Title One

Doe Jane
Doe John
Smith Jane
Smith John (etc...)

 

[pbaldy]

There's also

http://www.mvps.org/access/modules/mdl0004.htm

and

http://support.microsoft.com/kb/210163/en-us

Paul
MS Access MVP 2007/2008

 

[happybunnyj]

Thanks, I will try this and if I can't figure it out, I'll have to do a sub-report (which won't look as nice).

 

[dhookom]

You could try provide us with your table structures and data types with sample records. I'm sure we can get this working for you.

Duane
Hook'D on Access
MS Access MVP

 

[happybunnyj]

Attached is a DB with the tables and sample data for the report in which I would like to concatenate speakers.

I left off using a subreport because I couldn't get Dhookom's concatenate function to work (and even that was a problem as it was repeating a row for each value in the sub-report).

I'm new to VB and am eager to learn from this. Thanks in advance for your assistance!

 

[happybunnyj]

Sorry. I thought I could upload directly from my hard drive.

 

[dhookom]

You can take the time to type or copy and paste from your mdb into a reply. Otherwise you could use the solution to Upload to box.net as in the attachment to reply.

Duane
Hook'D on Access
MS Access MVP

 

[happybunnyj]

Thanks again, Duane.

Here's the link to the sample DB I uploaded to box.net:

http://www.box.net/shared/w9jl8ico4s

 

[dhookom]

You would need to create a new module and copy the concatenate function into it. Save the module as modConcatenate.

Then remove the speakers and speaker item join tables from the report's record source. Delete the subreport from the detail section and remove the speaker references from the Sorting and Grouping dialog.

Create a query "qselSpeakers" with a SQL view of:

SELECT [tbl-Item Speaker Join].ItemID, [FirstName] & " " & [LastName] AS FullName
FROM [tbl-SPEAKERS] INNER JOIN [tbl-Item Speaker Join] ON [tbl-SPEAKERS].SpeakerID = [tbl-Item Speaker Join].SpeakerID
ORDER BY [tbl-SPEAKERS].LastName, [tbl-SPEAKERS].FirstName;

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

Sorry, posted too soon.

Then add a text box to the detail section of the report with a control source of:
[blue][tt]=Concatenate("SELECT FullName FROM qselSpeakers WHERE ItemID =" & [ItemID])[/tt][/blue]

This should display all the speakers.

Duane
Hook'D on Access
MS Access MVP

 

[happybunnyj]

It works! THANK YOU, Duane!!!