Solved

Combining data from multiple rows into a single row

Posted on 2008-06-17
13
659 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

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

Expert Comment

by:GRayL
Comment Utility
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
Comment Utility
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:Alastair_White
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks, glad to help.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

763 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now