Disperse over months

[thart21]

Can this be done?

I have a single $ amount and an effective date. I need to take that dollar amount and disperse it out evenly over 12 months. Is it possible to create a query that will create the fields 1/1/2006, 2/1/2006 and enter those amounts?

For example, total savings is $12k and effective date is 2/1/2006. I need to show $1k in each month field in my query from 2/1/2006 through the month of 1/1/2007. I've tried DateSerial and some other methods but I can only get it to give me the divided amount in the effective date column and nothing else. Is DateSerial the way to go?

Thanks,

Toni

 

[stopher]

Is it possible to create a query that will create the fields 1/1/2006, 2/1/2006 and enter those amounts?

Do you mean physically create them or just temporarily in a query. I would advise the latter.

Take a look at the example I've attached. Basically it uses a new table called MonthOffset and a query type called a cartesian join to enable the creation on the 12 months. MonthOffset is just a list of the numbers 0-11. I've also used the following expression to create the fictitious dates based on the offset:
ProRataDate: DateAdd("m",[MonthOffSet],[baseDate])

I forgot to mention, I've added another query that shows the new dates as columns rather than rows. You could play around with this to show month/year rather than month/day/year.

hth
Stopher

 

[thart21]

This is exactly what I was looking for! Thanks so much, it makes sense to me as I look more into DateAdd. I'll be using that in the future for other things.

Thanks again!

Toni