Combining data from multiple rows into a single row
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:
JobNo Div
28-548 01
28-548 02
28-548 05
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:
JobNo Divs
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.
I have a Job table and an associated JobDiv table. Each Job may involve multiple Divisions.
Here would be an example of the table:
JobNo Div
28-548 01
28-548 02
28-548 05
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:
JobNo Divs
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.
GRayL
Start by defining what it is you need to see from both tables, and which of those fields need to be updatable.
ASKER
From the Job table I need to see all fields, and from the JobDiv table I want to see which Divisions the Job is related to. Since I can't have a subform, I'd want to see all the Divisions the job is related to in one record each (the concatenation problem).
All the fields in the Job table would need to be updateable. The Division(s) field can be "read only". The user could go to another form to update the Division data.
All the fields in the Job table would need to be updateable. The Division(s) field can be "read only". The user could go to another form to update the Division data.
You could have two subforms, the top one (subform1) hosting the Jobs table and an underlying subform (subform2) displaying the Divisions working on the selected job in subform1. Alternatively, go to single form view for jobs and use a subform to view the divisions.
You may be able to leave the Continuous Forms view and insert an additional textbox in the form with a control source that calls a function that creates a concatenated string of Div numbers.
Comments?
You may be able to leave the Continuous Forms view and insert an additional textbox in the form with a control source that calls a function that creates a concatenated string of Div numbers.
Comments?
ASKER
I'm trying to go with option # 3, if I can get it figured out.
You need to create a standard module and insert the code in the snippet , assumes the division field is Div in table DivsJobs.
Call the function via a Query:
SELECT JobNo, DivStr(JobNo) FROM DivsJobs GROUP BY JobNo;
Call the function via a Query:
SELECT JobNo, DivStr(JobNo) FROM DivsJobs GROUP BY JobNo;
Public Function DivStr(myJob as String) as String
Dim db as Database, rs as Recordset, strFind as String
Set db = CurrentDB
Set rs =db.Openrecordset("DivsJobs",dbOpenSnapShot)
rs.movelast
rs.movefirst
strFind = "JobNo = '" & myJob & "'"
rs.findfirst strfind
Do while not rs.nomatch
DivStr = DivStr & rs.Div & ", "
rs.FindNext strFind
If len(DivStr)>1 then DivStr = Left(DivStr,Len(DivStr)-2) 'delete trailing ', '
End Sub
ASKER
Not suprisingly, I'm doing something (or a few somethings) wrong. Is myJob supposed to be a value, or maybe a field name, in the query or in the function?
Also, I created the standard module, and pasted the code - all I changed was the "DivJobs" - the table's name is "JobDiv". (The field in question is "Div") But I get an error "Undefined function 'divstr' in expression" when I try to run the query.
Also, I created the standard module, and pasted the code - all I changed was the "DivJobs" - the table's name is "JobDiv". (The field in question is "Div") But I get an error "Undefined function 'divstr' in expression" when I try to run the query.
ASKER
I haven't gotten a response for a couple days and I really need this to work, so I decided to start monkeying around... but I was wondering about some things, like if the function is called "DivStr", what is being referred to in line 10? I don't know anything at all about how functions work. I did not understand that the name of the function (line 1) is apparently also the name of a variable (line 10) that will be populated or will populate the Div field as the loop repeats itself.
Also I don't really understand about the loop - what determines how many times it repeats? Does is go through each record (of the recordset "rs", created in line 4) and find all entries for that certain jobno (defined in line 7, I guess, but I'm not sure where "myjob" comes from...) then somehow moves on to the next jobno in the main query?
I had changed line 10 to "div = div & rs.div & ", ", not knowing at all if that was anything correct to do, and I got a "do without loop" error message. Does a "do"
need a "loop", syntax-wise?
I also wondered if I had made it apparent that I need this concatenated data to appear in every record of a recordset which should include all job numbers; I'm not just dealing with one job at a time, I need to see them all.
I'm pretty lost here. Lookin' for a little help, s'il vous plaît.
Meantime I've tried going with the two subforms - one with all the jobs in continuous forms view, and another one with the related Divisions, linked to an unbound control on the main form, the value of which is set on the Current event of the subform with all the jobs in it. This works kinda OK for viewing, but you can't filter for given Division(s). That's the main reason I want the concatenated data in the source for the form - so my users can filter for the Div(s).
Ignorance is not bliss.
Also I don't really understand about the loop - what determines how many times it repeats? Does is go through each record (of the recordset "rs", created in line 4) and find all entries for that certain jobno (defined in line 7, I guess, but I'm not sure where "myjob" comes from...) then somehow moves on to the next jobno in the main query?
I had changed line 10 to "div = div & rs.div & ", ", not knowing at all if that was anything correct to do, and I got a "do without loop" error message. Does a "do"
need a "loop", syntax-wise?
I also wondered if I had made it apparent that I need this concatenated data to appear in every record of a recordset which should include all job numbers; I'm not just dealing with one job at a time, I need to see them all.
I'm pretty lost here. Lookin' for a little help, s'il vous plaît.
Meantime I've tried going with the two subforms - one with all the jobs in continuous forms view, and another one with the related Divisions, linked to an unbound control on the main form, the value of which is set on the Current event of the subform with all the jobs in it. This works kinda OK for viewing, but you can't filter for given Division(s). That's the main reason I want the concatenated data in the source for the form - so my users can filter for the Div(s).
Ignorance is not bliss.
Been away for a couple of days.
In line 1, myJob is the name of the variable assigned to take JobNo passed by calling the function DivStr(). Be sure you made the function Public.
The loop is limited by the number of records containing the job Number passed to the function. FindFirst and FindNext tested my .NoMatch will keep the loop going until it runs out of records and returns a .NoMatch.
The code should return one record for every different job number in the table - with the concatenated list of Div numbers.
Can you post the code as you have modified it, assuring yourself of course that all table and field names are spelled correctly?
In line 1, myJob is the name of the variable assigned to take JobNo passed by calling the function DivStr(). Be sure you made the function Public.
The loop is limited by the number of records containing the job Number passed to the function. FindFirst and FindNext tested my .NoMatch will keep the loop going until it runs out of records and returns a .NoMatch.
The code should return one record for every different job number in the table - with the concatenated list of Div numbers.
Can you post the code as you have modified it, assuring yourself of course that all table and field names are spelled correctly?
ASKER
Hello GRayL! Am I glad to see you...
OK - Getting really close now!
Thanks for the clarification on those concepts I was wondering about.
Meanwhile, I made the following modifications:
1. In Line 10, apparently "rs.div" is supposed to be "rs!div". Until I changed the dot to an exclamation point, I kept getting a "method or data member not found" message. Changed it and that message went away.
2. After I changed line 10, I kept getting a "Do without Loop" error. I added a new line 13, wildly guessing and simply entering the word "Loop". Then that message went away.
3. Then I got "Expected End Function" error, so I changed the last line (now 14) to "End Function" instead of "End Sub".
Now, after making those three changes, I ran the query and - POW - like magic no error messages, and I got two result columns - one with each job number and one with the associated Div numbers, all concatenated!!!!
Like I said, we're really close to my desired result...
However, despite what seems in line 10 to clearly add a comma and a space, there are no commas or spaces. Maybe line 12 needs a tweak? If I remark it out, I get all the commas and spaces, plus of course one extra set.
OK - Getting really close now!
Thanks for the clarification on those concepts I was wondering about.
Meanwhile, I made the following modifications:
1. In Line 10, apparently "rs.div" is supposed to be "rs!div". Until I changed the dot to an exclamation point, I kept getting a "method or data member not found" message. Changed it and that message went away.
2. After I changed line 10, I kept getting a "Do without Loop" error. I added a new line 13, wildly guessing and simply entering the word "Loop". Then that message went away.
3. Then I got "Expected End Function" error, so I changed the last line (now 14) to "End Function" instead of "End Sub".
Now, after making those three changes, I ran the query and - POW - like magic no error messages, and I got two result columns - one with each job number and one with the associated Div numbers, all concatenated!!!!
Like I said, we're really close to my desired result...
However, despite what seems in line 10 to clearly add a comma and a space, there are no commas or spaces. Maybe line 12 needs a tweak? If I remark it out, I get all the commas and spaces, plus of course one extra set.
Public Function DivStr(myJob As String) As String
Dim db As Database, rs As Recordset, strFind As String
Set db = CurrentDb
Set rs = db.OpenRecordset("JobDiv", dbOpenSnapshot)
rs.MoveLast
rs.MoveFirst
strFind = "JobNo = '" & myJob & "'"
rs.FindFirst strFind
Do While Not rs.NoMatch
DivStr = DivStr & rs!Div & ", "
rs.FindNext strFind
If Len(DivStr) > 1 Then DivStr = Left(DivStr, Len(DivStr) - 2) 'delete trailing ', '
Loop
End FunctionASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perhaps all the EE Genius/Master/Guru types should include deliberate mistakes in their proposed solutions...the sense of panic heightens the awareness level of the student/victim, and the lessons learned this way are certainly burned deeper into my consciousness than spoon-fed solutions would be!
But seriously...I really do appreciate the help! I changed the query that calls the function to an Insert Into, so the results pop into a temp table which I join to the main Jobs table to make a completely updateable recordset for my form, and I have exactly what I wanted in the Division text box, and can sort or filter as needed.
Although similar solutions had been posted previously, I couldn't make heads or tails out of them. Thanks for breaking it down for me so I could get it. Yet another positive EE experience! No doubt you'll see me asking questions in the future - thanks again!
But seriously...I really do appreciate the help! I changed the query that calls the function to an Insert Into, so the results pop into a temp table which I join to the main Jobs table to make a completely updateable recordset for my form, and I have exactly what I wanted in the Division text box, and can sort or filter as needed.
Although similar solutions had been posted previously, I couldn't make heads or tails out of them. Thanks for breaking it down for me so I could get it. Yet another positive EE experience! No doubt you'll see me asking questions in the future - thanks again!
ASKER
And I moved the Loop line up one as you suggested - commas and spaces are coming out perfectly. This issue is completely solved to 100% satisfaction.
Thanks, glad to help.