Breakdown of costs into months

[Guest]

I have a database which records the cost of a guarantee o the customer and
also the period of guarantee (i.e. $1000 at 12 months or €1500 at 24 months),
it also has the date the guarantee period starts. What i need to do is create
some sort of report or pivot table that will show the year broken down into
months and the guarantee cost broken down into these months according to how
long the period is (i.e. $1000 at 12 months starting on 01/06/2006 would show
June = $100 July = $100 Aug = $100 Sept = $100 Oct = $100 etc......)

I am really having problems trying to figure this one out, can anybody offer
any light on the situation?

Thanks in Advance

 

[Guest]

Create a table name CountNumber with a number field labeled CountNUM
containing numbers from zero to your maximum spread. This query has the
payments starting the following month. Use the second query if first moth is
paid up front.

SELECT Barry.GuaranteeID, DateAdd("m",[COUNTNUM],[StartDate]) AS [Payment
Due], Round(([Price]/[Months]),2) AS [Monthly Cost], CountNumber.CountNUM AS
[Month]
FROM Barry, CountNumber
WHERE (((CountNumber.CountNUM)<=[Months] And (CountNumber.CountNUM)>0))
ORDER BY Barry.GuaranteeID, DateAdd("m",[COUNTNUM],[StartDate]);

SELECT Barry.GuaranteeID, DateAdd("m",[COUNTNUM],[StartDate]) AS [Payment
Due], Round(([Price]/[Months]),2) AS [Monthly Cost], CountNumber.CountNUM AS
[Month]
FROM Barry, CountNumber
WHERE (((CountNumber.CountNUM)<[Months]))
ORDER BY Barry.GuaranteeID, DateAdd("m",[COUNTNUM],[StartDate]);

 

[James A. Fortune]

I have a database which records the cost of a guarantee o the customer and
also the period of guarantee (i.e. $1000 at 12 months or €1500 at 24 months),
it also has the date the guarantee period starts. What i need to do is create
some sort of report or pivot table that will show the year broken down into
months and the guarantee cost broken down into these months according to how
long the period is (i.e. $1000 at 12 months starting on 01/06/2006 would show
June = $100 July = $100 Aug = $100 Sept = $100 Oct = $100 etc......)

I am really having problems trying to figure this one out, can anybody offer
any light on the situation?

Thanks in Advance

There may be a much easier way of doing this but...

Access has a financial function for annuities called Pmt.

$1000 at 12 months starting on 1/6/2006
$10000 at 5 months starting on 7/7/2006

MyTable
[ID] [Principle] [Rate] [Payments] [StartDate]
1 1000 0 12 #1/6/2006#
2 10000 0 5 #7/7/2006#

Note: - Pmt(0, 12, 1000) => 83.3333333333

I'd like that annuity to be spread out like so:
DateAdd("m", 0 to 11, [StartDate])

For that purpose, an auxiliary table of integers allows this:

tblIntegers
ID theInt
1 1
2 2
3 3
....
12 12
....

I make use of the fact that a subquery returning multiple values can be
used as an argument in a function. The DateSerial function is used to
obtain the dates for the first day of the month and the last day of the
month.

qryPaymentForMonth:
PARAMETERS dtInput DateTime;
SELECT Sum((SELECT Sum(IIf(DateAdd("m", theInt - 1 , [StartDate])
BETWEEN DateSerial(Year(dtInput), Month(dtInput), 1) AND
DateSerial(Year(dtInput), Month(dtInput) + 1, 0), 1, 0)) FROM
tblIntegers WHERE theInt <=
[Payments])*(-Pmt(0,[Payments],[Principal]))) AS PaymentForMonth FROM
MyTable;

!qryPaymentForMonth:
dtInput: 3/3/06
PaymentForMonth
83.3333333

!qryPaymentForMonth:
dtInput: 7/7/06
PaymentForMonth
2083.3333333

!qryPaymentForMonth:
dtInput: 1/1/07
PaymentForMonth
0

It looks like it has a chance of working. Grouping by something like
Format(dtX, "mmyyyy") might also work. I just tried things in thought
order. I think this should be joined with a table containing the months
you want to report instead of having a parameter for inputting a month.
N.B., tblIntegers must have enough records to cover the maximum
Payments value in MyTable. I hope this gets you started.

James A. Fortune
(e-mail address removed)

 

[James A. Fortune]

Create a table name CountNumber with a number field labeled CountNUM
containing numbers from zero to your maximum spread. This query has the
payments starting the following month. Use the second query if first moth is
paid up front.

SELECT Barry.GuaranteeID, DateAdd("m",[COUNTNUM],[StartDate]) AS [Payment
Due], Round(([Price]/[Months]),2) AS [Monthly Cost], CountNumber.CountNUM AS
[Month]
FROM Barry, CountNumber
WHERE (((CountNumber.CountNUM)<=[Months] And (CountNumber.CountNUM)>0))
ORDER BY Barry.GuaranteeID, DateAdd("m",[COUNTNUM],[StartDate]);

SELECT Barry.GuaranteeID, DateAdd("m",[COUNTNUM],[StartDate]) AS [Payment
Due], Round(([Price]/[Months]),2) AS [Monthly Cost], CountNumber.CountNUM AS
[Month]
FROM Barry, CountNumber
WHERE (((CountNumber.CountNUM)<[Months]))
ORDER BY Barry.GuaranteeID, DateAdd("m",[COUNTNUM],[StartDate]);

Karl,

I didn't see that you had already answered. I'm sorry about
gatecrashing the party.

James A. Fortune
(e-mail address removed)

 

[Guest]

Thanks Karl that worked great for me

Create a table name CountNumber with a number field labeled CountNUM
containing numbers from zero to your maximum spread. This query has the
payments starting the following month. Use the second query if first moth is
paid up front.

SELECT Barry.GuaranteeID, DateAdd("m",[COUNTNUM],[StartDate]) AS [Payment
Due], Round(([Price]/[Months]),2) AS [Monthly Cost], CountNumber.CountNUM AS
[Month]
FROM Barry, CountNumber
WHERE (((CountNumber.CountNUM)<=[Months] And (CountNumber.CountNUM)>0))
ORDER BY Barry.GuaranteeID, DateAdd("m",[COUNTNUM],[StartDate]);

SELECT Barry.GuaranteeID, DateAdd("m",[COUNTNUM],[StartDate]) AS [Payment
Due], Round(([Price]/[Months]),2) AS [Monthly Cost], CountNumber.CountNUM AS
[Month]
FROM Barry, CountNumber
WHERE (((CountNumber.CountNUM)<[Months]))
ORDER BY Barry.GuaranteeID, DateAdd("m",[COUNTNUM],[StartDate]);

I have a database which records the cost of a guarantee o the customer and
also the period of guarantee (i.e. $1000 at 12 months or €1500 at 24 months),
it also has the date the guarantee period starts. What i need to do is create
some sort of report or pivot table that will show the year broken down into
months and the guarantee cost broken down into these months according to how
long the period is (i.e. $1000 at 12 months starting on 01/06/2006 would show
June = $100 July = $100 Aug = $100 Sept = $100 Oct = $100 etc......)

I am really having problems trying to figure this one out, can anybody offer
any light on the situation?

Thanks in Advance