List of Birthdays, Wedding Anniversaries and Membership Anniversaries 1

[THWatson]

Using Access 2000

In a club's Membership database there is a table, tblMembers. Included in the fields are MemberID, LastName, FirstName, DateOfBirth, WeddingAnniversary, DateJoined.

The club's fiscal year is from October 1 through September 30.

They desire a list of members showing as follows...
1. Sorted chronologically from October through September
2. Birth month and birth day for members in each month
3. Wedding Anniversary month and day and # of years married in each month
4. Club membership Anniversary and # years in club in each month

Included in a query to produce the results for the report, are columns...
Sept30Age: DateDiff("yyyy",[DateOfBirth],DateSerial(Year(Date()),9,30))
Sept30WeddingAnniversary: DateDiff("yyyy",[WeddingAnniversary],DateSerial(Year(Date()),9,30))
Sept30KiwanisAnniversary: DateDiff("yyyy",[YearJoined],DateSerial(Year(Date()),9,30))

My questions are...
1. Is this on the right track to produce the desired results?
2. How to get this sorted properly in order to produce a report that corresponds chronologically to the fiscal year, October 1 through September 30?

Thanks.

Tom

 

[Remou]

You could use something on the lines of:

SortBy: Val(Choose(Month([TheDate]),4,5,6,7,8,9,10,11,12,1,2,3))

MichaelRed has some date of birth coding that should work very well with any anniversary dates:

http://www.tek-tips.com/viewthread.cfm?qid=1520232

http://lessthandot.com

 

[THWatson]

Remou
Thanks for your reply.

In your SortBy: Val(Choose(Month([TheDate]),4,5,6,7,8,9,10,11,12,1,2,3)) I'm not clear what TheDate refers to.

I will take a look at Michael Red's suggested approach.

Tom

 

[dhookom]

Tom,
When you state "desire a list of members" it suggests a single list. Is this true or do you want 3 separate reports?

If you use "yyyy" with DateDiff(), you get the number of year changes between 2 dates. For instance from Christmas of 2008 to today would be 1 year.

I am also confused because you have used both YearJoined and DateJoined. Is this a typo? Do you actually store the date or just the year? If you only store the year, how would you know if it was before or after the start of your year.

Duane
Hook'D on Access
MS Access MVP

 

[THWatson]

Duane
1. Single list is what the club would like. (another way, I suppose, would be to provide a main report with 2 subreports)

2. Yes, I know and am using the DateDiff() function.

3. Good catch. My bad. The field is YearJoined, although it would more appropriately be named DateJoined as it is a full date. (there is no DateJoined field)

Tom

 

[dhookom]

Since it is one report then I would first create a union query to normalize your table:

SELECT MemberID, LastName, FirstName, DateOfBirth as TheDate, "DOB" as DateType
FROM tblMembers
UNION ALL
SELECT MemberID, LastName, FirstName, WeddingAnniversary, "Ann"
FROM tblMembers
UNION ALL
SELECT MemberID, LastName, FirstName, YearJoined, "Join"
FROM tblMembers;

You can then use this query as the base to calculate the YearsOf, month, etc.

Your report can then sort/group by whatever you need.


Duane
Hook'D on Access
MS Access MVP

 

[THWatson]

Duane
I was just working through a process using the same approach.

Thanks!!

Tom

 

[THWatson]

Duane
I had to add in some additional fields, and WHERE clauses.

There are 20 members for whom there is no Wedding Anniversary date (either because they are not married, or the data has not been entered.

The query returns 133 complete rows, plus 20 extra blank "Wedding Anniversary" (what you called "Ann" in your example SQL) rows.

Here is the union query's SQL.

SELECT MemberID, LastName, FirstName, DateOfBirth as TheDate, "Date Of Birth" as DateType,Status,DateDiff("yyyy",[DateOfBirth],Date())+Int(Format(Date(),"mmdd")<Format([DateOfBirth],"mmdd")) As FixYears,Month(TheDate) As Month,Day([DateOfBirth]) as Day
FROM tblMembers
WHERE tblMembers.Status="Active" Or tblMembers.Status="Senior"
UNION ALL
SELECT MemberID, LastName, FirstName, WeddingAnniversary, "Wedding Anniversary",Status,DateDiff("yyyy",[WeddingAnniversary],Date())+Int(Format(Date(),"mmdd")<Format([WeddingAnniversary],"mmdd")) AS FixWeddingAnniversaryYears,Month(WeddingAnniversary) As Month,Day([WeddingAnniversary]) as Day
FROM tblMembers
WHERE  tblMembers.Status="Active" Or tblMembers.Status="Senior"
UNION ALL SELECT MemberID, LastName, FirstName, YearJoined, "Kiwanis Anniversary",Status,DateDiff("yyyy",[YearJoined],DateSerial(Year(Date()),9,30)) AS Sept30KiwanisAnniversary,Month(YearJoined) as Month,Day([YearJoined]) as Day
FROM tblMembers
WHERE tblMembers.Status="Active" Or tblMembers.Status="Senior"
ORDER BY Month, Day, DateType, LastName, FirstName;

Is there a way to fix this?

Tom

 

[dhookom]

What do you want to fix? Do you want to filter out the records with no anniversary? If so, just add to the where clause of that section of the union

WHERE  (tblMembers.Status="Active" Or tblMembers.Status="Senior") and [WeddingAnniversary] is not null

Duane
Hook'D on Access
MS Access MVP

 

[THWatson]

Duane
I had done that...but missed a bracket.

Too early in the new year, I guess.

Thanks again.

Tom