Spreading a Cost over Months

[kdaniel7979]

Earlier I posted a problem:

In my database I would like to have the following fields:
Cost
Start Date
End Date

I would like to be able to figure out how to spread out this cost
between the two dates and summerize these cost by months.


For example:
Input Information
A - Cost $30, Start Jan-06, End Mar-06
B - Cost $40, Start Feb-06, End Mar-06
C - Cost $10, Start Feb-06, End Mar-06
etc

Created Export table or Query:

Cost Start End Jan-06 Feb-06 Mar-06
A $30 Jan-06 Mar-06 $10 $10 $10
B $40 Feb-06 Mar-06 $20 $20
C $10 Feb-06 Mar-06 $5 $5
Sum $10 $35 $35

And Rob Oldfield was nice enough to recommend:
"The way that I would do it would be loop through each record of the
table,
then loop through each of the months that make up that record, and
append
each month to a temp table. Air code would be...

dim db as database
dim rsin as recordset
dim rsout as recordset
set db=currentdb
set rsin=db.openrecordset("SourceTable")
db.execute "delete from TargetTable"
set rsout=db.openrecordset("TargetTable")
rsin.movefirst
dim amount as double,months as integer, d as date, sd as date, i as
integer
do while not rsin.eof
sd=rsin("Start Date")
months=datediff("m",sd,rsin("End Date"))+1
amount=rsin("Cost")/(months+1)
d=dateserial(year(sd),month(sd),1)
for i=0 to months-1
rsout.addnew
rsout("Date")=dateadd("m",i,d)
rsout("Amount")=amount
rsout("ID")=rsin("ID") 'where ID is your PK field
rsout.update
next
rsin.movenext
loop


That'll give you a list broken down by month. To get what you want
after
that, if would just be a crosstab query (row headings would be the pk,
column headings the dates, summing amounts). You could then link that
back
to your source table if it contained any further information that you
wanted
to include.


Note that the reason to pick out the first of each month is so that the

grouping works easily. If you're just formatting as dates without the
day
showing later, then that doesn't matter.


I realise that, if you get that working, your next question is going to
be
how to create a report to show your data. I'd suggest worrying about
that
later."



However, this code is a little beyond me. Can someone give me some
recommendations or where to start with this?

Thx
Dan

 

[PC Datasheet]

Before you embark on this, consider that there are two problems you will
have to solve with this recommendation--
1. You can not just append data to a table each time you want to run the
report. After the first append, each succeeding append will create duplicate
data in the table.
2. Temp tables will cause your database to rapidly bloat in size.

 

[Rob Oldfield]

Basically, you just need to replace 'SourceTable' with the Table (or Query)
name where your source data comes from. In addition, set up a new (temp)
table which replaces 'TargetTable'. Fields in that, in my example, need to
be Date (date), Amount (numeric) and ID (same format as ID on your source
table). One slight error in my air code: you shouldn't really name fields
Date - it can cause problems. Probably best to change to PayDate or
whatever.

And then just run the code: either by dropping it into the code behind a
button, or into a module function and calling that.

Note that Steve's concerns are caused by his not noticing the line:
db.execute "delete from TargetTable" which removes all old entries from the
temp table.

I'm going to be away from my usual newsgroup access points for a few days
from now, but if you have any problems then I'll do my best to keep an eye
on this thread. Apologies if you again get caught up flame wars surrounding
Steve - it's really nothing that you're doing wrong.

 

[kdaniel7979]

Thanks Rob. I'll let you know how I make out.

Cheers

 

[kdaniel7979]

I set up a sample DB and got an error on line 5 "Set db = CurrentDb"
The error in the debug was "Invalid outside procedure".

I'm not sure if I set this up correctly. You can send me an email to
(e-mail address removed) If you want me to send you the DB to take a look
at.

Cheers

Dan