Calculating Future Months

[SQL Brad]

I am building a database to store lease dates and amounts. I would like to
build a report that shows date a contract is signed, start date of lease and
number of months. How would I show a report that prints out the next 5
months?

For example, I would enter 5/1/2008 and $500 for 6-month lease. How would I
show that months June, July, Aug, Sept, Oct. have $500 in them.

thanks

 

[Jeff Boyce]

It sounds to me like you can "calculate" the value from the # of months and
the monthly amount. If so, why bother storing the calculated value? In
some unusual situations, it may be necessary to store calculated values, but
I don't understand enough about your situation to tell if it qualifies.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[SQL Brad]

Jeff...I am not trying to store the calculated amounts, something like this
would be the data fields

column1- name
column2-lease amount
column3-number of months

but the report would look something like this:

Name Month 1 Month2 Month3 Month4 Month5
Month6
$500 $500 $500 $500
$500 $500

and then I would like to be able to total Month1, Month2, Month3, etc.

it's kind of like a projection sheet. The only other way I can think of
doing this is to have actual fields called Month1, Month2, Month3, etc. and
plaing duplicate values in each field. I was just trying to avoid entering
the same data 6x on a 6 month lease, or 12 times on an annual lease.
Actually, now that I think about it, I need to take into account the current
date when running the report. Cause if I run this report 3 months from now,
I really should only have 3 months left of data, assuming I only have 1
tenant with a 6-month lease. Does that make more sense.

 

[KARL DEWEY]

You have to add a lease start date.
Create a table named CountNumber with field CountNUM containing numbers from
0 (zero) through your maximum lease spread. This query has criteria to show
from this month forward.
SQL_Brad ---
SELECT [Lease Table].Name, [Lease Table].[lease amount], [Lease
Table].[number of months], [Lease Table].[Lease start], [CountNUM]+1 AS
[Month], DateAdd("m",[CountNUM],[Lease start]) AS [Due Date]
FROM [Lease Table], CountNumber
WHERE ((([CountNUM]+1)<=[number of months]) AND
((DateAdd("m",[CountNUM],[Lease start]))>=Date()));

TRANSFORM First(SQL_Brad.[Due Date]) AS [FirstOfDue Date]
SELECT SQL_Brad.Name, SQL_Brad.[Lease start], SQL_Brad.[number of months],
SQL_Brad.[lease amount]
FROM SQL_Brad
GROUP BY SQL_Brad.Name, SQL_Brad.[Lease start], SQL_Brad.[number of months],
SQL_Brad.[lease amount]
PIVOT "Month " & [Month];