Results 1 to 7 of 7
  1. #1
    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).

    I hope that makes sense, can anyone please help me I need this report and I am so past deadline!

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

  2. #2
    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. #3
    Join Date
    Mar 2004
    Posts
    56
    edited, any better?

  4. #4
    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. #5
    Join Date
    Apr 2004
    Location
    Reading, UK
    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. #6
    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. #7
    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
  •