By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,510 Members | 1,437 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,510 IT Pros & Developers. It's quick & easy.

This is my problem......

P: n/a
zpq
I have 5 records. What makes each record unique is the
charge_start_date 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
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
zp*@comcast.net wrote:
I have 5 records. What makes each record unique is the
charge_start_date 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.


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.
Nov 12 '05 #2

P: n/a
DFS

<zp*@comcast.net> wrote in message
news:7b*************************@posting.google.co m...
I have 5 records. What makes each record unique is the
charge_start_date 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.
Recommend not creating columns for each year. You can create a cross-tab
query to show your yearly charges by TenantID.
any help is appreciated.

tia

stan

Nov 12 '05 #3

P: n/a
zpq
>
I have 5 records. What makes each record unique is the
charge_start_date 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.
Actually there are more than 5,000 records. Imported from an AS400.

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_date) and the ced (charge_end_date).

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

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.

Nov 12 '05 #4

P: n/a
zp*@comcast.net wrote:
I have 5 records. What makes each record unique is the
charge_start_date 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.


Actually there are more than 5,000 records. Imported from an AS400.

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_date) and the ced (charge_end_date).

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


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

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

In a sub
Sub TestMonths()
Dim intMonths As Integer
Dim datStart As Date
datStart = #4/14/2003#
intMonths = MonthsLeftInYear(datStart) 'should return 9
intMonths = MonthsLeftInYear(Date()) 'should return 1 as it is 12/21/03
intMonths = MonthsLeftInLease(datStart, 1) 'should return 12
intMonths = MonthsLeftInLease(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.Openrecordset(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.Execute 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.Openrecordset(.....)
rst.AddNew
rst!TXID= 1
rst.Update

'to get to that record just added
rst.Bookmark = rst.LastModified

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

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

Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.