Prorate Amounts over 12 Periods

G

Guest

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?
 
G

Guest

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));
 
G

Guest

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

Find then highlight in yellow 6
How do I add amounts referencing specific dates? 7
most recent rows 5
Slow Crosstab Query 2
Obtain Month & Year from a Text formatted Date field using Left Fu 2
Crosstab Query based on Ordinal Values 6
Numbers from query concactinate when added in Report 1
Creating a "grid" of data 3

Top