# Thread: Desperate with Contract Dates!!

1. Registered User
Join Date
Mar 2004
Posts
56

## Unanswered: Desperate with Contract Dates!!

I am desperate and I hope I can explain this proper, I am late with a report and stuck on how to get access 2003 to do what I need. I hope you all can help.

I have 2 date fields, Start Date and Expiration date. These dates are relative to when a contract was signed and when it expires. This DB is 15,000 records large. There is also a field called “weeks active” this is derived from a datediff formula between “start date” and “expire date” (to calculate number of weeks between the 2). 98% of contracts run 52 weeks, but there are some anomalies (over 52 weeks), wherein lays my problem. Because the “weeks active” field plays a role in calculating weekly and annual revenue (\$*weeks) anything higher than say 52 weeks is going to mess up my numbers for the year of 2003 breakdown.

Up to now I have been using a querry (start date: <=#5/31/2003#)
And (expire date >=#5/31/2003#) but obviously this isn’t working as this will bring into the equation contracts that are “weeks active” over 52 weeks. I still need to include the over 52 week contracts, but only take 52 weeks out of say 104 weeks of the total (say for example out of the 104 weeks I only need 52 of those weeks in my equation as any more than that would go over a one year summary of 2003).

Thank you
Last edited by roma92; 06-30-04 at 08:42.

2. Registered User
Join Date
Jan 2004
Location
The Netherlands
Posts
421
I for one do not understand your problem...

Maybe you can retry explaining and/or give a detailed example of what your trying to do....

Greetz

The Mailman

3. Registered User
Join Date
Mar 2004
Posts
56
edited, any better?

4. Registered User
Join Date
Mar 2004
Posts
56
Or maybe in other words, for those contracts that say are 104 weeks, I still need to calculate their 52 week portion, as currently if I was to calculate the 104 weeks ([field]*[weeks active]=annual revenue) it would report more revenue/improper revenue. I only need 52 out of the 104 weeks.

5. Registered User
Join Date
Apr 2004
Location
Posts
16
You've probably done this by now but can't you just use a calculated field in your query then only returns a maximum of 52 and a minimum of 0?

CALCFIELD: iif(datediff("ww",[StartDate],[ExpDate])>52,52,datediff("ww",[StartDate],[ExpDate]))

Sean

6. Registered User
Join Date
Jan 2004
Location
The Netherlands
Posts
421
You shouldnt "mess" with your original post.... once someone has posted something below....

What i am getting is this,
Say you have a startweek 50 - 2002 and endweek 52-2003 then you have 54 weeks.
You would only want 52/54*Revenue for the 2003

Likewise, startweek 45 - 2003, endweek 13 -2004, 20 weeks
You want 7/20* revenue for 2003 and (next year) 13/20 * Revenue for 2004

Correct?

7. Registered User
Join Date
Jun 2004
Location
Saratoga Springs
Posts
24
How do you want to calculate yearly revenue for the +52 week records?

For example, do you want the revenue calculation to be a straight percentage?

yearly_revenue = total_revenue_amount/(weeks) * 52

To use this calculation, you have to assume equal revenue for each and every week.

hope this helps

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•