Allocating Costs to the Proper Month

[BroomerEd]

I have a table of job contracts that show a start date, an end date and a cost. Invariably, these project span more that one accounting period. I have a file that includes a record for each accounting month that shows the start date, the end date and the number of days in each period.

What I can't accomplish is to allocate the proper cost to each accounting month.

eg
Job Contract 1 costs $6,200, start date is Oct 15 and end date is Dec 15. The accounting month start date for Oct is Oct 1 and the end date is Nov 1, for Nov, start date is Nov 1 and end date is Nov 29 and for Dec, start date is Nov 30 and end date is Jan 3, 09.


The report should look like this:

JC # Oct Nov Dec(Acct'g mths not calendar)
1 $1,800 $2,800 $1,600


Can anyone help?

 

[2Grandpa]

You will need to use code to calculate this. We will need some additional information.
1) Are the acounting periods the same each year? If so we can hard code them in else we should set up a table for them.
2) Need to know the starting date and ending date for each accounting period. There cannot be an overlaping period. In your inital post you stated that 1 Nov was the end date for Oct but also the start date for Nov. This cannot be.
3) Is the data prorated by the number of days open during a accounting period?

 

[Remou]

I am a little confused that a period starts on the same day as another period ends, however, you might try something like:

SELECT IIf(a.startDate<=ap.StartDate 
             And a.enddate>=ap.enddate,
               DateDiff("d",ap.startdate,ap.enddate),
             IIf(a.Startdate>=ap.Startdate,
               DateDiff("d",a.Startdate,ap.EndDate),
             IIf(a.enddate>=ap.Startdate,
               DateDiff("d",ap.Startdate,a.EndDate),0))) AS Days, 
(Amount/DateDiff("d",a.startdate,a.enddate))*(Days+1) AS Expr2
FROM Accounts AS a, AccountPeriods AS ap
WHERE (((ap.StartDate) Between a.Startdate And a.EndDate))
OR (((ap.EndDate) Between a.Startdate And a.EndDate));

 

[PWise]

create a numbers table with 1 field
digitid
1
2
3
.....
1000000


Select sum(costperday),piriodname from(
Select datediff("d",pstart,pend)/cost as costperday ,amonth.piriodname
from projects,numbers
inner join accountingmonth as amonth
on dateadd("d",digidid-1,projectstart) between Amonth.Start and Amonth.end
and dateadd("d",digidid-1,projectstart)<=projectend)dt
group by piriodname

 

[BroomerEd]

Thanks for your responses, I've been away over the holidays and haven't had a chance to try any of the solutions but hopefully I have clarified my problem below.

The accounting periods don't overlap and each year will have a different start date and end date for accounting month. I do have a table that I can revise the dates for each period.

Is the data prorated by the number of days open during a accounting period?

Answer: Yes, the cost for each accounting month should be based on the number of days in the month that the job contract is open.