Birthday query for many children in one record

[claire]

I have a database for the church, it has one main flat table only.

Each record for the parents have field for child1 name,
child1birthday, child2 name, child2birthday, child3 name,
child3birthday - up to 5 children.

I am having difficulty trying to get a report to list the birthdays of
the children like this

parent name, child 1 name, child 1 birthday month etc. so you get
something like this:-

Parent John Smith, child Mary Smith January 3
Parent Fred Jones, child Bert Jones March 4
Parent John Smith, child Jane Smith March 6
Parent Harry Brown, child Jane Brown March 6
Parent Bert Taylor, child Anne Taylor December 18
Parent Fred Jones, child Sue Jones December 21

This will enable the children to get a birthday card from the church.

I have tried putting the childrens details on separate tables and also
setting up a query for each child and then running a new query for the
children using these expressions:-

Expr1: Month([baptism_an1])
Expr2: Day([baptism_an1])

but cannot seem to get the right data out. Is there a way I can do
this?

I have looked at the listings here but cannot seem to find the answer.
Any help would be much appreciated.

 

[Guest]

Claire,

Given your table structure (you really need to normalize this so that you
have a Children table that map to parents), I would recommend you start with
a Union Query that would look something like:

Select [Parent Name], [Child1 Name] as [Child_Name], [Child1Birthday] as DOB
FROM yourTable
WHERE [Child1 Name] is NOT NULL
UNION aLL
Select [Parent Name], [Child2 Name] as [Child_Name], [Child2Birthday] as DOB
FROM yourTable
WHERE [Child2 Name] is NOT NULL
UNION aLL
Select [Parent Name], [Child3 Name] as [Child_Name], [Child3Birthday] as DOB
FROM yourTable
WHERE [Child3 Name] is NOT NULL
UNION aLL
Select [Parent Name], [Child4 Name] as [Child_Name], [Child4Birthday] as DOB
FROM yourTable
WHERE [Child4 Name] is NOT NULL
UNION aLL
Select [Parent Name], [Child5 Name] as [Child_Name], [Child5Birthday] as DOB
FROM yourTable
WHERE [Child5 Name] is NOT NULL

Save this as qry_ChildDOBs

Create a new query based on qry_ChildDOBs that looks something like:

Select [Parent Name], [Child_Name], Format([Dob], "mmm dd")
FROM qry_ChildDOBs
Order by Format([DOB], "mmdd"), [Parent Name]

HTH
Dale