Query bassed on criterion

[Yasir]

Hi

I have a query to get 10 year values. Is it possible to run different queries based on when the sum of the values reaches 100. Is this possible?

For example I can have

10
20
20
10
10
10
10
10
0
0

or

0
10
30
30
30
0
0
0
0
0
0

or any other number of combinations. In the first example, it reaches 100 in the 8th year so it should run query X but in the second example, it reaches 100 in the 5th year so it should run query Y. Possible?

 

[khawar]

Yes possible but using coding in vba

 

[Yasir]

Yes possible but using coding in vba

Thanks for the reply! Could you please point me in the right direction. How would I set the criterion for filtering which set of years I want? What usually happens is that the queries pick up values from three fields in a form to filter the results.

 

[khawar]

First you have to loop through recordset to retrieve the year in which your required total is achieved then using that year you have to perform further action or run a specific query

and instead of saving queries you can also build sql string dynamically to achieve what you want

 

[Yasir]

First you have to loop through recordset to retrieve the year in which your required total is achieved then using that year you have to perform further action or run a specific query

and instead of saving queries you can also build sql string dynamically to achieve what you want

Sorry bro but you'll have to elaborate on that a bit because I don't get it.

 

[khawar]

If you tell some detail what is your table structure what is your data what are the logics and what are the results you want to acheive then I can be more specific

 

[Yasir]

If you tell some detail what is your table structure what is your data what are the logics and what are the results you want to acheive then I can be more specific

Okay...

I have a table that has the disbursement profile for, let's say, a loan. You want to disburse 100,000$ over 10 years and this shows how they spread out. This table just has the disbursement percentages for the different years, all of which add up to 100%. In year 1 we would disburse 10%, in year 2 10 percent, in year3 5 percent etc...

There's a markup that's applied to those different disbursements. To calculate that I need to know the time difference between the date the amount was disbursed and the date of the last disbursement.

If for instance, I disburse 10% (10,000) in year 2 (2010) and the amount finishes disbursing 7 years from the start, then the difference is 5 years. I use that 5 years and the 10,000 to calculate the markup for that disbursement of 10,000$, this is repeated for all the different disbursements.

What I wanted to do was design a query that would first calculate the disbursment period. Based on that it would run a query that's designed for that time period. In the above example, it would run the query designed for 7 years.

I hope that made sense to you. I know it's very convoluted but I can't think of a better solution.

Thanks

 

[khawar]

and what is the query that you have designed for different years

 

[Yasir]

and what is the query that you have designed for different years

Sorry about the delay.

SELECT (([DisbursementL]![1]*(1+([dbo_Repayment]![ServiceFee]/100))^([dbo_Repayment]![GestationPeriod]-0.5))-([DisbursementL]![1]))+(([DisbursementL]![2]*(1+([dbo_Repayment]![ServiceFee]/100))^([dbo_Repayment]![GestationPeriod]-1.5))-([DisbursementL]![2]))+(([DisbursementL]![3]*(1+([dbo_Repayment]![ServiceFee]/100))^([dbo_Repayment]![GestationPeriod]-2.5))-([DisbursementL]![3]))+(([DisbursementL]![4]*(1+([dbo_Repayment]![ServiceFee]/100))^([dbo_Repayment]![GestationPeriod]-3.5))-([DisbursementL]![4]))+(([DisbursementL]![5]*(1+([dbo_Repayment]![ServiceFee]/100))^([dbo_Repayment]![GestationPeriod]-4.5))-([DisbursementL]![5]))+(([DisbursementL]![6]*(1+([dbo_Repayment]![ServiceFee]/100))^([dbo_Repayment]![GestationPeriod]-5.5))-([DisbursementL]![6]))+(([DisbursementL]![7]*(1+([dbo_Repayment]![ServiceFee]/100))^([dbo_Repayment]![GestationPeriod]-6.5))-([DisbursementL]![7])) AS Markup1, (([DisbursementL]![8]*(1+([dbo_Repayment]![ServiceFee]/100))^([dbo_Repayment]![GestationPeriod]-7.5))-([DisbursementL]![8]))+(([DisbursementL]![9]*(1+([dbo_Repayment]![ServiceFee]/100))^([dbo_Repayment]![GestationPeriod]-8.5))-([DisbursementL]![9])) AS Markup2, (([Markup1]+[Markup2]+[Lease])*((1+([ServiceFee]/100))^[RepaymentPeriod]))-([Lease]) AS Income
FROM DisbursementL, dbo_Main INNER JOIN dbo_Repayment ON (dbo_Main.ID = dbo_Repayment.MainID) AND (dbo_Main.ID = dbo_Repayment.MainID)
WHERE (((dbo_Main.Year)=[Forms]![Mainform]![ReportMainForm].[Form]![Year]) AND ((dbo_Main.Growth)=[Forms]![Mainform]![ReportMainForm].[Form]![Growth]) AND ((dbo_Main.Version)=[Forms]![Mainform]![ReportMainForm].[Form]![Version]) AND ((DisbursementL.HAYear)=[Forms]![Mainform]![ReportMainForm].[Form]![Year]))
GROUP BY DisbursementL.Lease, dbo_Repayment.ServiceFee, dbo_Repayment.RepaymentPeriod, (([DisbursementL]![1]*(1+([dbo_Repayment]![ServiceFee]/100))^([dbo_Repayment]![GestationPeriod]-0.5))-([DisbursementL]![1]))+(([DisbursementL]![2]*(1+([dbo_Repayment]![ServiceFee]/100))^([dbo_Repayment]![GestationPeriod]-1.5))-([DisbursementL]![2]))+(([DisbursementL]![3]*(1+([dbo_Repayment]![ServiceFee]/100))^([dbo_Repayment]![GestationPeriod]-2.5))-([DisbursementL]![3]))+(([DisbursementL]![4]*(1+([dbo_Repayment]![ServiceFee]/100))^([dbo_Repayment]![GestationPeriod]-3.5))-([DisbursementL]![4]))+(([DisbursementL]![5]*(1+([dbo_Repayment]![ServiceFee]/100))^([dbo_Repayment]![GestationPeriod]-4.5))-([DisbursementL]![5]))+(([DisbursementL]![6]*(1+([dbo_Repayment]![ServiceFee]/100))^([dbo_Repayment]![GestationPeriod]-5.5))-([DisbursementL]![6]))+(([DisbursementL]![7]*(1+([dbo_Repayment]![ServiceFee]/100))^([dbo_Repayment]![GestationPeriod]-6.5))-([DisbursementL]![7])), (([DisbursementL]![8]*(1+([dbo_Repayment]![ServiceFee]/100))^([dbo_Repayment]![GestationPeriod]-7.5))-([DisbursementL]![8]))+(([DisbursementL]![9]*(1+([dbo_Repayment]![ServiceFee]/100))^([dbo_Repayment]![GestationPeriod]-8.5))-([DisbursementL]![9])), dbo_Repayment.FinanceTypeCode
HAVING (((dbo_Repayment.FinanceTypeCode)="01"));

This is for when it takes 9 years to reach 100%, if it's 8 years I would remove

([DisbursementL]![8]))+(([DisbursementL]![9]*(1+([dbo_Repayment]![ServiceFee]/100))^([dbo_Repayment]![GestationPeriod]-8.5))-([DisbursementL]![9])

Thanks for your help bro!