Can I concatenate child fields into one? 1

[deovolente]

Not a programmer, and pretty new to Access, so there is probably a simple fix for this but here goes... I am trying to run a report of Customers and Orders for fulfillment purposes. I would like to be able to concatenate all the ProductName values from an Order Details table in one field so they only take up one line in the report rather than several. Any suggestions?

 

[WP]

Yes. But it is a tricky one requiring some code.

What you need to aim for is a table stucture of just:

Order Number Customer ID Products
001 C0001 Product 1, Product 2, Product 3 etc
002 C0001 Product 8, Product 9, Product 13 etc
003 C0002 Product 10, Product 11

You need to create this work table from your order details table.

I have done something similar to this.

If this is the right track then I'll dig out the code I used. If not then please give more information.


Bill Paton
william.paton@ubsw.com

http://www.neptune400.co.uk/

Check out my website !

 

[Telsa]

Simply code into the field on the report

FieldName1 & ", " & FieldName2 & ", " FieldName3

etc.

Place that in the one field and stretch the field to accommodate the extra data. You can also change the CanGrow property of the field to Yes so it will stretch out.

You can use any delimiter you want. I just picked the comma as an example.

Is this what you were looking for???

HTH

Mary

 

[deovolente]

Thank you for your reply!!

The report should look like this:
John Doe Address CSZ
Product1; Product2; Product3

The database is similar to Nwind with Customers, Orders, Order Details, & Products tables.

While I was waiting for a reply, I found the following post on The Access Web but I keep getting a syntax error when I plug it into my query. (Yes, I'm a newbie, but I did remember to plug in my own object names...) Maybe I'm not referencing them correctly. I dunno. Maybe your code will be better. PLEASE HELP!!!

Thank you!

(

http://www.mvps.org/access/modules/mdl0004.htm)

---Posted by Dev Ashish---

Modules: Return a concatenated list of sub-record values

(Q) How can I extract all values of a field from a table which is the related to another table in a 1:M relationship?

(A) The following function fConcatChild can be used in a query

SELECT Orders.*, fConcatChild("Order Details","OrderID","Quantity","Long",[OrderID]) AS SubFormValuesFROM Orders;

This example is based on Orders and Orders Details tables in Northwind database which are related in a 1:M relationship. The fConcatChild simply states Concatenate all values in field Quantity in table Order Details where linking field is OrderID of datatype Long, for each value of [OrderID] returned by the table Orders.


'************ Code Start **********
Function fConcatChild(strChildTable As String, _
strIDName As String, _
strFldConcat As String, _
strIDType As String, _
varIDvalue As Variant) _
As String
'Returns a field from the Many table of a 1:M relationship
'in a semi-colon separated format.
'
'Usage Examples:
' ?fConcatChild("Order Details", "OrderID", "Quantity", _
"Long", 10255)
'Where Order Details = Many side table
' OrderID = Primary Key of One side table
' Quantity = Field name to concatenate
' Long = DataType of Primary Key of One Side Table
' 10255 = Value on which return concatenated Quantity
'
Dim db As Database
Dim rs As Recordset
Dim varConcat As Variant
Dim strCriteria As String, strSQL As String
On Error GoTo Err_fConcatChild

varConcat = Null
Set db = CurrentDb
strSQL = "Select [" & strFldConcat & "] From [" & strChildTable & "]"
strSQL = strSQL & " Where "

Select Case strIDType
Case "String":
strSQL = strSQL & "[" & strIDName & "] = '" & varIDvalue & "'"
Case "Long", "Integer", "Double": 'AutoNumber is Type Long
strSQL = strSQL & "[" & strIDName & "] = " & varIDvalue
Case Else
GoTo Err_fConcatChild
End Select

Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

'Are we sure that 'sub' records exist
With rs
If .RecordCount <> 0 Then
'start concatenating records
Do While Not rs.EOF
varConcat = varConcat & rs(strFldConcat) & &quot;;&quot;
.MoveNext
Loop
End If
End With

'That's it... you should have a concatenated string now
'Just Trim the trailing ;
fConcatChild = Left(varConcat, Len(varConcat) - 1)

Exit_fConcatChild:
Set rs = Nothing: Set db = Nothing
Exit Function
Err_fConcatChild:
Resume Exit_fConcatChild
End Function

'************ Code End **********

 

[TheInsider]

Hi deovolente,
I'll put my 2 cents in here too =). Mary is correct if the fields to concatenate correspond to one single record. For example, a client table may have a field for first name and a field for last name. So if you have a client named Tom Smith, you could combine his name (from the latter fields) into a single field as &quot;Tom Smith&quot; or &quot;Smith, Tom&quot;, with the &quot;&&quot; operator. ie. [Last Name] & &quot;, &quot; & [First Name]. However, if you are concatenating fields from multiple records into a single field - and I am sure that you are - this will not work. You need to do what Bill has suggested.

Rob Marriott
rob@career-connections.net

 

[TheInsider]

Hi,
Ignore my post deovolente, you re-posted more detail while I was writing the above =).

Rob Marriott
rob@career-connections.net

 

[WP]

Devonlente,

This code is 'neat' or 'cool' and better than what I was going to suggest.

Don't plug it into your query. Use it in the report :

So, in the control source of your text box on the report (Products) :

=fConcatChild(&quot;Order Details&quot;,&quot;OrderID&quot;,&quot;Quantity&quot;,&quot;Long&quot;,txtOrderID)

where txtOrderID is the name of the Order ID text box. This can be a hidden field on your report.



Bill Paton
william.paton@ubsw.com

http://www.neptune400.co.uk/

Check out my website !

 

[deovolente]

Something is still amiss... I haven't a clue about any sort of code, so I'll have to have someone out there interpret this for me!! Thanks for your help in advance.

I got the report set up just how I wanted and used the fConcatChild function from the module, but when I click to view the report, it brings up the module and highlights parts such as (red text):

Option Compare Database

Function fConcatChild(strChildTable As String, _
strIDName As String, _
strFldConcat As String, _
strIDType As String, _
varIDvalue As Variant) _
As String

'Returns a field from the Many table of a 1:M relationship
'in a semi-colon separated format.
'
'Usage Examples:
' ?fConcatChild(&quot;Order Details&quot;, &quot;OrderID&quot;, &quot;Quantity&quot;, _
&quot;Long&quot;, 10255)
'Where Order Details = Many side table
' OrderID = Primary Key of One side table
' Quantity = Field name to concatenate
' Long = DataType of Primary Key of One Side Table
' 10255 = Value on which return concatenated Quantity
'
Dim db As Database

Dim rs As Recordset
Dim varConcat As Variant
Dim strCriteria As String, strSQL As String
On Error GoTo Err_fConcatChild

varConcat = Null
Set db = CurrentDb
strSQL = &quot;Select [&quot; & strFldConcat & &quot;] From [&quot; & strChildTable & &quot;]&quot;
strSQL = strSQL & &quot; Where &quot;

Select Case strIDType
Case &quot;String&quot;:
strSQL = strSQL & &quot;[&quot; & strIDName & &quot;] = '&quot; & varIDvalue & &quot;'&quot;
Case &quot;Long&quot;, &quot;Integer&quot;, &quot;Double&quot;: 'AutoNumber is Type Long
strSQL = strSQL & &quot;[&quot; & strIDName & &quot;] = &quot; & varIDvalue
Case Else
GoTo Err_fConcatChild
End Select

Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

'Are we sure that 'sub' records exist
With rs
If .RecordCount <> 0 Then
'start concatenating records
Do While Not rs.EOF
varConcat = varConcat & rs(strFldConcat) & &quot;;&quot;
.MoveNext
Loop
End If
End With

'That's it... you should have a concatenated string now
'Just Trim the trailing ;
fConcatChild = Left(varConcat, Len(varConcat) - 1)

Exit_fConcatChild:
Set rs = Nothing: Set db = Nothing
Exit Function
Err_fConcatChild:
Resume Exit_fConcatChild
End Function

 

[WP]

This means that your DAO (Data Access Objects) has not been installed / registered.

Re-install ACCESS and select all options so as to install the DAO drivers.

If still not working you will have to register the driver manually.


Bill Paton
william.paton@ubsw.com

http://www.neptune400.co.uk/

Check out my website !

 

[deovolente]

WP,

Thank you for your responses! I really appreciate your insight. Unfortunately, I have had another project I've been working on and couldn't respond back to you.

I have re-installed Access with all the bells and whistles, but that hasn't done the trick. I even tried to modify the code, (dangerous for me, a non-code person) as described in Help for Data Object Library. i.e. changing
Dim db As Database
to
Dim dbs As DAO.Database
and so on.

I'm still missing something. When I try to preview the report, the VBA bugger runs, opens the module and highlights those same points as before. What am I doing wrong now? Thanks for your help!

 

[WP]

OK,

Firstly, I have assumed you are using Access 97. Is this correct ?

If it is then.

Step1.

While in the code form the top menu - Tools, References....
Find Microsoft DAO 3.51 Object Library. Is it selected ?

If not then select it and try again.

If it has some message about a missing file then you need manually register the DAO DLL.

This will be Step2


Bill Paton
william.paton@ubsw.com

http://www.neptune400.co.uk/

Check out my website !

 

[deovolente]

Sorry I wasn't more clear. I'm using Access 2000 and I assume that the code I'm trying to use is for Access 97 with the Data Object Library 3.51.

The Help menu says that 3.51 is not supported by Access 2000, so I have selected the Microsoft Data Object Library 3.6, and changed the code according to the Help menu. Now when I open the report, the debugger doesn't run, but my report is littered with #ERROR.

Any suggestions? Thanks!!

 

[WP]

OK,

Because ACCESS 2000 supports two different types of recordsets ( DAO and ADO) you have to explicity state which type you are using. So,

Try changing :

Dim db As Database

Dim rs As Recordset

To

Dim db As DAO.Database

Dim rs As DAO.Recordset


Bill Paton
william.paton@ubsw.com

http://www.neptune400.co.uk/

Check out my website !

 

[deovolente]

Did that. Still #Error. The debugger doesn't run anymore, I just get that #Error where the field is on the report. Could I have something else mixed up?

Thanks!

 

[WP]

Wow, this is a tough one.

OK,

Does every field on the report have #error ?

What is te recorset of your report. If it is based on a table give me the table fields. If on a query what's the SQL of the query ?

What is the function call in the field set to: =fConcatChild(........)
What is the ..... equal to?

Next I want you to try some debugging. I know it's difficult when you don't know the code but I'll help.

Open the report in design view. View the code ( top menu View, Code).

Find the Function.... easiest way is to use the combo box at top right.
Go to the line If .RecordCount <> 0 Then and press F9. This adds a break-point. Now run the report and the code should come up again.

Highlight the .RecordCount part and press Shift F9. If it is 0 then we are getting there.

Next what is the value of strSQL ?

Enough for now...get to it.


Bill Paton
william.paton@ubsw.com

http://www.neptune400.co.uk/

Check out my website !

 

[deovolente]

WP,

Thank you for you help and your patience. Please bear with me, I will try to answer all the questions you asked, but keep mind that I was given Access 2000, an old database and a manual. I am learning as I go!

The report is based on a query called Fulfillment Qry and uses three separate tables. The query itself works out fine, and is even used in other reports with no problems. It is only when I try to use it with this function that I have problems.

The fields in my query are as follows:
From the table called “Producer Master”: [VIP#], [Prefix], [FirstName], [Middle], [LastName], [Suffix], [CompanyName], [Address], [City], [State], [Zip], [CoopCode], and two concatenated fields from the qry: [ProducerName] and [ProducerSZ].
From the table called “Fulfillment”: [FulfillmentID]
From the table called “Fulfillment Details”: [ProductName] and [VendorName]

There are only two fields that show up on the report: [FulfillmentID] and [CoopCode]

On the report, the value of the field with the function in it is =fConcatChild(&quot;Fufillment Qry&quot;,&quot;FulfillmentID&quot;,&quot;ProductName&quot;,&quot;Long&quot;,[txtFulfillmentID])

I set the break where you asked, at
If .RecordCount <> 0 Then

When I select .RecordCount and press ShiftF9, I get a value of 1.

Thanks for your help!

 

[WP]

RecordCount=1 is a good sign. It means there are records.

Something else to try:

Open the report and let it break again. Pres F8 and step through the code.

When it get to varConcat = varConcat & rs(strFldConcat) & &quot;;&quot; you should see varConcat being buit.

Keep going till you get to Set rs = Nothing: Set db = Nothing and you should get the full product list.

If not then the code is not working and we can look at why tomorrow.

Now 22:05 UK time.

Time to go home now....



Bill Paton
william.paton@ubsw.com

http://www.neptune400.co.uk/

Check out my website !

 

[deovolente]

WP,

I'm sorry about the late night for you last night, I guess I didn't realize the time differential between the States and Scotland. Your input is much appreciated!

Despite all our efforts, something is still wrong. I don't know what could have happened between yesterday and today, but the code will not break at
If .RecordCount <> 0 Then
even with the breakpoint set there. I can set a breakpoint anywhere above that and when I open the report and it will open the code. Also, now when I highlight .RecordCount and press ShirtF9, I get an <out of context> message. What is this? I saved the changes I made yesterday. Nothing should have changed that I know of.

Thanks for all your help!

Valerie

PS I have meetings all day today, so won't be able to get back to you before your work day is done there.

 

[WP]

This is proving too difficult to solve by blindfold. It's a bit like one of those quiz shows where one person is blindfolded and the other tells him/her clues on where to go next.

If you can package the relevant parts in a new db and zip it and then e-mail me.

I wouldn't be able to look at it till Friday ( only have 2000 at home and I'm working away at the moment )

If you need it solved before Monday then start a new thread and maybe someone else can help


WP


Bill Paton
william.paton@ubsw.com

http://www.neptune400.co.uk/

Check out my website !