435,079 Members | 1,788 Online
Need help? Post your question and get tips & solutions from a community of 435,079 IT Pros & Developers. It's quick & easy.

# This is my problem......

 P: n/a 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
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 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 > 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