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!
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.