Query problem

[AndyShuter]

We have a mathematical eqaution that needs to be converted into a query.

Basically there are 4 parts to the eqaution which are based on a personal loan / hp agreement

Balance (B) - ie the amount borrowed
Term (N) - the number of months the loan is over
Annual Rate (F) - the cost per thousand per annum (ie 10% = £100 per thousand, per annumn)
Payment (R) - the result of the above three values

So, to calculate the PAYMENT, we use the foillowing equation
R=B((1/N)+(F/1200))

To calculate the BALANCE

B=R/((1/N)+(F/1200))

The Annual Rate...

F=(R-(B/N))*1200/B

And finally the TERM

N=B/(R-((B*F)/1200))

OK,

Our first aim is to be able to plant these mathematical equations into the grid of a query...

but secondly (and crucially) have the ability to force the control on th eform to accept either a manual input or make a calculation based on the other 3 values!!

Any help wouyld be greatly appreciated!


Happy New Year

 

[sonny]

I built a quick table using your characters to try this... Only B, F, N and a ID field.
To get "R" I used this in a query:

SELECT ((1/[N])+([F]/1200))*[B] AS 1, Table1.ID
FROM Table1;

To get "B" I used this:

SELECT R.ID, Table1.F, Table1.N, R.[1], [1]/((1/[N])+([F]/1200)) AS 2
FROM Table1 INNER JOIN R ON Table1.ID = R.ID;

This may help you get started. You will need 4 queries I believe.... I was walking ouit the door when I decided to help on this... I'll check later to see if you got it!

 

[sonny]

Here is a sample with 1 table and 4 queries. It does not allow for changing values in a query though...

I do believe this can be done with unbound controls on a form using no table and queries, just code...

 

[AndyShuter]

Thanks

Thanks 4 the solution - it got us going in the right direction!