Selecting only oldest children but storing names of younger siblings

[sistemalan]

My database stores details of children taking part in a music project.

I want to create a query which I can use for a mail merge to write to all the families. Some children are from the same families. I don't want more than one letter to go to the same family, but in the letter I want to be able to refer to each child.

There are two tables: T_Adults & T_Children, which are related in a One to Many Relationship by the common field AdultID.

So far I have the below query which will show all children who attend.

SELECT T_Children.FirstName, T_Children.Surname, T_Children.DateOfBirth, T_Children.AdultID, T_Adults.AdultName, T_Adults.Address1, T_Adults.Address2, T_Adults.PostCode, T_Children.SignedUpForAfterSchool
FROM T_Adults RIGHT JOIN T_Children ON T_Adults.AdultID = T_Children.GuardianID
WHERE (((T_Children.SignedUpForAfterSchool)=True));

What I want to be able to do is as follows:

1 - Where two or more records share the same parent (i.e. if the field T_Children.AdultID is the same for both records) only show the record for the oldest child (calculated from dates of birth).

The query should now show a) children with no siblings and b)The oldest children from families with more than one child taking part.

2 - Create a dynamic field on the records of children with siblings' names which records their siblings' first names. If there is one sibling it should read (for instance) "& John", if two siblings it should read ", Toby & John", if three it should read ", Sam, Toby and John", etc... This is so I can write letters and say things like "We hope that Jim, Sam, Toby and John can join us next week".

I'd be much obliged if someone could point me in the right direction with this.

Thanks in advance!

Alan

 

[sistemalan]

Hi again,

I have completed part 1 of the above, and could really use some help with the second bit. So far I have created 4 queries:

The first shows all children currently taking part:

Q_AttendingAfterSchool

SELECT T_Adults.AdultID, T_Children.FirstName, T_Children.Surname, T_Adults.AdultName, T_Adults.Address1, T_Adults.Address2, T_Adults.PostCode, T_Children.SignedUpForAfterSchool, T_Children.Quit, T_Children.DateOfBirth
FROM T_Adults RIGHT JOIN T_Children ON T_Adults.AdultID = T_Children.GuardianID
WHERE (((T_Children.SignedUpForAfterSchool)=True) AND ((T_Children.Quit)=False))
ORDER BY T_Adults.AdultID;

The second shows only the child with highest date of birth from each family

Q_OldestChild

SELECT Q_AttendingAfterSchool.AdultID, Q_AttendingAfterSchool.AdultName, Min(Q_AttendingAfterSchool.DateOfBirth) AS MinOfDateOfBirth
FROM Q_AttendingAfterSchool
GROUP BY Q_AttendingAfterSchool.AdultID, Q_AttendingAfterSchool.AdultName;

The third is there incase of twins. Two children from the same family with the same date of birth were both returned by the previous query. This one selects the child with the alphabetically lowest first name where date of birth and family are the same:

Q_RemoveTwins

SELECT T_Children.GuardianID, Min(T_Children.FirstName) AS MinOfFirstName
FROM T_Children INNER JOIN Q_OldestSibling ON (T_Children.GuardianID = Q_OldestSibling.AdultID) AND (T_Children.DateOfBirth = Q_OldestSibling.MinOfDateOfBirth)
GROUP BY T_Children.GuardianID
ORDER BY Min(T_Children.FirstName);

The fourth adds address details etc, and currently returns a list of every family with the name of the oldest child (or alphabetically first in case of twins), together with contact details.

Q_MailMerge

SELECT T_Children.FirstName, T_Children.Surname, T_Adults.AdultName, T_Adults.Address1, T_Adults.Address2, T_Adults.Address3, T_Adults.Address4, T_Adults.PostCode, T_Children.Orchestra
FROM T_Adults RIGHT JOIN (Q_RemoveTwins INNER JOIN T_Children ON (Q_RemoveTwins.MinOfFirstName = T_Children.FirstName) AND (Q_RemoveTwins.GuardianID = T_Children.GuardianID)) ON T_Adults.AdultID = T_Children.GuardianID
ORDER BY T_Children.FirstName, T_Children.Surname;

What I need to do now is add an additional field called Siblings which would do the following:

• For each record find all other records from query Q_AttendingAfterSchool where GuardianID field is the same.
• If no other records have same GuardianID, Siblings is blank.
• If other records have same GuardianID, Siblings will be a list of their FirstName fields.

If anyone can help me or even hint at the way forward, I would be most exceedingly grateful.

Cheers,

Alan

 

[gemma-the-husky]

have a totals query that counts families with more than 1 sibling. (or a variation of this)
(you could have a function that forms a string of the sibling names)

join this to your last query, so you get the sibling count (or list) for each of your letter addressees. This gets a bit closer, although it would include all the siblings in the sibling list. (including the addressee)

 

[sistemalan]

Thank you Dave,

Good Suggestion. I now have a query called CountSiblings:

SELECT T_Children.GuardianID, Count(T_Children.DateOfBirth) AS CountOfDateOfBirth
FROM T_Children
GROUP BY T_Children.GuardianID;

And the Q_MailMerge Query now looks like this:

SELECT T_Children.FirstName, T_Children.Surname, T_Adults.AdultName, T_Adults.Address1, T_Adults.Address2, T_Adults.Address3, T_Adults.Address4, T_Adults.PostCode, T_Children.Orchestra, Q_CountSiblings.CountOfDateOfBirth
FROM (T_Adults RIGHT JOIN (Q_RemoveTwins INNER JOIN T_Children ON (Q_RemoveTwins.MinOfFirstName = T_Children.FirstName) AND (Q_RemoveTwins.GuardianID = T_Children.GuardianID)) ON T_Adults.AdultID = T_Children.GuardianID) INNER JOIN Q_CountSiblings ON Q_RemoveTwins.GuardianID = Q_CountSiblings.GuardianID
ORDER BY T_Children.FirstName, T_Children.Surname;

So far, so good.

By the way Gemma looked very lovely. I think Huskys are my favourite dog.


Alan

 

[sistemalan]

I'm still trying to work this out, and it's getting urgent. Can anyone help me figure out what sort of function or whatever I need in order to list the sibling names?

Thanks!

Alan

 

[DCrake]

This is being made up as I type so bear with me.

Public Function ParseForenames(LngAdult As Long) As String

Dim Rs As DAO.Recordset
Dim IntSiblings As Integer
Dim strChildren As String

Set Rs = CurrentDb.OpenRecordset("Select * From TblSiblings Where AdultID = " & LngAdult & " Order By DOB Desc;"

If Not Rs.EOF and Not Rs.BOF Then

   Rs.MoveLast
   Rs.MoveFirst
   IntSiblings =   Rs.RecordCount

   Select Case InSiblings
       Case 1 ' Only 1 child
           strChildren = Rs("forename")
       Case 2 ' 2 children
          strChildren = Rs("forename") & " & "
          Rs.MoveNext
          strChildren = StrChildren & Rs("Forename")
      Case Else ' More than 2 chilren
         strChildren = ""
         '/Comma seperate all the forenames
         Do Until Rs.EOF
              strChildren = StrChildren & Rs("Forename") & ", "
              Rs.MoveNext
         Loop
         '/Drop the last comma
         strChildren = Left(strChildren,Len(strChildren)-2)
         '/Next we need to replace the last comma with a &
         Dim iIndex as integer
         iIndex = InstrRev(strChildren,",")
         strChildren = Trim(Left(strChildren,iIndex-1) & " & " & Mid(strChildren,iIndex+1))
         strChildren = StrConv(strChildren,3)
End Select

Rs.Close

End If

Set Rs = Nothing

ParseForenames = strChildren

End Function

Obviously I have used my own names for tables and fields for brevity you will need to amend accordingly.

This baiscally get the list of children belonging to the parent and sort them by age (oldest first).

Then counts how many there are, just one, two only or more than two. For gramatical purposes only.

then depending on how mant it builds up the string of child names.

Finally it replaces the last comma with a & and ensures that each forename is capitalised.

HTH.
Let me know if it works

 

[sistemalan]

Oh my God DCrake, this is exactly the sort of help I need. I'm going to try and work out how to put it into action now. I'll let you know how I get on.

Thank you so much!

Alan