Combining data from multiple rows into a single row
Posted on 2008-06-17
I know this has been asked many times before. I have read several Q's and their accepted solutions - but I have failed to grasp how to do this.
I have a Job table and an associated JobDiv table. Each Job may involve multiple Divisions.
Here would be an example of the table:
On my "View All Jobs" form, I need to be able to display the information that Job "28-548" involves Divisions 01, 02, & 05. I can't have a Subform since the main form is in "Continuous Forms" view.
I had thought that I should create a temporary table when (just before?) the "View All Jobs" form opens, and have the form be based on that table, and have a field in there that somehow gathers up the multiple rows from the JobDiv table and has in one row "01, 02, 05" as the data associated with JobNo "28-548".
Desired result row:
28-548 01, 02, 05
As I said, I've seen several posts in here that sound similar to my situation, but I have not been able to successfully decipher and apply the accepted solutions to solve my problem.
1. I don't understand exactly what to use for the record source of the form - presumably an SQL statement that would include the "concatenation" of the multi-row data...(calling the function within the SQL)?
2. Not real sure about how to create the function in the first place, but I assume that's the way to do it, since several posts with accepted solutions have been centered around that method.
3. Would the resultant recordset be updateable, as far as the other fields (not the concatenated Div field)? This form is used at daily progress meetings, and it is very convenient to be able to alter the data on the fly - while being able to view multiple (continuous records) jobs - but boss man also wants to be able to see if the jobs involve multiple divisions.
OK. Help me.