I know I've asked this question before, and I believe I'm about 75% there on getting what I need, but I'm lost as to how to get the other 25% to work out.

I have a club. Members have an "Initiation" date and can then have any number of "Out" dates for suspensions, demits, and "In" dates where they were reinstated or restored. So far, I have been able to get my queries to calculate total time "In" and total time "Out" with the help of others in this forum (thank you all again).

Given that a member has an initiation date, we can project a 25, 50 and 75 year anniversary date, as this is a simple calculation using DateAdd.

What I need to have is a way for the query to look at which anniversary date a period of inactivity applies to, i.e. before their 25 year anniversary, after the 25 but before the 50, etc.

InitDate: 1/1/1950
Suspend1: 12/31/1976
Restore1: 6/1/1977

This member has inactivity, but it wouldn't affect his 25 year anniversary, but would affect his 50 and 75 year. Using what I have been told (use the DateAdd function and add the total days to the end of the anniversary calculation) it applies the total of inactive time to each anniversary calculation regardless of when the inactivity occured.

Is there a way to check the date range and have it apply only to the anniversary it shares dates with?

I realize this probably all sounds like a jumble, but I'd be happy to explain further if needed.

Bluez

Hi

I did a vaguely similar thing a while back but can't find it to remind me so I'll try and explain.

As the 'suspended' periods can be any number, I created a table just for that with the following fields.
ID_No (to link to main people register)
StartDate (will be your InitDate)
SuspStart (will be suspend startDate)
SuspEnd (will be suspend end date)
RestartDate (will be your restore date usually its SuspEnd + 1)

Then I added calculated fields to calculate the time between SuspStart and SuspEnd (I put the startDate and RestartDate in just in case I needed them)

Then I can calculate the totalTime (which is StartDate to Now() - suspendedTime giving me the actual 'active' time. Then you can add some VB function along the lines of
If activeTime >25 and activeTime < 50 then
Years = 25+ etc etc.

Just read through this, hope it's ok
Col

I'll try and do what you're saying, by creating a play table with only those fields in it, and see if I can get it to work. I guess I'm still not clear on how it will find the appropriate anniversary to add days to without first checking to see if it's in the right date range, but I'll keep plugging away at it.

Thanks for your help!

Bluez

I got the impression that the anniversary date was the [StartDate] to [AnnivDate] minus any periods of suspension. Therefore if [StartDate] was 20/03/1950, the 25yr anniv was on 20/03/1975 (provided there was no suspensions) if there is suspensions of 3 yrs,then the 25yr anniv would be 20/03/1978 and so on. Have I got the wrong end of the stick?

Col