Spreading a cost over time

[kdaniel]

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

Record 1 $100,000 1/1/06 to 31/10/06 Which should spread out as
$10,000 per month.
Record 2 $60,000 1/1/06 to 31/3/06 Which would $20,000 / Month

Therefore in a report I would like to see the sumarries of each month:

Jan $30,000
Feb $30,000
Mar $30,000
Apr $10,000
Jun $10,000
etc, etc,

Does anyone have any ideas?

Thanks

Dan

 

[Guest]

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

Record 1 $100,000 1/1/06 to 31/10/06 Which should spread out as
$10,000 per month.
Record 2 $60,000 1/1/06 to 31/3/06 Which would $20,000 / Month

Therefore in a report I would like to see the sumarries of each month:

Jan $30,000
Feb $30,000
Mar $30,000
Apr $10,000
Jun $10,000
etc, etc,

Does anyone have any ideas?

Thanks

Dan

 

[kdaniel7979]

Bueller? ... Bueller? ...... Bueller?

Any one out there have some ideas?

Thx

 

[PC Datasheet]

Look at the DateDiff function in the Help file. DateDiff will give you the
number of monthe between two dates. In your example you can then divide the
$100,000 and the $60,000 by 10 and 3 respectively to get the monthly cost.

 

[kdaniel7979]

OK thx. I will start there.

 

[kdaniel7979]

I'm trying to get a table / report to look like this (below) based on
the input of 3 variables (Cost; Start Date; End Date) I have started
with diffdate which is helpful. But getting this information into
Jan-06, Feb-06 fields seems overwhelming. May start date and End date
may be 4-5 years apart. Am I overlooking an easier way to extrapolate
this information?

Thanks



Input variables
Cost Start End Jan-06 Feb-06 Mar-06 Apr-06 May-06 Jun-06
A $30 Jan-06 Mar-06 $10 $10 $10
B $80 Feb-06 May-06 $20 $20 $20 $20
C $10 Mar-06 Apr-06 $5 $5
D $30 May-06 Jun-06 $15 $15
Sum $10 $30 $35 $25 $35 $15


PS I have a rough idea how to do this in Excel, but I want to stay in
Access

 

[StopThisAdvertising]

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
Over 1125 users have come to me from the newsgroups requesting help
(e-mail address removed)

--
To Steve:
Over 375 users from the newsgroups have visited the website to read what kind of a 'resource' you are...

To the original poster:

Most people here have a common belief that the newsgroups are for *free exchange of information*.
But Steve is a notorious job hunter in these groups, always trying to sell his services.

Before you intend to do business with him look at:
http://home.tiscali.nl/arracom/whoissteve.html

Arno R

 

[PC Datasheet]

You can adapt the procedure below my signature line to do what you want ---

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
Over 1125 users have come to me from the newsgroups requesting help
(e-mail address removed)

1. Create a table named TblNumbers with one field named Num and populate
the table with 1 to 31
2. Create a query based on TblNumbers
3. Pull down Num into the first field of the query.
4. Put the following expression in the criteria for Num:
<=Day(DateSerial(6,1+1,0))
5. Put the following expression in the second field of the query:
MonthDate: DateSerial(6,1,[Num])
6. Put the following expression in the third field of the query:
=Day([MonthDate])
7. Put the following expression in the fourth field of the query:
=Format([MonthDate],"ddd")
8. Put the following expression in the fifth field of the query:
= Format([MonthDate],"dddd")
9. Run the query.

The query will return all the dates for this month (January 06) in the
second column. The third column will contain the day of the month for each
date. The fourth column will contain the three letter abbreviation for the
day of the week for each date. The fifth column will contain the full name
of the day of the week for each date.

In the DateSerial expression, 6 is the year (06) and 1 is the number of the
month. You can make the query return the dates for any year and month by
modifying the DateSerial function to make the 6 and 1 selectable on a form.

 

[Rob Oldfield]

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.

 

[Rob Oldfield]

As my old grandma used to say: "If you're proud of some code, then never be
hesitant about posting it. And ignore the fact that it has nothing to do
with the question." Sadly, that was just before we had to send her to the
home.

You can adapt the procedure below my signature line to do what you want ---

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
Over 1125 users have come to me from the newsgroups requesting help
(e-mail address removed)

1. Create a table named TblNumbers with one field named Num and populate
the table with 1 to 31
2. Create a query based on TblNumbers
3. Pull down Num into the first field of the query.
4. Put the following expression in the criteria for Num:
<=Day(DateSerial(6,1+1,0))
5. Put the following expression in the second field of the query:
MonthDate: DateSerial(6,1,[Num])
6. Put the following expression in the third field of the query:
=Day([MonthDate])
7. Put the following expression in the fourth field of the query:
=Format([MonthDate],"ddd")
8. Put the following expression in the fifth field of the query:
= Format([MonthDate],"dddd")
9. Run the query.

The query will return all the dates for this month (January 06) in the
second column. The third column will contain the day of the month for each
date. The fourth column will contain the three letter abbreviation for the
day of the week for each date. The fifth column will contain the full name
of the day of the week for each date.

In the DateSerial expression, 6 is the year (06) and 1 is the number of the
month. You can make the query return the dates for any year and month by
modifying the DateSerial function to make the 6 and 1 selectable on a form.

I'm trying to get a table / report to look like this (below) based on
the input of 3 variables (Cost; Start Date; End Date) I have started
with diffdate which is helpful. But getting this information into
Jan-06, Feb-06 fields seems overwhelming. May start date and End date
may be 4-5 years apart. Am I overlooking an easier way to extrapolate
this information?

Thanks



Input variables
Cost Start End Jan-06 Feb-06 Mar-06 Apr-06 May-06 Jun-06
A $30 Jan-06 Mar-06 $10 $10 $10
B $80 Feb-06 May-06 $20 $20 $20 $20
C $10 Mar-06 Apr-06 $5 $5
D $30 May-06 Jun-06 $15 $15
Sum $10 $30 $35 $25 $35 $15


PS I have a rough idea how to do this in Excel, but I want to stay in
Access

 

[kdaniel7979]

OK Thanks

I have a two good solutions.

 

[kdaniel7979]

I agree with the philosophy of posting / exchanging information for
free. And as such, some information may be not accurate, overly
complicated, etc. However, in defense of "Steve", he/she has possibly
provided a solution for me and did not solicit me for any services, and
I thank them for it.

Do get me wrong, I also appreciate the warning.

Cheers