Aggregate Feature that works with strings

[gavinjm]

I am working with an application that has activities and subactivities; i.e. the activity table is tied to the subactivity table by a one-to-many relationship. I am looking for a way to efficiently summarize data from a text field in the subactivities table and associate it with the activity in a form and/or report. Similar to the way number type fields can be summed. Following is an example:

Activity 1
Subactivity 1.1 [strfield1.1]
Subactivity 1.2 [strfield1.2]
Subactivity 1.3 [strfield1.3]
Activity 2
Etc.

Note, that [strfield] 1.1, 1.2, 1.3 are the same field in different records.

How do I present this is a form or report as:

Activity 1: [strfield1.1] [strfield1.2] [strfield1.3]

In other words, having information (text fields) from different records appear on the same line.

Is my question clear?

I have been working around this for awhile but I would like to resolve it. I think I could do it with Dlookup, by scrolling through the number of fields in subactivity records associated with different activities but it does not seem an efficient solution.

Any tips?

Thanks

 

[huzza]

Hi,
The only thing I can suggest is if all the activities have the same number of subactivities then you could use columns in your report. If you set the number of columns equal to the number of subactivities+1 and group by the activity. Have the activity 1, activity 2 etc in the group header then they will show up at the start of each line with the subactivities listed after.
If the number of subactivities differs in each case then I'm affraid I've got no suggestions.
Richard

 

[Quehay]

If I get the question correctly. . .you can do what Richard suggests and group by [Activity] in a report and then use the detail section for displaying [SubActivity]. He's right about needing the number of SubA's clearly delimited to be able to do a CrossTab (columns with Sub Name headings and a check, 'X', etc., to indicate possession.

Alternative: Use the detail section to keep going down vertically to display the SubA's with a Header Section that displays the parent Activity; this way you can shrink or expand acc. to the number of subs.

Alternative: If you have a table of SubA's you can do an outer join, showing Activities with every possible sub. This will automatically expand as the number of defined subs does (Report formatting won't follow this without VBA routine and then you'll run out of margin) and use a query to display results--you can then dump this into Excel or Word for display or independent storage.

RE the Aggregate: Did you want to count the number of Subs a given Activity has in the report? If so just put a txtbx with =Count([Subs]) control source in the group footer.

Hope this helps--post again to rephrase the problem if necessary--it's an interesting thread and probably useful to others!

 

[gavinjm]

Folks:

I believe I found what I was looking for in my web searches over the weekend. I appreciate responses from QUEHAY and HUZZA,. I found the following on the website:

http://home.att.net/~dashish/

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 &amp; rs(strFldConcat) &amp; &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 **********

 

[huzza]

Hi,
Thanks for letting us know, that could be really useful.
Richard