Is there a way to eliminate redundant formula snippets 2

[EdAROC]

Is there a way to use a calculated value rather than having to re-use the formula in each column of a query?

For example...

Need the following calculations:
Layman's Terms:

Maximum Quantity is the allowed quantity over quantity ordered we may ship and bill. Example - Overrun is 5% (.05). Customer orders 100, we can ship 105 and bill.

Excess Quantity is the quantity manufactured above the Maximum Quantity. Example - we produce 108, Excess is 3. If we produce less than Max Qty the value is 0 (zero).

Excess Sold is the quantity of Excess Quantity that customer agreed to buy. Example - Customer agrees to purchase 2 of the 3 Excess Qty, 2 is the Excess Sold.

In my queries I have to repeat the same formula snippet:

Max_Qty: Qty_Ordered * (1 + Overrun_pct)

Excess_Qty: IIf(QTY_MFG > (Qty_Ordered * (1 + Overrun_pct)),Qty_Ordered * (1 + Overrun_pct)-QTY_MFG,0)

Excess_Sold: IIf(QTY_SOLD > (Qty_Ordered * (1 + Overrun_pct)),QTY_SOLD - (Qty_Ordered * (1 + Overrun_pct),0)

* Max Qty formula snippet occurs 5 times. How nice it would be to define it once and use Max_Qty instead in the other 4 places. As I understand, and have experienced, I can't reference another column.

 

[rac2]

Sure.

Create a view with columns for each calculation plus the key column. Save that view, call it SomeQuantitiesForThings.

In the queries that use these calucalted values, JOIN this view to the basic table using the key column. Then you can refer to the calculated columns from SomeQuantitiesForThings instead of repeatedly writing the formulas.

SELECT a.*,
       b.*
FROM SomeThings a
JOIN SomeQuantitiesForThings b ON b.thing_id = a.thing_id

The columns in SomeQuantitiesForThings can be referred to by name in any further calculations, aggregates, or conditions that you may need.

 

[SkipVought]

or...
write a function in VBA

Function Max_Qty(nOrdQty as Double, nOvrPct as Double) As Double
  Max_Qty = nOrdQty  * (1 + nOvrPct)
End Function

In use...
[tt]
Excess_Qty: IIf(QTY_MFG > (MaxQty([Qty_Ordered], [Overrun_pct])),MaxQty([Qty_Ordered], [Overrun_pct])-QTY_MFG,0)
[/tt]

Skip,

_[red][/red]

 

[Remou]

I am a little puzzled. In both my version of Access (2000) and the new beta version, it is possible to create a Max_Qty column and refer to it in other columns, as long as the columns appear later in the grid. I remember seeing a note about this in a Microsoft article, but unfortunately I cannot find it.

 

[EdAROC]

OK, 3 ways to skin the cat. Thanks all.

Responding in reverse order, plus latest error message and question:

Re: Creating a column and using it in later columns.
That's what I had done (months ago) and got an error message. Either on this forum or another someone explained that this functionality didn't exist. (We use Acess 2000). I'm going to try this again and see if it works.

Re: VBA Function
This is better than "brute force", still a lot of text. Was going to work on it to practice the technique.

Re: Views
How do I create a View? (New terminology and can't find it in Access Help or "The Complete Reference..." book). Is this another way of saying create a query and define Max_Qty: and Excess_Qty:, link that query in the final query and reference the values?

I appreciate all the replies - new things to learn!
-------------
Here's my latest challenge:

Got the following error message when running my query:
"This expression is typed incorrectly or it is too complex to be evaluated. for example, a numeric expressioin may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables."

Here's the expression:
EXCESS_COST: IIf(Round(([qryTally]![SumOfITQUANTITY] - Round([ORDERS]![QTY_ORDERED] * (1+[ORDERS]![OVERRUN_PCT]*0.01),0)) * [SumOfEXTENDED_CST]/[SumOfITQUANTITY],0)>0, Round(([qryTally]![SumOfITQUANTITY] - Round([ORDERS]![QTY_ORDERED] * (1+[ORDERS]![OVERRUN_PCT]*0.01),0)) * [SumOfEXTENDED_CST]/[SumOfITQUANTITY],0),0)

How do I create "variables"? Nothing in Access help or other resources to learn about it.

 

[Remou]

How about:

[tt]EXCESS_COST: IIf(Round(([qryTally]![SumOfITQUANTITY] - Round([ORDERS]![QTY_ORDERED] * (1 + [ORDERS]![OVERRUN_PCT] * 0.01), 0)) * [SumOfEXTENDED_CST] / [SumOfITQUANTITY], 0) > 0, Round(([qryTally]![SumOfITQUANTITY] - Round([ORDERS]![QTY_ORDERED] * (1 + [ORDERS]![OVERRUN_PCT] * 0.01), 0)) * [SumOfEXTENDED_CST] / [SumOfITQUANTITY], 0), 0)[/tt]

 

[EdAROC]

I compared your string to mine and see no difference.
The string produces the error as indicated in the previous message.

I'm not clear as to what point you are trying to make.

Thinking this over I'm guessing you mean that "EXCESS_COST" is a "variable" - defined in a query column.

 

[Remou]

Sorry, I thought I had made a small change. I found that the statement work in a mock-up query. I wonder about your version of Jet? Is it possible that you have an earlier version than I do?

http://support.microsoft.com/kb/239114

 

[EdAROC]

This is cool. OK. My PC has the latest for XP, 4.0.8618.0. However, my PC may not have had the latest when I encountered the problem of trying to define a value and use it in a later column. I'm going to experiment and create EXCESS_QTY (one of the repeated snippets) and then use EXCESS_QTY in a later column, like EXCESS_COST.

BTW - What does the error message mean by "Variable"?

Thanks for helping me through this.

 

[Remou]

In this case, not much I think. You could simplify the expression by breaking it down over several columns, but if you cannot refer to columns, this will not help. However, it may be worth trying, in order to find out what part of the expression Access is choking on. It is likely to be something to do with the fields, as I say, it worked ok in a mock-up.

 

[EdAROC]

Dang! OK, here's my comedy of errors...

I discovered that I can define a value, and reference the variable in later columns. Phew! Formulas are much cleaner now.

I now know why I got the error message: "This expression is typed incorrectly or it is too complex..."
I encountered was because there is a form, with VBA code, that generates the report, which runs the query, which includes some values defined from the form/vba. I discovered that I need to run the form once to get those values stored. Then I can simply run the query directly to further develop it.

Everything's running smooth now...
I'm about to submit another thread because I can't find where I stored the sample for a technique I need. :-(

Thanks everyone for your help - especially explanations and illustrations so that I can learn.

 

[rac2]

Re:   Views
How do I create a View? (New terminology and can't find it in Access Help or "The Complete Reference..." book). Is this another way of saying create a query and define Max_Qty: and Excess_Qty:, link that query in the final query and reference the values?

Yes. A VIEW is the same thing as a Select Query. VIEW is a standard term in SQL; it means a SELECT statement that has been saved, which is what is meant in Access by the term Select Query.

Max_Qty and Excess_Qty are aliases for the formulas; alias simply means they can be used in place of the formulas. They are used in the same way that column names are used.