My data consist of records that have a charge start date and a charge
end date. The data will all consist of a tenant name and id. for
each tenant id, there can be multiple charges identified by the charge
start date and the charge end date. That is to say, the charge start
and end date make the records unique. There are also lease start
dates and lease end dates.
So, Tenant A has a lease start date of 01/01/00 and a lease end date
of 12/31/10, and over the life of the lease, will have 10 seperate
charges with charge steps (increases) each year of $1,000.
So, what you will have is something like this:
A charge start date of 01/01/00 (which is the same as the lease start
date), and a charge end date of 12/31/00, with a value of $10,000
(this is the first charge). The second charge will have a charge
start date of 01/01/01 and a charge end date of 12/31/01, with a value
of $11,000. The third charge will have a charge start date of
01/01/02 with a charge end date of 12/31/02. The pattern will follow
to the end of the lease, so for the life of the lease, there will be
10 unique records for Tenant A.
To complicate things even more, some of the charges start in the
middle of the year, or on the second quarter, etc, etc... For
instance, the (first)charge start date may be 04/01/00 and the charge
end date will be 03/31/01, and this pattern will continue.
There are at least 1,000 records. The longest lease life is out to
2084, with a lease start date of 2000.
My challenge is to create a spreadsheet with the column headings of
each year out to 2084, picking up the charge associated with each
year.
Now, some of the charges are based on five years, that is, the charge
start date is 01/01/01 and the charge end date is 12/31/05, etc....
crosstab query will not work, because I need a value in each field,
unless there is some way of populating the field programatically.
thanks in advance for all suggestions.
stan