Birthday query for many children in one record

  • Thread starter Thread starter claire
  • Start date Start date
C

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.
 
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
 
Back
Top