Warren, you could create a UNION query to get all the birthdates into a
single column.
Prepare a query that gets you the fields you want just for the father's
birthday. Switch it to SQL View (View menu), and replace the trailing
semicolon with:
UNION ALL
Then add the whole SQL statement again to get the mother's birthday.
Repeat this process for each child.
In the end, your SQL statement will look something like this:
SELECT FamilyID,
Month([Birthdate Father]) As BMonth,
Day([Birthdate Father) As BDay,
"Father" As Person
FROM Table1
UNION ALL
SELECT FamilyID,
Month([Birthdate Mother]) As BMonth,
Day([Birthdate Mother) As BDay,
"Mother" As Person
FROM Table1
UNION ALL
SELECT FamilyID,
Month([Birthdate Child 1]) As BMonth,
Day([Birthdate Child 1) As BDay,
"Child1" As Person
FROM Table1
UNION ...
.... and so on.
You can then use this query for a report that lists everyone's birthdays by
BMonth and BDay (i.e. in the order the occur throughout the year.)
A better solution would be to create a record for the family, and with a
related record for each person in the family. The Person table would have
these fields:
PersonID AutoNumber primary key
FamilyID relates to the primary key of the Family table.
RoleID "Father", or "Mother", or "Child"
BirthDate Date/Time
You can now create the report really easily, because there is only one field
where everyone's birthdate is to be found.
That simple structure does not cope with people who belong to more than one
family (e.g. children who spend half the week with Dad, and half with Mum.)
If you need to handle that as well, you can download a more advanced example
explained in this article:
People in households and companies - Modelling human relationships
at:
http://allenbrowne.com/AppHuman.html
HTH.
