Union Query

  • Thread starter Thread starter Andie
  • Start date Start date
A

Andie

I have inherited a badly designed data base that has as
part the following fields: Name of Child 1,Date of Birth
child 1, Name of Child 2,Data of Birth Child 2, Name of
Child 3, Date of Birth Child 3 all stored in one table.
Could some please tell me the SQL code required to
combine these fields into one field in the Query so that
I can extract specific Age groups


Thank You
 
Try a Union query such as:

SELECT tblBadDesign.Name1, tblBadDesign.DOB1
FROM tblBadDesign
UNION
SELECT tblBadDesign.Name2, tblBadDesign.DOB2
FROM tblBadDesign
UNION
SELECT tblBadDesign.Name3, tblBadDesign.DOB3
FROM tblBadDesign;

This will give a blank row if any record has no entries for one or more
children. If you want to eliminate that, you'll need something like

SELECT tblBadDesign.Name1, tblBadDesign.DOB1
FROM tblBadDesign WHERE tblBadDesign.Name1 Is Not Null
UNION
SELECT tblBadDesign.Name2, tblBadDesign.DOB2
FROM tblBadDesign WHERE tblBadDesign.Name2 Is Not Null
UNION
SELECT tblBadDesign.Name3, tblBadDesign.DOB3
FROM tblBadDesign WHERE tblBadDesign.Name3 Is Not Null;

Both these queries will not show duplicates where the name and dob in
different records both match.

Rob
 
Back
Top