Help with SQL statement

xfitr2

DB: Access 2002 Prog: VB6 (using DAO 3.6) (Italics are relationships) tbl_Customer l_Mem_id str_First str_Middle str_Last l_Phone (etc...) tbl_LATCH l_ID_id dt_Date l_Mem_id str_Note tbl_Children l_ID_id l_Mem_id str_Name dt_Date dt_DOB I am trying to build a recordset which pulls all the people from tbl_LATCH and builds a recordset that has a field [Count] which is the total number of children they have and another field [Ages]** which is the ages of the children. Also, included is the fields from tbl_Customer. **[Ages] i.e. (4, 5, 12, 10) If there were four recordsets returned from the subquery or whatever then it would concat them into one field. Any help would greatly be appreciated. Thanks in advance. -Garrett

Mike Ellison

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.