This is my problem......

[zpq@comcast.net]

I have 5 records. What makes each record unique is the
charge_start_da te and the charge_end_date .

what i need to do is this:

rec1 csd is 04/01/03 and the ced is 03/31/05 charge_amt = 50,000
rec2 csd is 04/01/05 and the ced is 03/31/10 charge_amt = 60,000
rec3 csd is 04/01/10 and the ced is 03/31/15 charge_amt = 70,000
rec4 csd is 04/01/15 and the ced is 03/31/20 charge_amt = 80,000
rec5 csd is 04/01/20 and the ced is 03/31/25 charge_amt = 100,000

there is also a charge_amt associated with each record.

What I have to do is create calendar year charges ( jan - dec ).
2003 is 9/12 of 50,000
2004 is 3/12 of 50,000 and 9/12 of 60,000
2005 is 3/12 of 60,000 and 9/12 of 70,000
and so on.

The charge dates will vary throughout the year.

There is a tenant_id that I can group on, the 5 records above will be
grouped by a tenant_id.

So, I will have to create a new table with field headings of 2003
2004 2005 2006 2007 ......to 2025

Also, the number of years will vary, although I can look into the data
and get the end year. I am importing data into an access db.

any help is appreciated.

tia

stan

[Salad]

Re: This is my problem......

zpq@comcast.net wrote:
[color=blue]
> I have 5 records. What makes each record unique is the
> charge_start_da te and the charge_end_date .
>
> what i need to do is this:
>
> rec1 csd is 04/01/03 and the ced is 03/31/05 charge_amt = 50,000
> rec2 csd is 04/01/05 and the ced is 03/31/10 charge_amt = 60,000
> rec3 csd is 04/01/10 and the ced is 03/31/15 charge_amt = 70,000
> rec4 csd is 04/01/15 and the ced is 03/31/20 charge_amt = 80,000
> rec5 csd is 04/01/20 and the ced is 03/31/25 charge_amt = 100,000
>
> there is also a charge_amt associated with each record.
>
> What I have to do is create calendar year charges ( jan - dec ).
> 2003 is 9/12 of 50,000
> 2004 is 3/12 of 50,000 and 9/12 of 60,000
> 2005 is 3/12 of 60,000 and 9/12 of 70,000
> and so on.
>
> The charge dates will vary throughout the year.
>
> There is a tenant_id that I can group on, the 5 records above will be
> grouped by a tenant_id.
>
> So, I will have to create a new table with field headings of 2003
> 2004 2005 2006 2007 ......to 2025
>
> Also, the number of years will vary, although I can look into the data
> and get the end year. I am importing data into an access db.
>
> any help is appreciated.[/color]

And this is my answer....

Do you know VBA? If you know how to program then some commands, methods,
prooperties I'd look at include OpenRecordset, MoveFirst/Next, EOF, Insert
Into, Execute, declare variables with DIM. You might want to look at
queries and update queries.

Your problem is difficult to comprehend. Also, are you years in 5 year
increments at the end? You also discuss a chareant record...what's that?
What is 9/12? Is that 3/4ths of the amount? Is 3/12 1/4th of the
amount. What is the break point...March, Sept? You may want to readdress
the problem again and provide a better description and detail.

[DFS]

Re: This is my problem......


<zpq@comcast.ne t> wrote in message
news:7b4bd62a.0 312201104.42799 15@posting.goog le.com...[color=blue]
> I have 5 records. What makes each record unique is the
> charge_start_da te and the charge_end_date .
>
> what i need to do is this:
>
> rec1 csd is 04/01/03 and the ced is 03/31/05 charge_amt = 50,000
> rec2 csd is 04/01/05 and the ced is 03/31/10 charge_amt = 60,000
> rec3 csd is 04/01/10 and the ced is 03/31/15 charge_amt = 70,000
> rec4 csd is 04/01/15 and the ced is 03/31/20 charge_amt = 80,000
> rec5 csd is 04/01/20 and the ced is 03/31/25 charge_amt = 100,000
>
> there is also a charge_amt associated with each record.
>
> What I have to do is create calendar year charges ( jan - dec ).
> 2003 is 9/12 of 50,000
> 2004 is 3/12 of 50,000 and 9/12 of 60,000
> 2005 is 3/12 of 60,000 and 9/12 of 70,000
> and so on.
>
> The charge dates will vary throughout the year.
>
> There is a tenant_id that I can group on, the 5 records above will be
> grouped by a tenant_id.
>
> So, I will have to create a new table with field headings of 2003
> 2004 2005 2006 2007 ......to 2025
>
> Also, the number of years will vary, although I can look into the data
> and get the end year. I am importing data into an access db.[/color]

Recommend not creating columns for each year. You can create a cross-tab
query to show your yearly charges by TenantID.


[color=blue]
> any help is appreciated.
>
> tia
>
> stan[/color]

[zpq@comcast.net]

Re: This is my problem......

>[color=blue][color=green]
> > I have 5 records. What makes each record unique is the
> > charge_start_da te and the charge_end_date .
> >
> > what i need to do is this:
> >
> > rec1 csd is 04/01/03 and the ced is 03/31/05 charge_amt = 50,000
> > rec2 csd is 04/01/05 and the ced is 03/31/10 charge_amt = 60,000
> > rec3 csd is 04/01/10 and the ced is 03/31/15 charge_amt = 70,000
> > rec4 csd is 04/01/15 and the ced is 03/31/20 charge_amt = 80,000
> > rec5 csd is 04/01/20 and the ced is 03/31/25 charge_amt = 100,000
> >
> > there is also a charge_amt associated with each record.
> >
> > What I have to do is create calendar year charges ( jan - dec ).
> > 2003 is 9/12 of 50,000
> > 2004 is 3/12 of 50,000 and 9/12 of 60,000
> > 2005 is 3/12 of 60,000 and 9/12 of 70,000
> > and so on.
> >
> > The charge dates will vary throughout the year.
> >
> > There is a tenant_id that I can group on, the 5 records above will be
> > grouped by a tenant_id.
> >
> > So, I will have to create a new table with field headings of 2003
> > 2004 2005 2006 2007 ......to 2025
> >
> > Also, the number of years will vary, although I can look into the data
> > and get the end year. I am importing data into an access db.
> >
> > any help is appreciated.[/color]
>
> Actually there are more than 5,000 records. Imported from an AS400.[/color]
There are about 1,000 tenants. Each tenant could have from 5 to 10
"rent steps". the rent steps are identified by the csd
(charge_start_d ate) and the ced (charge_end_dat e).

Some of the charges start at the beginning of the year and some start
at other points of the year. Actually, the charges begin when the
lease is executed.

So, if a lease is executed on 4/1/05, then the first charge will have
a charge start date of 4/1/05 and an end charge date of (the charge
dates are negotiated, but are always based on a year), so, if a charge
starts on 4/01/05 then it would end on 3/31/what ever year was
negotiated for the first rent step, so lets say it ends on 03/31/10.
So, the first rent step with a value of $50,000 ends on 03/31/10 and a
new rent step begins on 04/01/10 and runs 5 years to 03/31/15 and has
a value of $60,000, and so on and so on.....

....but of course leases can be executed at any point during the year,
so the charge date and charge end date can occur at any point during
the year.

What I need to do is "annualize all the charges".

I do know vba. I have not used recordsets much. What what be useful
is some code to get me started.

tia

stan

[color=blue]
> Do you know VBA? If you know how to program then some commands, methods,
> prooperties I'd look at include OpenRecordset, MoveFirst/Next, EOF, Insert
> Into, Execute, declare variables with DIM. You might want to look at
> queries and update queries.
>
> Your problem is difficult to comprehend. Also, are you years in 5 year
> increments at the end? You also discuss a chareant record...what's that?
> What is 9/12? Is that 3/4ths of the amount? Is 3/12 1/4th of the
> amount. What is the break point...March, Sept? You may want to readdress
> the problem again and provide a better description and detail.[/color]

[Salad]

Re: This is my problem......

zpq@comcast.net wrote:
[color=blue][color=green]
> >[color=darkred]
> > > I have 5 records. What makes each record unique is the
> > > charge_start_da te and the charge_end_date .
> > >
> > > what i need to do is this:
> > >
> > > rec1 csd is 04/01/03 and the ced is 03/31/05 charge_amt = 50,000
> > > rec2 csd is 04/01/05 and the ced is 03/31/10 charge_amt = 60,000
> > > rec3 csd is 04/01/10 and the ced is 03/31/15 charge_amt = 70,000
> > > rec4 csd is 04/01/15 and the ced is 03/31/20 charge_amt = 80,000
> > > rec5 csd is 04/01/20 and the ced is 03/31/25 charge_amt = 100,000
> > >
> > > there is also a charge_amt associated with each record.
> > >
> > > What I have to do is create calendar year charges ( jan - dec ).
> > > 2003 is 9/12 of 50,000
> > > 2004 is 3/12 of 50,000 and 9/12 of 60,000
> > > 2005 is 3/12 of 60,000 and 9/12 of 70,000
> > > and so on.
> > >
> > > The charge dates will vary throughout the year.
> > >
> > > There is a tenant_id that I can group on, the 5 records above will be
> > > grouped by a tenant_id.
> > >
> > > So, I will have to create a new table with field headings of 2003
> > > 2004 2005 2006 2007 ......to 2025
> > >
> > > Also, the number of years will vary, although I can look into the data
> > > and get the end year. I am importing data into an access db.
> > >
> > > any help is appreciated.[/color]
> >
> > Actually there are more than 5,000 records. Imported from an AS400.[/color]
> There are about 1,000 tenants. Each tenant could have from 5 to 10
> "rent steps". the rent steps are identified by the csd
> (charge_start_d ate) and the ced (charge_end_dat e).
>
> Some of the charges start at the beginning of the year and some start
> at other points of the year. Actually, the charges begin when the
> lease is executed.
>
> So, if a lease is executed on 4/1/05, then the first charge will have
> a charge start date of 4/1/05 and an end charge date of (the charge
> dates are negotiated, but are always based on a year), so, if a charge
> starts on 4/01/05 then it would end on 3/31/what ever year was
> negotiated for the first rent step, so lets say it ends on 03/31/10.
> So, the first rent step with a value of $50,000 ends on 03/31/10 and a
> new rent step begins on 04/01/10 and runs 5 years to 03/31/15 and has
> a value of $60,000, and so on and so on.....
>
> ...but of course leases can be executed at any point during the year,
> so the charge date and charge end date can occur at any point during
> the year.
>
> What I need to do is "annualize all the charges".
>
> I do know vba. I have not used recordsets much. What what be useful
> is some code to get me started.
>
> tia
>
> stan[/color]

Here are some examples Aircode...not tested...but should give you an idea
Function MonthsLeftInYea r(datDate As Date) As Integer
Dim datYearEnd As Date
Dim datFor As Date
datYearEnd = DateSerial(Year (datDate),12,31 )
For datFor = datDate To datYearEnd
MonthsLeftInYea r = MonthsLeftInYea r + 1
datFor = DateAdd("m",1,d atFor)
next
End Function

This can be modified to:
Function MonthsLeftInLea se(datDate As Date, numYears As Integer) As Integer
Dim datEnd As Date
Dim datFor As Date
datEnd = DateSerial(Year (datDate) + numYears,Month( datDate),Day(da tDate) -1
For datFor = datDate To datEnd
MonthsLeftInLea se = MonthsLeftInLea se + 1
datFor = DateAdd("m",1,d atFor)
next
End Function

In a sub
Sub TestMonths()
Dim intMonths As Integer
Dim datStart As Date
datStart = #4/14/2003#
intMonths = MonthsLeftInYea r(datStart) 'should return 9
intMonths = MonthsLeftInYea r(Date()) 'should return 1 as it is 12/21/03
intMonths = MonthsLeftInLea se(datStart, 1) 'should return 12
intMonths = MonthsLeftInLea se(Date(), 1) 'should return 4 as it is 12/21/03
End Sub

Recordset ex: Uses TX table. Gets a couple of fields.
Sub TestTX()
' you can create a query via querybuilder. In the QB, from the menu,
select View/SQL
' copy the code (Edit/Copy) and paste into the code module and covert to
string. See
' my strSQL example

Dim strSQL As String
Dim rst As Recordset
Dim intTenant As Integer
Dim intThisYear As Integer
Dim intLastYear As Integer

intTenant = 123 'assign a tenant id. Then select all tx records between a
date range
'use this as the recordset to process/compute
strSQL = "Select TXCode , TXAmt, TXDate From TX Where TXTenant = " &
intTenant & _
" Order By TXDate"
Set rst = Currentdb.Openr ecordset(strSQL ,dbopensnapshot ) 'or use
dbopendynaset if you want to modify

'see if there an tx records
if rst.recordcount > 0 then
rst.MoveFirst 'go to first record
Do While Not rst.EOF skip to end of records
If Year(TXDate) < Year(Date)
intLastYear = intLastYear + 1
Else
intThisYear = intThisYear + 1
Endif
rst.MoveNext 'skip to next record
Loop
Endif
rst.close 'close and remove recordset
set rst = Nothing

'display to op the counts
msgbox "There were " & intLastYear & " Transactions last year and " &
intThisYear & " transactions This Year"

'if you want to add the counts for the tenant to a table
strSQL = " Insert Into TXCount (TXTenant, TXLast, TXThis) Values (" & _
intTenant & ", " & intlastyear & ", " & intThisYear & :)"
Currentdb.Execu te strSQL

End Sub

The above example gives you enough of a start on recordsets. To Add or Edit or
Delete recordsets, use dbOpenDynaset when you open. To Add or Edit the record,
use AddNew and Edit methods and Update at end Ex:
Set rst = Currentdb.Openr ecordset(.....)
rst.AddNew
rst!TXID= 1
rst.Update

'to get to that record just added
rst.Bookmark = rst.LastModifie d

'now change the id
rst.Edit
rst!TXID = 2
rst.Update
rst.bookmark = rst.lastmodifie d

'you can set a forms bookmark now
Me.Bookmark = rst.bookmark