Set up your tables in a good relational Parent-Child structure.
Then write queries against them. This function returns a list of values
separated by a semi-colon.
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.
Paste this function into a Module:
'************ 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) & ";"
.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 **********
