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

[wchelly]

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?

 

[Trevor G]

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.

 

[wchelly]

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

 

[Trevor G]

Im not sure you can do this, try looking at a Cross Tab query.

 

[boblarson]

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

 

[wchelly]

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

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?

 

[boblarson]

You could use the Replace function around it

MyField:Replace(ConcatRelated(.....etc....), ", ,", ", ")

 

[wchelly]

You could use the Replace function around it

MyField:Replace(ConcatRelated(.....etc....), ", ,", ", ")

Hi Bob,
Thanks for your response...this works, but it makes the query slow. Is that typical?

 

[boblarson]

Yeah, it could be slow. You probably should modify the ConCatRelated to bypass nulls.

 

[wchelly]

Yeah, it could be slow. You probably should modify the ConCatRelated to bypass nulls.

Do you mean in the query or VBA?

 

[boblarson]

Do you mean in the query or VBA?

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:

   '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

 

[wchelly]

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

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:

   '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

 

[boblarson]

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

Remove one and it should work.

 

[wchelly]

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!

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

Remove one and it should work.

 

[boblarson]

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.

 

[wchelly]

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

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.

 

[boblarson]

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

Answer posted there.