Union Report

[ritland]

I am trying to create a birthday list from a database that lists both
the husband and wife on the same row. I cannot modify this
arrangement. It looks like this:

ID FirstName LastName DOB SpouseFirstName SpouseDOB
1 Tom Doe 8/9/1980 Amy 5/6/1978

How can I create a report in Access that will seperate the Husband and
Wife into seperate rows and then list everyone by birth month then
birth day? Any thoughts?

Thanks so much.

 

[KARL DEWEY]

Try this --
SELECT RITLAND.FirstName, RITLAND.LastName, RITLAND.DOB
FROM RITLAND
UNION ALL SELECT RITLAND.SpouseFirstName AS FirstName, RITLAND.LastName,
RITLAND.SpouseDOB AS DOB
FROM RITLAND;

 

[John Spencer]

Use a UNION query as the starting point
SELECT FirstName, LastName
, Month(DOB) as MonthNum
, Day(Dob) as DayNum
FROM TheTable
UNION ALL
SELECT SpouseFirstName, LastName
, Month(SpouseDOB) as MonthNum
, Day(SpouseDob) as DayNum
FROM TheTable

Optionally add the following order by to the end if you want exactly the
results you asked for.

ORDER BY MonthNum, DayNum, LastName, FirstName

Save that query and use it as the source if you want to limit the report to
certain months or any other things

SELECT FirstName, Lastname,
,MonthNum, DayNum
FROM theUnionQuery
Order by MonthNum, DayNum

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[ritland]

Use a UNION query as the starting point
SELECT FirstName, LastName
, Month(DOB) as MonthNum
, Day(Dob) as DayNum
FROM TheTable
UNION ALL
SELECT SpouseFirstName, LastName
, Month(SpouseDOB) as MonthNum
, Day(SpouseDob) as DayNum
FROM TheTable

Optionally add the following order by to the end if you want exactly the
results you asked for.

ORDER BY MonthNum, DayNum, LastName, FirstName

Save that query and use it as the source if you want to limit the report to
certain months or any other things

SELECT FirstName, Lastname,
,MonthNum, DayNum
FROM theUnionQuery
Order by MonthNum, DayNum

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.









- Show quoted text -

Thanks! It works perfectly. One quick question, how can I filter out
blank or null entries? Not every person in the database has thier
birthday information inserted.

 

[John Spencer]

Change the UNION Query to the following

SELECT FirstName, LastName
, Month(DOB) as MonthNum
, Day(Dob) as DayNum
FROM TheTable
WHERE DOB is not null
UNION ALL
SELECT SpouseFirstName, LastName
, Month(SpouseDOB) as MonthNum
, Day(SpouseDob) as DayNum
FROM TheTable
WHERE SpouseDOB is Not Null

Or use the union query as a starting point and a where clause to
eliminate nulls

SELECT *
FROM theSavedUnionQuery
WHERE MonthNum is Null


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================