S

#### Stephen

queries into SQL Views. The problem I am having is that SQL doesn't seem to

be able to use progressive formulas. Here is what I mean:

I have a commisison calculation that requires about 20 differnent variables.

Example ...(I have simplified this for sample purposes):

Field1 * Field2 + Field3 - Field4 = TotalA

(TotalA - Field5) * Field6 - Field7 = TotalB

(TotalB * Field8 - Field9) * Field10 - Field11 = TotalC

(TotalC - Field12) * Field13 + Field14 = TotalD

In and MDB, I can create a query with 4 simple calculations as listed above

(each of which can refer to a previous calculation (i.e. TotalA, TotalB,

TotalC)

However, when I try to do this with a SQL view, it does not recognize

TotalA, TotalB or TotalC. In order to write the above formula for the view,

I have to write:

((((((Field1 * Field2 + Field3 - Field4 ) - Field5) * Field6 - Field7) *

Field8 - Field9) * Field10 - Field11) - Field12) * Field13 + Field14

As you can imagine, with 20 variables, each of which requires significant

calculations, this formula becomes too long to process and completely

unmanageable to debug and modify. To make things more difficult, the view

is used to display the formula in a form for a user as well, so they can see

the calculation. As a result, each subtotal requires a calculation in the

view so the controls on teh form and use the specific field as a source.

Therefore I have to setup each stage of the formula as a separate

calculation. The View is then doing the following calculations:

Field1 * Field2 + Field3 - Field4 = TotalA

((Field1 * Field2 + Field3 - Field4) - Field5) * Field6 - Field7 = TotalB

(((Field1 * Field2 + Field3 - Field4) - Field5) * Field6 - Field7) *

Field8 - Field9) * Field10 - Field11 = TotalC

((((Field1 * Field2 + Field3 - Field4) - Field5) * Field6 - Field7) *

Field8 - Field9) * Field10 - Field11) - Field12) * Field13 + Field14 =

TotalD

It seems that I am asking the view to calculated the same figures multiple

times, thus making the view more complicated and thus, slower. If I realize

that TotalB is calculated wrong, I haev to then modify the formula for

TotalB and then make thos same changes to every foumla that uses TotalB as a

base for its calculation. NIGHTMARE! Help? Is there a way to refer to

"calculated fields" in a view without creating 20 separeate views, each

dependent on the prior view? Since SQL is used to evaluate very complex

calculations, it seems that this is not an unusual situation, so I imagine

there must be an easy way to to this is SQL.

-Stephen