473,320 Members | 1,848 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Need help to find total EMI of each month

Need help on access database design and queries
Hi All, I’m working on a small project for loan Financial service company. I’m developing a Schedule Entry System . It has a Leasee who takes the loan to purchase products, supplier who supplies products
There are 4 Lease Types monthly, quarterly , half yearly and yearly. There 3 type of Purchase types like local (lease is a local from local state ), interstate and import. This is because each purchase type ie local, interstate and import have different tax rates.
Company will give loan to purchase products. A agreement between company and leassee to take loan and repay the amount over agreed period .It is called schedule.Each schedule has start date and end date. Start date is what start of first EMI and last EMI ends on end date. Maximum EMI for Monthly is 108, quarterly is 40 , halfyearly is 20 and yearly is 10 EMIs.

Leasee details and Suppliers details are master tables where date is added prior to Add Schedule form.
In Add schedule form only I need to select Leassee and suppliers. Along with this in Add Schedule form I will select Lease type (Ex: Monthly) from combo box, no of EMI (ex:60), startdate , based on EMI end date is calculated automatically, Lease Amount (which is the sum of all EMIs)
Lease may buy different products from different supplies so I have a invoice table for supplier , here sum of invoice amount of all supplier is equal to lease amount

My Database design as below,


tblLeeasse Details
nLesseeId
txtLesseeName
txtContractNumber
txtLesseeAddress1
txtLesseeCity
txtLesseeState
txtLesseeCountry
txtLesseeEmail

`tblSupplierDetails
nSupplId
txtSupplName
txtSupplTinNum
txtSupplAddress1
txtSupplCity
txtSupplState
txtSupplCountry
txtSupplEmail

tblSchedule
nScheduleId
txtScheduleNumber
nLesseeId
txtLeaseTyp
numOfEMI
dtmRentalStDate
dtmRentalEndDate
nLeaseRent
nCurrenyType
nCurrencyRate
nPurType
nBasicValue0
nTaxRate0
nTaxAmt0
nBasicValue4
nTaxRate4
nTaxAmt4
nBasicValue12
nTaxRate12
nTaxAmt12
nTotalInvoiceValue
nTotalTaxAmt
dtmRegDate
txtRemarks tblEMI
nEMIID
nScheduleId
cEMI1
cEMI2
cEMI3
cEMI4
cEMI5
.
.
.
cEMI50
.
.
cEMI100
.
.
.
cEMI108



Here I’m giving sample data of two important table where I’m having problem. Some of the fields I have left due to space problem .

tblSchedule

1st Entry 2nd Entry

nScheduleId 11 12
txtScheduleNumber 39 57
nLesseeId 27 27
txtLeaseTyp monthly monthly
numOfEMI 60 60
dtmRentalStDate 29-Sep-06 30-Nov-06
dtmRentalEndDate 29-Aug-11 30-Oct-11
nLeaseRent 10520 1600
nCurrenyType INR INR
nCurrencyRate 1 1
nPurType InterState InterState
nBasicValue0 0 0
nTaxRate0 0 0
nTaxAmt0 0 0
nBasicValue4 535645 197346
nTaxRate4 4 4
nTaxAmt4 21426 7894
nBasicValue12 0 0
nTaxRate12 12.5 12.5
nTaxAmt12 0 0
nBasicValueOth 0 0
nTaxRateOth 0 0
nTaxAmtOth 0 0
nTotalInvoiceValue 557071 205240
nTotalTaxAmt 21426 7894
dtmRegDate 13-Oct-10 13-Oct-10
txtRemarks 5fdfd 205240


tblEMI

nEMIID 11 12
nschId 11 12
cEMI1 $10,520.00 $1,600.00
cEMI2 $10,520.00 $1,600.00
cEMI3 $10,520.00 $1,600.00
cEMI4 $10,520.00 $1,600.00
cEMI5 $10,520.00 $1,600.00
cEMI6 $10,520.00 $1,600.00
cEMI7 $10,520.00 $1,600.00
cEMI8 $10,520.00 $1,600.00
cEMI9 $10,520.00 $1,600.00
cEMI10 $10,520.00 $1,600.00
cEMI11 $10,520.00 $1,600.00
cEMI12 $10,520.00 $1,600.00
cEMI13 $10,520.00 $1,600.00
cEMI14 $10,520.00 $1,600.00
cEMI15 $10,520.00 $1,600.00
cEMI16 $10,520.00 $1,600.00
cEMI17 $10,520.00 $1,600.00
cEMI18 $10,520.00 $1,600.00
cEMI19 $10,520.00 $1,600.00
cEMI20 $10,520.00 $1,600.00
cEMI21 $10,520.00 $1,600.00
cEMI22 $10,520.00 $1,600.00
cEMI23 $10,520.00 $1,600.00
cEMI24 $10,520.00 $1,600.00
cEMI25 $10,520.00 $1,600.00
cEMI26 $10,520.00 $1,600.00
cEMI27 $10,520.00 $1,600.00
cEMI28 $10,520.00 $1,600.00
cEMI29 $10,520.00 $1,600.00
cEMI30 $10,520.00 $1,600.00
cEMI31 $10,520.00 $1,600.00
cEMI32 $10,520.00 $1,600.00
cEMI33 $10,520.00 $1,600.00
cEMI34 $10,520.00 $1,600.00
cEMI35 $10,520.00 $1,600.00
cEMI36 $10,520.00 $1,600.00
cEMI37 $10,520.00 $1,600.00
cEMI38 $10,520.00 $1,600.00
cEMI39 $10,520.00 $1,600.00
cEMI40 $10,520.00 $1,600.00
cEMI41 $10,520.00 $1,600.00
cEMI42 $10,520.00 $1,600.00
cEMI43 $10,520.00 $1,600.00
cEMI44 $10,520.00 $1,600.00
cEMI45 $10,520.00 $1,600.00
cEMI46 $10,520.00 $1,600.00
cEMI47 $10,520.00 $1,600.00
cEMI48 $10,520.00 $1,600.00
cEMI49 $10,520.00 $1,600.00
cEMI50 $10,520.00 $1,600.00
cEMI51 $10,520.00 $1,600.00
cEMI52 $10,520.00 $1,600.00
cEMI53 $10,520.00 $1,000.00
cEMI54 $10,520.00 $1,600.00
cEMI55 $10,000.00 $1,600.00
cEMI56 $10,520.00 $1,600.00
cEMI57 $10,520.00 $1,600.00
cEMI58 $10,520.00 $1,600.00
cEMI59 $10,520.00 $1,600.00
cEMI60 $10,520.00 $1,600.00
cEMI61 $0.00 $0.00
cEMI62 $0.00 $0.00
cEMI63 $0.00 $0.00
cEMI64 $0.00 $0.00
cEMI65 $0.00 $0.00
cEMI66 $0.00 $0.00
cEMI67 $0.00 $0.00
cEMI68 $0.00 $0.00
cEMI69 $0.00 $0.00
cEMI70 $0.00 $0.00
cEMI71 $0.00 $0.00
cEMI72 $0.00 $0.00
cEMI73 $0.00 $0.00
cEMI74 $0.00 $0.00
cEMI75 $0.00 $0.00
cEMI76 $0.00 $0.00
cEMI77 $0.00 $0.00
cEMI78 $0.00 $0.00
cEMI79 $0.00 $0.00
cEMI80 $0.00 $0.00
cEMI81 $0.00 $0.00
cEMI82 $0.00 $0.00
cEMI83 $0.00 $0.00
cEMI84 $0.00 $0.00
cEMI85 $0.00 $0.00
cEMI86 $0.00 $0.00
cEMI87 $0.00 $0.00
cEMI88 $0.00 $0.00
cEMI89 $0.00 $0.00
cEMI90 $0.00 $0.00
cEMI91 $0.00 $0.00
cEMI92 $0.00 $0.00
cEMI93 $0.00 $0.00
cEMI94 $0.00 $0.00
cEMI95 $0.00 $0.00
cEMI96 $0.00 $0.00
cEMI97 $0.00 $0.00
cEMI98 $0.00 $0.00
cEMI99 $0.00 $0.00
cEMI100 $0.00 $0.00
cEMI101 $0.00 $0.00
cEMI102 $0.00 $0.00
cEMI103 $0.00 $0.00
cEMI104 $0.00 $0.00
cEMI105 $0.00 $0.00
cEMI106 $0.00 $0.00
cEMI107 $0.00 $0.00
cEMI108 $0.00 $0.00


Everything ok for me except that I’m not able to calculate total of the EMI under each month of all schedules .For example, Consider that When I see the report from current month pending EMI (ex Oct 2010) I have see the previous months EMI due, so in report EMI start from sep 2010.

Considering this ,The Sep 2010 due for scheduleid 11 is 55th EMI and that of schedule id 12 th is 53rd EMI
So I want to calculate sum of 55th EMI of schedule id 11 and 53rd EMI of schedule id 12 . Currently I have struggling here.
Please anybody help me how do this. Is there any problem with my database?
Thanks all in advance.
Oct 18 '10 #1
3 2423
jimatqsi
1,271 Expert 1GB
Gurunath,
So in a word what you are looking for is how to calculate the number of months between two dates, the beginning date and the current date, or the report date.

If the days are not important, you can use this code to calculate the number months between two dates, for exampe between September '06 and August '10
Expand|Select|Wrap|Line Numbers
  1. Months=(YEAR(ReportDate)-YEAR(dtmRentalStDate ))*12+MONTH(ReportDate)-MONTH(dtmRentalStDate )
  2.  
If the day of the month is important you can calculate the number of months like this:
Expand|Select|Wrap|Line Numbers
  1. Months=IF(DAY(ReportDate)>=DAY(dtmRentalStDate ),0,-1)+(YEAR(ReportDate)-YEAR(dtmRentalStDate))*12+MONTH(ReportDate)-MONTH(dtmRentalStDate )
  2.  
Give thanks to Shyam Pillai of mvps.org for that code.

I might also suggest you could just get the number days between two dates and divide by 30, if that fits the business rules okay.
Expand|Select|Wrap|Line Numbers
  1. Months=datediff("d",ReportDate,dtmRentalStDate)/30
Jim
Oct 18 '10 #2
Hi Jim,
Thanks for your reply.

My question is not to calculate the number of months between two dates. In Simple words I want to calculate sum of values of EMI of a month.

Let me explain you once again, I’m working on a small project for loan Financial service company. I’m developing a Schedule Entry System . It has a Leasee who takes the loan to purchase products, supplier who supplies products
There are 4 Lease Types monthly, quarterly , half yearly and yearly. There 3 type of Purchase types like local (lease is a local from local state ), interstate and import. This is because each purchase type ie local, interstate and import have different tax rates.
Company will give loan to purchase products. A agreement between company and leassee to take loan and repay the amount over agreed period .It is called schedule.Each schedule has start date and end date. Start date is what start of first EMI and last EMI ends on end date. Maximum EMI for Monthly is 108, quarterly is 40 , halfyearly is 20 and yearly is 10 EMIs.
Leasee details and Suppliers details are master tables where detials like name address etc is added prior to Add Schedule form. In Add schedule form only I need to select Leassee and suppliers. Along with this in Add Schedule form I will select Lease type (Ex: Monthly) from combo box, no of EMI (ex:60), startdate , based on no of EMI end date is calculated automatically, Lease Amount (which is the sum of all EMIs)
Leassee may buy different products from different supplies so I have a invoice table for supplier , here sum of invoice amount of all supplier is equal to lease amount
Everything ok for me except that I’m not able to calculate total of the EMI under each month of all schedules .For example, Consider that When I see the report from current month pending EMI (ex Oct 2010) I have see the previous months EMI due, so in report EMI start from sep 2010.
(My Question is )
Considering this ,The Sep 2010 due for scheduleid 11 is 55th EMI and that of schedule id 12 th is 53rd EMI So I want to calculate sum of 55th EMI of schedule id 11 and 53rd EMI of schedule id 12 .

Currently I'm struggling here. Please anybody help me how do this.

Is there any problem with my database?
Oct 19 '10 #3
jimatqsi
1,271 Expert 1GB
Where do 55 and 53 come from? There are 48 months from Sep 2006 through Aug 2010, inclusive and these are monthly schedules. So how does the EMI for schedule 11 get calculated to 55? How does schedule 12 EMI calculate to 53?

Your question is not actually clear to me. You say
Everything ok for me except that I’m not able to calculate total of the EMI under each month of all schedules .For example, Consider that When I see the report from current month pending EMI (ex Oct 2010) I have see the previous months EMI due, so in report EMI start from sep 2010.
Is that a good thing or a bad thing? Do you mean you are seeing the prior month's EMI instead of the current month?

I suspect coming up with the EMI number is a little hard to do with only SQL, so probably I would add a column to my query that looks like this:
EMI:EMICalculate([scheduleID])

and the function EMICalculate would return either the EMI number (55,53) or alternatively it could calculate the EMI number and return the sum of the nth EMI.

Depending on your level of VBA experience you may need some additional help with that.

I might be missing the mark because I don't feel like I well understand the problem.

Jim
Oct 19 '10 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

6
by: Mark Anthony | last post by:
ok so first i will give write the question out, my code, the db structure, then what is going wrong. Write a report to return the top store in terms of Sales Dollars for each month sales have...
3
by: johkar | last post by:
I need to document.write out a select list populated with the dates for the first and third Wednesday of each month. How do I get to the actual days? <select name="mySelect"> <option value="Oct...
1
by: amit.gaind | last post by:
Please suggest something to get date corresponding to 2nd wednesday of each month in VB.NET.
3
by: amit.gaind | last post by:
Please suggest how to get date correponding to 2nd wednesday of each month ?
3
by: ats | last post by:
Does anybody have any sample code for calculating the date for teh last Friday in each month. TIA -- ats@jbex When an old lady got hit by a truck I saw the wicked gleam in your eyes
2
by: p655279 | last post by:
Dear All, Does anyone know how to extract first business date of each month in a DB2 SQL? many many thanks
1
by: AccessHunter | last post by:
I have the list of patients in the admittance table who were in care during the years 2004, 2005 and 2006. The admittance table has fields, CaseID, SeqNbr, Birthdate I want to find the patients who...
10
by: drrajnishpatel via AccessMonster.com | last post by:
i am trying to get "ID" an incremental number that simply resets to 1 each month in my "table1" thanks, -- Message posted via AccessMonster.com...
2
by: karimufeed | last post by:
I am working on an access project for pension calculation. I want to find total length of service between two dates. i.e. if the Date of entry into service is 15/3/1980 and the date of retirement...
12
Microblitz
by: Microblitz | last post by:
Graph of top four products each month for last 6 months? (Yeah I know I typ'od the title) I need create a chart which displays the four highest selling products for each month in the last 6...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.