Write query-dates of birth and wed anniversaries in the same colum

[Spudlover]

I have a table with First Name, Last Name, BIRTH DA, Spouse and WED DA (among
other things). I have been able to create two queries, one gives who has a
BIRTH DA on every day in the year, the other gives which couples have a WED
DA on every day of the year. Using medium date. Access 2000.

I would like a query (and eventually a report) that lists all of the days
in the year in one column. In the second column I would like everyone that
has either a birth date and/or wedding date.
Example:
January 2 Mary Jones
January 3 Tom & Mary Smith
February 14 Dave & Lisa Green
Sam Thompson
February 21 Betty Moore

Can this be done? Can someone help write the codes or expressions that are
required?

Thanks for anyone who can offer assitance. I have been thinking about this
on and off for two years, and can't find anyone to help.

 

[Van T. Dinh]

Check Access Help on Union Queries.

 

[John Vinson]

I have a table with First Name, Last Name, BIRTH DA, Spouse and WED DA (among
other things). I have been able to create two queries, one gives who has a
BIRTH DA on every day in the year, the other gives which couples have a WED
DA on every day of the year. Using medium date. Access 2000.

I would like a query (and eventually a report) that lists all of the days
in the year in one column. In the second column I would like everyone that
has either a birth date and/or wedding date.
Example:
January 2 Mary Jones
January 3 Tom & Mary Smith
February 14 Dave & Lisa Green
Sam Thompson
February 21 Betty Moore

Can this be done? Can someone help write the codes or expressions that are
required?

Thanks for anyone who can offer assitance. I have been thinking about this
on and off for two years, and can't find anyone to help.

A UNION query will do the trick here. You need to do this in the SQL
window, the query grid can't handle it. Try creating a new query;
select the table; and select SQL from the leftmost tool in the
toolbar. Edit the query to

SELECT [Birth Da] AS EDate, [First Name] & " " & [Last Name] AS Who
FROM yourtable WHERE [Birth Da] IS NOT NULL
UNION ALL
SELECT [WED DA], [First Name] & " & " & [Spouse] & " " & [Last Name]
FROM yourtable WHERE [Wed Da] IS NOT NULL
ORDER BY EDate;

Not sure how (or if) you're recording the birthdates of spouses - or
if you'll have each person in the table twice, once in the [First
Name] field and once as [Spouse] (not to mention couples where the two
keep different last names)... but the UNION query is the key.

John W. Vinson[MVP]

 

[Spudlover]

Thank you. We are getting close to what I need.
1. The query is sorted by year of birth, how do I rewrite the code to sort
chronologically Jannuary 27, followed by January 28, followed by February 1?
Year does not matter. I have not worked with SQL before and don't know
where to find the properties.

2. My table is medium date - 10 Dec 1912. For some reason this query
shows short date - 12 25 1981. How do I change the query to medium date that
I prefer?

3. You are right, it did list all couples having an anniversary twice.
Ian & Julie Jones followed by Julie & Ian Jones both with the same date. How
do I correct this?

4. I can't think of anyinstance in the database where we have any couples
with different last names, but that would present a problem.

4. What does EDate stand for? Sorry, I'm really new at this.
Thanks again for your assistance.

I have a table with First Name, Last Name, BIRTH DA, Spouse and WED DA (among
other things). I have been able to create two queries, one gives who has a
BIRTH DA on every day in the year, the other gives which couples have a WED
DA on every day of the year. Using medium date. Access 2000.

I would like a query (and eventually a report) that lists all of the days
in the year in one column. In the second column I would like everyone that
has either a birth date and/or wedding date.
Example:
January 2 Mary Jones
January 3 Tom & Mary Smith
February 14 Dave & Lisa Green
Sam Thompson
February 21 Betty Moore

Can this be done? Can someone help write the codes or expressions that are
required?

Thanks for anyone who can offer assitance. I have been thinking about this
on and off for two years, and can't find anyone to help.

A UNION query will do the trick here. You need to do this in the SQL
window, the query grid can't handle it. Try creating a new query;
select the table; and select SQL from the leftmost tool in the
toolbar. Edit the query to

SELECT [Birth Da] AS EDate, [First Name] & " " & [Last Name] AS Who
FROM yourtable WHERE [Birth Da] IS NOT NULL
UNION ALL
SELECT [WED DA], [First Name] & " & " & [Spouse] & " " & [Last Name]
FROM yourtable WHERE [Wed Da] IS NOT NULL
ORDER BY EDate;

Not sure how (or if) you're recording the birthdates of spouses - or
if you'll have each person in the table twice, once in the [First
Name] field and once as [Spouse] (not to mention couples where the two
keep different last names)... but the UNION query is the key.

John W. Vinson[MVP]