Create Query for one to many, with many on same row...

W

wchelly

Registered User.
Local time
Today, 03:50
Joined
Mar 1, 2010
Messages
146
I have a shipment database with a
Shipment Table and
Materials/Containers Table
Each shipment can have multiple containers and materials.

I want to create a querry field that lists all materials and containers for each shipment. How do I do that?
 
I would create a query on the 2 tables and add the fields in you want. Then save it add any criteria to the fields.

Next create a report which will group by the shipment and then the other fields will show below so you have a solution because the query is the source but the report will do the groupings.
 
I understand what you are suggesting and I have done that for other reports and it works well in many cases. However, The the simple reason I am trying to do the query in this way is because I need for all of the data for the shipment to be on one line. I know it is possible to list the materials like the following:

Shipment 1 1 Toolbox, 1 Loader, 1 LI Container

Rather than

Shipment 1
1 Toolbox
1 Loader
1 LI Container
 
Im not sure you can do this, try looking at a Cross Tab query.
 
boblarson said:
You can do this using Allen Browne's code. See if you can figure it out first but post back if you run into trouble:
http://allenbrowne.com/func-concat.html
Click to expand...


Yes! This works!! But now I have the following problem.

I am concatinating rows and then also concatinating the fields....

ShipperID Material Container Equipment
3 10 Shoes 10 Boxes Tarps
6 15 Pants 15 Bags
7 5 Coats 5 Hangers

So first I concatinate All the materials, all the Containers and all the Equipment

10 Shoes, 15 pants, 5 Coats 10 Boxes, 15 Bags, 5 Hangers , , Tarps

Then I will concatinate together these strings. However if you look at the Equipment "Tarps" it has two commas because there is no equipment needed for the first, but there is a "blank" record so it thinks there is something there. I'm not sure how to get rid of these commas. Any Ideas?
 
You could use the Replace function around it

MyField:Replace(ConcatRelated(.....etc....), ", ,", ", ")
 
boblarson said:
You could use the Replace function around it

MyField:Replace(ConcatRelated(.....etc....), ", ,", ", ")
Click to expand...


Hi Bob,
Thanks for your response...this works, but it makes the query slow. Is that typical?
 
Yeah, it could be slow. You probably should modify the ConCatRelated to bypass nulls.
 
wchelly said:
Do you mean in the query or VBA?
Click to expand...
VBA so then you don't need to worry about blanks. It appears that he handles nulls but didn't take into account empty strings. So, change the code to what I've done in the red:

Code: 
   'Loop through the matching records
    Do While Not rs.EOF
        If bIsMultiValue Then
            'For multi-valued field, loop through the values
            Set rsMV = rs(0).Value
            Do While Not rsMV.EOF
                If Not IsNull(rsMV(0)) Then
                    strOut = strOut & rsMV(0) & strSeparator
                End If
                rsMV.MoveNext
            Loop
            Set rsMV = Nothing
        ElseIf [B][COLOR=red]Len((rs(0) & vbNullString) > 0[/COLOR][/B] Then
            strOut = strOut & rs(0) & strSeparator
        End If
        rs.MoveNext
    Loop
    rs.Close
 
I've typed what you have below but when I compile I get a synax error message.

'Loop through the matching records
Do While Not rs.EOF
If bIsMultiValue Then
'For multi-valued field, loop through the values
Set rsMV = rs(0).Value
Do While Not rsMV.EOF
If Not IsNull(rsMV(0)) Then
strOut = strOut & rsMV(0) & strSeparator
End If
rsMV.MoveNext
Loop
Set rsMV = Nothing
ElseIF Len((rs(0) & vbNullString) > 0 Then
strOut = strOut & rs(0) & strSeparator
End If
rs.MoveNext
Loop
rs.Close

'Return the string without the trailing separator.
lngLen = Len(strOut) - Len(strSeparator)
If lngLen > 0 Then
ConcatRelated = Left(strOut, lngLen)
End If





boblarson said:
VBA so then you don't need to worry about blanks. It appears that he handles nulls but didn't take into account empty strings. So, change the code to what I've done in the red:

Code: 
   'Loop through the matching records
    Do While Not rs.EOF
        If bIsMultiValue Then
            'For multi-valued field, loop through the values
            Set rsMV = rs(0).Value
            Do While Not rsMV.EOF
                If Not IsNull(rsMV(0)) Then
                    strOut = strOut & rsMV(0) & strSeparator
                End If
                rsMV.MoveNext
            Loop
            Set rsMV = Nothing
        ElseIf [B][COLOR=red]Len((rs(0) & vbNullString) > 0[/COLOR][/B] Then
            strOut = strOut & rs(0) & strSeparator
        End If
        rs.MoveNext
    Loop
    rs.Close
Click to expand...
 
Sorry, I had an extra ( in there after the word LEN

Remove one and it should work.
 
Well, the code compiles and the system doesn't return any errors but it's not working the way I think it should work. I continue to get

", , box" for some entries.

It is unclear to me why the commas would display before the data. It is also unclear to me why the system inconsistenly returns commas for different records. Some records display the commas correctly, and some do not. It makes me think it has something to do with how the data is being entered, however what the difference is is not apparent.

Bob, I really appreciate your help. I am glad there are people like you willing to help!

boblarson said:
Sorry, I had an extra ( in there after the word LEN

Remove one and it should work.
Click to expand...
 
You need to set a breakpoint and F8 through the code to see what is happening. Try putting criteria on the PK field in your actual query so that only a row with that result is being returned. Then set the breakpoint and F8 through and watch the values at each step.
 
I've responded to this in the VBA Programmers Forum as my question seems to have morphed into a coding issue. :) You can find my response here:

http://www.access-programmers.co.uk/forums/showthread.php?p=1193766#post1193766

boblarson said:
You need to set a breakpoint and F8 through the code to see what is happening. Try putting criteria on the PK field in your actual query so that only a row with that result is being returned. Then set the breakpoint and F8 through and watch the values at each step.
Click to expand...
 
You must log in or register to reply here.

Similar threads

R
Current Inventory Calculations
Replies
6
Views
478
tvanstiphout
T
C
Automatic Label Numbering
Replies
19
Views
649
ChrisMore
C
H
Query of Sum of One field with different criteria
Replies
14
Views
746
hrdpgajjar
H
A
One to Many query question
Replies
12
Views
495
Isaac
Isaac
D
Only a single record shows in the subform
Replies
2
Views
230
Gasman
Gasman

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom