Solved

Combining data from multiple rows into a single row

Posted on 2008-06-17
13
663 Views
Last Modified: 2012-08-13
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.
0
Comment
Question by:Alastair_White
  • 7
  • 6
13 Comments
 
LVL 44

Expert Comment

by:GRayL
ID: 21805191
Start by defining what it is you need to see from both tables, and which of those fields need to be updatable.  
0
 

Author Comment

by:Alastair_White
ID: 21805509
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.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 21805784
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?
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

Author Comment

by:Alastair_White
ID: 21806374
I'm trying to go with option # 3, if I can get it figured out.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 21806645
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;




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

Open in new window

0
 

Author Comment

by:Alastair_White
ID: 21808002
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.
0
 

Author Comment

by:Alastair_White
ID: 21823102
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.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 21837758
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?
0
 

Author Comment

by:Alastair_White
ID: 21846126
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.
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 Function

Open in new window

0
 
LVL 44

Accepted Solution

by:
GRayL earned 500 total points
ID: 21847497
Sorry about all those dumb errors.  Actually I was just seeing if you would catch all my deliberate mistakes ;-)

I think you've got it except the final If statement has to be outside the the Loop.  It's only purpose is to remove the comma and space that were concatenated after the final DivStr.  By keeping it inside, you will lose all the puctuation and spaces.
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
Loop
If Len(DivStr) > 1 Then DivStr = Left(DivStr, Len(DivStr) - 2) 'delete trailing ', '
End Function

Open in new window

0
 

Author Closing Comment

by:Alastair_White
ID: 31469880
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!
0
 

Author Comment

by:Alastair_White
ID: 21848686
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.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 21849403
Thanks, glad to help.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question