Prorate Amounts over 12 Periods

K

Kirk P.

Starting with this set of data,

Eff Date Annual Revenue
07/04/07 $56,600,000.00

Can I design a query that would display the annual revenue spread out
equally over 12 months, like this?

Eff Date Annual Revenue Monthly Revenue
07/04/07 $56,600,000.00 $4,716,666.67
08/04/07 $56,600,000.00 $4,716,666.67
09/04/07 $56,600,000.00 $4,716,666.67
10/04/07 $56,600,000.00 $4,716,666.67
11/04/07 $56,600,000.00 $4,716,666.67
12/04/07 $56,600,000.00 $4,716,666.67
01/04/08 $56,600,000.00 $4,716,666.67
02/04/08 $56,600,000.00 $4,716,666.67
03/04/08 $56,600,000.00 $4,716,666.67
04/04/08 $56,600,000.00 $4,716,666.67
05/04/08 $56,600,000.00 $4,716,666.67
06/04/08 $56,600,000.00 $4,716,666.67

Calculating the monthly amount is simple enought, but how do I get it to
display the effective date each month for a 12 month period?
 
K

KARL DEWEY

Create a table named CountNumber with a field named CountNUM containing zero
to your maximum spread.

SELECT DateAdd("m",[CountNUM],[Eff Date]) AS [Effective Date],
Kirk_P_1.[Annual Revenue], Format([Annual Revenue]/[Enter number of months
spread],"$#,###.##") AS [Monthly Revenue], CountNumber.CountNUM
FROM Kirk_P_1, CountNumber
WHERE (((CountNumber.CountNUM)<=[Enter number of months spread]-1));
 
K

Kirk P.

Works perfectly, thanks Karl!


KARL DEWEY said:
Create a table named CountNumber with a field named CountNUM containing zero
to your maximum spread.

SELECT DateAdd("m",[CountNUM],[Eff Date]) AS [Effective Date],
Kirk_P_1.[Annual Revenue], Format([Annual Revenue]/[Enter number of months
spread],"$#,###.##") AS [Monthly Revenue], CountNumber.CountNUM
FROM Kirk_P_1, CountNumber
WHERE (((CountNumber.CountNUM)<=[Enter number of months spread]-1));

--
KARL DEWEY
Build a little - Test a little


Kirk P. said:
Starting with this set of data,

Eff Date Annual Revenue
07/04/07 $56,600,000.00

Can I design a query that would display the annual revenue spread out
equally over 12 months, like this?

Eff Date Annual Revenue Monthly Revenue
07/04/07 $56,600,000.00 $4,716,666.67
08/04/07 $56,600,000.00 $4,716,666.67
09/04/07 $56,600,000.00 $4,716,666.67
10/04/07 $56,600,000.00 $4,716,666.67
11/04/07 $56,600,000.00 $4,716,666.67
12/04/07 $56,600,000.00 $4,716,666.67
01/04/08 $56,600,000.00 $4,716,666.67
02/04/08 $56,600,000.00 $4,716,666.67
03/04/08 $56,600,000.00 $4,716,666.67
04/04/08 $56,600,000.00 $4,716,666.67
05/04/08 $56,600,000.00 $4,716,666.67
06/04/08 $56,600,000.00 $4,716,666.67

Calculating the monthly amount is simple enought, but how do I get it to
display the effective date each month for a 12 month period?
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

count not-overlapping days in a "Table" with Beginning and EndingsDates Specified 0
Calculating Work Time 7
Find then highlight in yellow 6
Looping through data with calculations 1
next date?? 1
How do I add amounts referencing specific dates? 7
Reverse Index Match 2
Get the last price 3

Top