Convert Rows to Columns

[AccessNewbie73]

I am working on creating something to support trainings. I'd like to be able to send an email to the person leading the training with the list of the training participants that have been assigned to the training. To do this I need a single record with the training information that I can then link to the list of assigned participants. I'd like to be able to take Table A below and produce a query that has a single row with the TrainingSessionID and as many columns in the output query as there are rows in the table below. The trick is that the number of rows will change - some sessions may have as few as two participants and others could have as many as 15 or so. I've attached a sample database with the table containing the data set in Table A below. Much thanks for any assistance as I am stumped on this one.

TableA
TrainingSessionID ParticipantName ParticipantAdded
1 Smith, Joe (1234) 5/4/16 15:30
1 Jones, Mike (3456) 5/4/16 15:48
1 Johnson, John (4567 5/4/16 23:50

Column A above is the same for each record in the table. Column C (while not necessary for the output query) has unique values for each record if this is useful. How do I "convert" the above table to create a query like what is displayed below? Keeping in mind the number of rows in the above table is unknown (it may be between 2 - 15 records.


Desired Output
TrainingID Name1 Name2 NameN
1 Smith, Joe (1234) Jones, Mike (3456) Johnson, John (4567)

 

[Isskint]

You want to look up Crosstab queries. This will give you the recordsource for a report. However the variable nature of the number of headings will cause problems (each time you run the report, unless you have the same number of headings as when you first run it, you get errors and parameter prompts).

There's a very simple solution to this problem: Provide generic heading names for the maximum number of headings you are likely to have - you said 15?. In Design View for your query, click in the table area at the top of the query, right-click, and select Properties. On the single tab for Query Properties, there's an entry called Column Headings. You can enter a list of column headings here (Name 1, Name 2.... Name15?) and, if the crosstab query doesn't generate a column with this heading, then a column with the heading would be inserted.

 

[Galaxiom]

Take a look post 20 on this thread.

Stopher shows how to deal with a variable number of records in a Cross Tab.

 

[Isskint]

Hi Galaxiom,

Wow, good link (still on the Yellowglen?). Would that method still cause the headings problem in a report though?

 

[AccessNewbie73]

Thank you Galaxiom for providing the link to the crosstab thread. What an insightful link you provided, challenging me to think differently about my approach!!

 

[Galaxiom]

Would that method still cause the headings problem in a report though?

I have not confirmed that the following works for Reports but it does in Forms. I don't use Reports for anything beyond the very basics so I am not well versed in them.

It could result in names for the fields that were not known by the ControlSource of the report controls.

This can be overcome by simultaneously looping through the Controls and Recordset.Fields Collections while assigning the Fields' Name property to the Controls' ControlSource.

The Labels can also be assigned the Fields' Name property by referring to the Controls' Controls Collection. The label of a Control is referred to in a loop as:

Me.Controls(loopcounter).Controls(0)

(still on the Yellowglen?)

When it isn't too pricy. Quite fond of WolfBlass Red Label too and it is often to be had at a very good price. Recently the Jacobs Creek Trilogy has been going for $10. Hard to go past unless Oyster Bay is on super special.