Birthday & Anniversary Queries

If you sort by the birthday or anniversary month/days as separate columns one will have to take precedence over the other of course.  A better way might possibly be to return both as a single column.  That way if someone has a birthday in February say, but an anniversary in September the query will return two rows for that member, one sorted amongst the February dates, the other amongst the September dates.  You can do this by means of a UNION ALL operation, e.g.

SELECT FirstName, LastName, EmailAddress,

"Birthday" AS AnniversaryType,

FORMAT(BirthDate,"dd mmmm") AS AnniversaryDate,

FORMAT(BirthDate,"mmdd") AS SortColumn

FROM Members

WHERE BirthDate IS NOT NULL

UNION ALL

SELECT FirstName, LastName, EmailAddress,

"Anniversary", FORMAT(MarriageDate,"dd mmmm"),

FORMAT(MarriageDate,"mmdd")

FROM Members

WHERE MarriageDate IS NOT NULL

ORDER BY SortColumn;

As Karl said, you don't need to create any calculated fields in the table.  You can't create a UNION query visually in query design view, only in SQL view, so open the query designer as if designing a new query, switch to SQL view and paste in the above SQL statement in place of what's there.  Then edit it, changing the table and column names to the actual names of your table and columns.  Remember that table or column names which include spaces or other special characters must be wrapped in square brackets, e.g. [Marriage Date].  Note that the string expressions "Birthday" and "Anniversary" as the AnniversaryType column are constants, so don't change these.

I created 4 new calculated fields in my table like you said and then was able to add them to my query instead of the actual Date/Time field of the occasion.

The calculated fields are not to be in the table but in your query.  Your table to have 2 DateTime fields for birthday and wedding.

Thank you!

I'm not sure if I interpretted this correctly but I got the result I was looking for.

I created 4 new calculated fields in my table like you said and then was able to add them to my query instead of the actual Date/Time field of the occasion.

After that, I was able to sort them in ascending order by month, then day.

PERFECT!!! Happy dance! This is why I switched our database to access! Four years of frustration is over:)

In your query add a column as Month([DateField]).  This will all grouping by the month.

Month(#10/11/2012#) returns 10.

You can also use Format.

 Format(#10/11/2012#,"m") returns 10.

Format(#10/11/2012#,"mmm") returns Oct.

Format(#10/11/2012#,"mmmm") returns October.

Jack