How do I sort a report on date fields from two tables?

[Joseph Ellis]

Hello all,

I have two tables in a church directory database. One table has
anniversary dates, the other has birthdates:

[couples!anniversary]
[individuals!birthday]

I'd like to make a report that lists all anniversaries and birthdays
in one list, in chronological order:

...
April 18th: Jim's Birthday
Bob and Mary's Anniversary
April 30th: Jane's Birthday
May 5th: Bill's Birthday
May 15th: Dick and Jane's Anniversary
...

Is there a way to do this?

Thanks for any help.
Joseph

 

[Steve Schapel]

Joseph,

The short answer is that you should be able to make a Union Query which
combines the birthday and name from the person table with the
anniversary and names from the couple table. And then you can use this
query as the record source of your report.

 

[Joseph Ellis]

Joseph,

The short answer is that you should be able to make a Union Query which
combines the birthday and name from the person table with the
anniversary and names from the couple table. And then you can use this
query as the record source of your report.

Thankee kindly, sir. That's what I needed. I guess I need to get a
book or something to help me learn Access, instead of plodding through
the help system. I had never heard of a union query before.

Just FYI, here's what I've got so far:


select [hh first name] as [first name], [hh last name] as [last name],
format([anniversary], "mmmm d \(\A\n\n\i\v\e\r\s\a\r\y\)") as [date]
from [households] where ([in directory?] = true) and ([retired?] =
False) and ([anniversary] <> null)

union select [first name], [last name], format([birthday], "mmmm d")
as [date] from [individuals] where ([retired?] = False) and
([birthday] <> null)

order by [date];


Any suggestions for improvement? Particularly, is there a more
graceful way to append " (Anniversary)" to the anniversary date than
what I've done above?

Thanks again for the help.
Joseph

 

[Steve Schapel]

Joseph,

A few comments...
Yes, it is important to have a good book in your toolbox.
It is not a good idea to use a ? as part of the name of a field or control.
It is not a good idea to use Date as the name of a field or control, it
is a 'reserved word' (has a special meaning)
Try this...
SELECT [first name], [last name], Format([birthday], "mmmm d") AS
[SpecialDate]
FROM [individuals]
WHERE [retired]=0 AND [birthday] Is Not Null
UNION SELECT [hh first name], [hh last name], Format([anniversary],
"mmmm d") & " (Anniversary)" as [SpecialDate]
FROM [households]
WHERE [in directory]=-1 AND [retired]=0 AND [anniversary] Is Not Null
ORDER BY [SpecialDate]