Need help on access database design and queries

Question

**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

nScheduleid txtScheduleNumber nLesseeId txtLeaseTyp numOfEMI dtmRentalStDate dtmRental dDate Leaserent nTaxRate4 nTotalInvoiceValue nTotalTaxAmt

1 37 1 monthly 60  6/29/2006   8/29/2011   10520 4% 678600 0 
2 59 1 monthly 60  11/30/2006 10/31/2011  1600  4%    205240  0

tblEMI

EMIDID nScheduleId cEMI1 cEMI2 cEMI3 cEMI4 ---   cEMI60  ---     cEMI108

1       1          10520 10520 10520 10520 ---  10520  
2       2          1600  1600 1600  1600  ---  1600  

Report
Note lease rent is the monthly emi

My desired reports should be as per current date (I started from sep10 because I want see Previous month EMI due this month )

slno schduleno Leasseid txtLeaseTyp numOfEMI dtmRentalStDate dtmRentalEndDate

1 37 1 monthly 60 6/29/2006 8/29/2011 Leaserent nTaxRate4 nTotalInvoiceValue nTotalTaxAmt

10520 4% 678600 0

2 59 1 monthly 60 11/30/2006 10/31/2011

1600 4% 205240 0

Sep10 Oct10 Nov10 Dec10 Jan11 Feb11 Mar11 Apr11 May11 Jun11 Jul11 Aug11 Sep11 Oct11 Nov11 …Conts 10520 10520 10520 10520 10520 10520 10520 10520 10520 10520 10520 10520 10520 10520 0520 10520

1600 1600 1600 1600 1600 1600 1600 1600 1600 1600 1600 1600 1600 1600 1600 1600 12120 12120 12120 12120 12120 12120 12120 12120 12120 12120 12120 12120 12120 12120 12120 12120

Answer 1

hi Remou,

cEMI1, cEMI2... are the fileds where I'm storing EMI values as we have max 108 EMI (monthly) so have created 108 cEMI fields

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 have struggling here. Please anybody help me how do this. Is there any problem with my database?

Thanks all in advance.