Need help on access database design and queries

[Gurunath Dhage]

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.