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
txtContractNumb er
txtLesseeAddres s1
txtLesseeCity
txtLesseeState
txtLesseeCountr y
txtLesseeEmail
`tblSupplierDet ails
nSupplId
txtSupplName
txtSupplTinNum
txtSupplAddress 1
txtSupplCity
txtSupplState
txtSupplCountry
txtSupplEmail
tblSchedule
nScheduleId
txtScheduleNumb er
nLesseeId
txtLeaseTyp
numOfEMI
dtmRentalStDate
dtmRentalEndDat e
nLeaseRent
nCurrenyType
nCurrencyRate
nPurType
nBasicValue0
nTaxRate0
nTaxAmt0
nBasicValue4
nTaxRate4
nTaxAmt4
nBasicValue12
nTaxRate12
nTaxAmt12
nTotalInvoiceVa lue
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
txtScheduleNumb er 39 57
nLesseeId 27 27
txtLeaseTyp monthly monthly
numOfEMI 60 60
dtmRentalStDate 29-Sep-06 30-Nov-06
dtmRentalEndDat e 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
nTotalInvoiceVa lue 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.
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
txtContractNumb er
txtLesseeAddres s1
txtLesseeCity
txtLesseeState
txtLesseeCountr y
txtLesseeEmail
`tblSupplierDet ails
nSupplId
txtSupplName
txtSupplTinNum
txtSupplAddress 1
txtSupplCity
txtSupplState
txtSupplCountry
txtSupplEmail
tblSchedule
nScheduleId
txtScheduleNumb er
nLesseeId
txtLeaseTyp
numOfEMI
dtmRentalStDate
dtmRentalEndDat e
nLeaseRent
nCurrenyType
nCurrencyRate
nPurType
nBasicValue0
nTaxRate0
nTaxAmt0
nBasicValue4
nTaxRate4
nTaxAmt4
nBasicValue12
nTaxRate12
nTaxAmt12
nTotalInvoiceVa lue
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
txtScheduleNumb er 39 57
nLesseeId 27 27
txtLeaseTyp monthly monthly
numOfEMI 60 60
dtmRentalStDate 29-Sep-06 30-Nov-06
dtmRentalEndDat e 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
nTotalInvoiceVa lue 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.