S
Stephen
I have been migrating an MDB to SQL and have had to rewrite many of the
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
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