Hi Garrett. You may want to have a subquery take care of the [Count] and [Ages] fields, with a custom function to derive [Ages]. Start with a custom function to return an age given a birthdate. Here is a good function that comes from a Microsoft KB article:
Function Age(varBirthDate As Variant) As Integer
Dim varAge As Variant
If IsNull(varBirthDate) Then Age = 0: Exit Function
varAge = DateDiff("yyyy", varBirthDate, Now)
If Date < DateSerial(Year(Now), Month(varBirthDate), \_
Day(varBirthDate)) Then
varAge = varAge - 1
End If
Age = CInt(varAge)
End Function
Then create a function that takes a parent id (l_Mem_ID in your case) and returns a string of ages for each child. This can be done by executing a query on the fly and looping through the resulting recordset:
Function GetAgesOfChildren(parentID As Integer) As String
Dim sResult As String
Dim sSql As String
Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Set conn = Application.CurrentProject.Connection
Set rst = New ADODB.Recordset
'--select statement uses the Age() function
sSql = "Select Age(DOB) From tbl\_Children Where l\_Mem\_ID = " \_
& parentID & " Order By Age(DOB)"
'--open the recordset
rst.Open sSql, conn
sResult = ""
'--loop through the recordset to create a concatentated string
While Not (rst.EOF)
If sResult <> "" Then sResult = sResult & ", "
sResult = sResult & CStr(rst(0))
rst.MoveNext
Wend
GetAgesOfChildren = sResult
Set rst = Nothing
Set conn = Nothing
End Function
It won't be particularly effecient, but it will get the job done. Your subquery could then just link tbl_Customers to tbl_Children, use the Group By option with the Count() function for your [Count] field, then use the following as an expression for the third [Ages] field in the query grid:
Field-- Ages: GetAgesOfChildren([tbl_Customers].[l_Mem_id])
Table--
Total-- Expression
Make the join between tbl_Customers and tbl_Children a left outer join and you'll get zero values for those customers without children records, rather than having those records drop off. Finally then, you can use this subquery in your main query with the tbl_LATCH table to get your final result set. I hope this helps.
