cascading calculations HELP!!!

[Guest]

HI I'm new to access and I need to to some calculations in it. I'm doing them
in a query. I have a few problems. I tried before to do the Iif statement but
somehow it returned almost 2 million results and then froze on me. I want to
end up with a query where 4 fields are calculated simultanously. 2 of these
are Iif calculations and the others are simple multiplications or
subtractions. my problems are this:

1. in the design of the query what do I put in the field box? ( i want this
to be a new field)
2. some of the calculations are based on values that are in two separate
tables. (will this work)
3. calculations are cascading, ie the second one uses the result of the
first and the 3rd uses the result of the 2nd etc. however all numbers are
important and need to be viewable.

so can I do this? please help!! and include details in your answers incase
it's something fiddly im just missing right now.

Thanks!

 

[Guest]

Stephanie:

First, you will need both tables, with a relationship, in the query. Then
you can set your fields up like this:

1. [table1].[value1]
2. [table1].[value2]
3. [table2].[value3]
4. [table2].[value4]
5. [Name1]: [value1] <function> [value2]
6. [Name2]: [name1] <function> [value3]
7. [Name3]: [name2] <function> [value4]

If you need an IIf statement, make sure it is the If...Then...Else format.
Example:

Iif([value1] <function> [value2]=0, 1, [value1] <function> [value2])

**read as IF [value1] <function> [value2]=0 THEN set field equal to 1 ELSE
set field equal to [value1] <function> [value2].

Hope this helps.

Sharkbyte

 

[Al Camp]

Stephanie,
I take it you're using the query design grid...
I'll use an example...
(1. and 3.) In the next blank column, in the "Field:" box,
LineTotal : Price * Qty
That calculates the LineTotal, and binds the calculation to that field name.

You CAN'T "chain" calculations in a query by calling the name of another
calculation... like this...
Commision : LineTotal * .10 (won't work)
You have to call the complete calculation... like this... in another
blank column
Commission : (Price * Qty) * .10

If you had another calculation that relied on LineTotal and Commision,
again... you would have to refer them by their full calculation, and not by
their name.
OtherCalc : ((Price * Qty) * .10) / 2

(2.) Yes you can use values from several tables, and long as they are
included in the query, and properly related.

 

[Guest]

You CAN'T "chain" calculations in a query by calling the name of another
calculation... like this...
Commision : LineTotal * .10 (won't work)
You have to call the complete calculation... like this... in another
blank column
Commission : (Price * Qty) * .10

I don't recall having this problem, that Al mentions, but I haven't had to
do this, for some time, and would recommend you go with his suggestions.

Sharkbyte

 

[Al Camp]

Sharkbyte,
Hmmm... I just tested that, and you're right. While my test case was very
simple...
Calc1 : Amt * 10
Calc2 : [Calc1] / 2 (worked)

Seems as though I've tried "chaining" many times before, and run into
problems. Most probably, because those calcs were not so straight forward,
I may have been introducing some other issue, and just thought it was a
"chaining" problem.

Well, I'll let Stephanie know that. My way would work, but... it is a bit
of overkill if not really necessary.
Thanks for the heads up!

 

[Al Camp]

Stephanie,
I stand corrected on my statement...

You CAN'T "chain" calculations in a query by calling the name of
another calculation... like this...

It appears (via Sharkbyte's post) that "chaining" is OK. Using my same
examples...
1. LineTotal : Price * Qty
2. Commision : [LineTotal] * .10
3. OtherCalc : [Commision] / 2
Mea Culpa!

 

[Guest]

Hi Al, so I'm still having some problems. I've put all the info I need for
the calculations into one query and then i'm going to base the query where
i'm doing the calculations on that one. but I can't seem to make the iff
statement work it keeps saying its undefined.
I have this statement in the criteria:
iff([InfoForOpp]![PERIMETER (m)]*[InfoForOpp]![angler
density]/100>100,[InfoForOpp]![PERIMETER (m)]*[InfoForOpp]![angler
density]/100,[InfoForOpp]![SA (HA)]*[InfoForOpp]![angler density])

and the field is perimeter and the table is InfoForOpp

what have I done wrong??? I want this to be outputed into a new field names
TotRodDays

Stephanie,
I stand corrected on my statement...

You CAN'T "chain" calculations in a query by calling the name of
another calculation... like this...

It appears (via Sharkbyte's post) that "chaining" is OK. Using my same
examples...
1. LineTotal : Price * Qty
2. Commision : [LineTotal] * .10
3. OtherCalc : [Commision] / 2
Mea Culpa!
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

Stephanie,
I take it you're using the query design grid...
I'll use an example...
(1. and 3.) In the next blank column, in the "Field:" box,
LineTotal : Price * Qty
That calculates the LineTotal, and binds the calculation to that field
name.

You CAN'T "chain" calculations in a query by calling the name of
another calculation... like this...
Commision : LineTotal * .10 (won't work)
You have to call the complete calculation... like this... in another
blank column
Commission : (Price * Qty) * .10

If you had another calculation that relied on LineTotal and Commision,
again... you would have to refer them by their full calculation, and not
by their name.
OtherCalc : ((Price * Qty) * .10) / 2

(2.) Yes you can use values from several tables, and long as they are
included in the query, and properly related.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

 

[Guest]

nevermind I figured out that one. but I do have one other question. I need to
make an expression that does one of three things depending on the value.
<20ha=1
21-50ha=2

50ha=3

so right now I have it written as:

iif([ha]<20, 1, iff([ha]<50, 2, iif([ha]>=50, 3)))

is this right??

Stephanie,
I stand corrected on my statement...

You CAN'T "chain" calculations in a query by calling the name of
another calculation... like this...

It appears (via Sharkbyte's post) that "chaining" is OK. Using my same
examples...
1. LineTotal : Price * Qty
2. Commision : [LineTotal] * .10
3. OtherCalc : [Commision] / 2
Mea Culpa!
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

Stephanie,
I take it you're using the query design grid...
I'll use an example...
(1. and 3.) In the next blank column, in the "Field:" box,
LineTotal : Price * Qty
That calculates the LineTotal, and binds the calculation to that field
name.

You CAN'T "chain" calculations in a query by calling the name of
another calculation... like this...
Commision : LineTotal * .10 (won't work)
You have to call the complete calculation... like this... in another
blank column
Commission : (Price * Qty) * .10

If you had another calculation that relied on LineTotal and Commision,
again... you would have to refer them by their full calculation, and not
by their name.
OtherCalc : ((Price * Qty) * .10) / 2

(2.) Yes you can use values from several tables, and long as they are
included in the query, and properly related.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

 

[Ron Hinds]

Not quite. Try this:

iif([ha]<20, 1, iff([ha]<50, 2, 3))

nevermind I figured out that one. but I do have one other question. I need to
make an expression that does one of three things depending on the value.
<20ha=1
21-50ha=2

50ha=3

so right now I have it written as:

iif([ha]<20, 1, iff([ha]<50, 2, iif([ha]>=50, 3)))

is this right??

Stephanie,
I stand corrected on my statement...

You CAN'T "chain" calculations in a query by calling the name of
another calculation... like this...

It appears (via Sharkbyte's post) that "chaining" is OK. Using my same
examples...
1. LineTotal : Price * Qty
2. Commision : [LineTotal] * .10
3. OtherCalc : [Commision] / 2
Mea Culpa!
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

Stephanie,
I take it you're using the query design grid...
I'll use an example...
(1. and 3.) In the next blank column, in the "Field:" box,
LineTotal : Price * Qty
That calculates the LineTotal, and binds the calculation to that field
name.

You CAN'T "chain" calculations in a query by calling the name of
another calculation... like this...
Commision : LineTotal * .10 (won't work)
You have to call the complete calculation... like this... in another
blank column
Commission : (Price * Qty) * .10

If you had another calculation that relied on LineTotal and Commision,
again... you would have to refer them by their full calculation, and not
by their name.
OtherCalc : ((Price * Qty) * .10) / 2

(2.) Yes you can use values from several tables, and long as they are
included in the query, and properly related.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

HI I'm new to access and I need to to some calculations in it. I'm doing
them
in a query. I have a few problems. I tried before to do the Iif statement
but
somehow it returned almost 2 million results and then froze on me. I want
to
end up with a query where 4 fields are calculated simultanously. 2 of
these
are Iif calculations and the others are simple multiplications or
subtractions. my problems are this:

1. in the design of the query what do I put in the field box? ( i want
this
to be a new field)
2. some of the calculations are based on values that are in two separate
tables. (will this work)
3. calculations are cascading, ie the second one uses the result of the
first and the 3rd uses the result of the 2nd etc. however all numbers are
important and need to be viewable.

so can I do this? please help!! and include details in your answers
incase
it's something fiddly im just missing right now.

Thanks!

 

[Al Camp]

Stephanie,
If you want an IFF calculation bound to a field called TotRodDays, you
need to put into the Field: box in a blank query column.
TotRodDays : (Your IFF statement here)

I'm a bit hinky about the field name [PERIMETER (m)] or [SA (HA)]. I
would change the names to PerimeterM, or SaHA and lose the parens in those
names. Also, avoid spaces in Field names (ex. [AnglerDensity])

If you have properly included the tables with the values you need for
your calculations in your query, it shouldn't be necessary to include the
table names for each element in the calculation. If there is more than one
table in the query with a ParameterM or AnglerDensity, then table names are
called for. Otherwise, not...
If you do need to specify the table for each element then...
[InfoForOpp].[ParimeterM]
is the correct syntax for each element, rather than with a "!" separator.

There is no way I can verify your calculation, your fields, or the exact
syntax in a text email... but try this...

InfoForOpp : IFF(([PerimeterM] * [Angler Density]) / 100 > 100,
([PerimeterM] * [AnglerDensity]) / 100,
[SaHA] * [AnglerDensity])
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

Hi Al, so I'm still having some problems. I've put all the info I need for
the calculations into one query and then i'm going to base the query where
i'm doing the calculations on that one. but I can't seem to make the iff
statement work it keeps saying its undefined.
I have this statement in the criteria:
iff([InfoForOpp]![PERIMETER (m)]*[InfoForOpp]![angler
density]/100>100,[InfoForOpp]![PERIMETER (m)]*[InfoForOpp]![angler
density]/100,[InfoForOpp]![SA (HA)]*[InfoForOpp]![angler density])

and the field is perimeter and the table is InfoForOpp

what have I done wrong??? I want this to be outputed into a new field
names
TotRodDays

Stephanie,
I stand corrected on my statement...

You CAN'T "chain" calculations in a query by calling the name of
another calculation... like this...

It appears (via Sharkbyte's post) that "chaining" is OK. Using my
same
examples...
1. LineTotal : Price * Qty
2. Commision : [LineTotal] * .10
3. OtherCalc : [Commision] / 2
Mea Culpa!
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

Stephanie,
I take it you're using the query design grid...
I'll use an example...
(1. and 3.) In the next blank column, in the "Field:" box,
LineTotal : Price * Qty
That calculates the LineTotal, and binds the calculation to that field
name.

You CAN'T "chain" calculations in a query by calling the name of
another calculation... like this...
Commision : LineTotal * .10 (won't work)
You have to call the complete calculation... like this... in another
blank column
Commission : (Price * Qty) * .10

If you had another calculation that relied on LineTotal and
Commision,
again... you would have to refer them by their full calculation, and
not
by their name.
OtherCalc : ((Price * Qty) * .10) / 2

(2.) Yes you can use values from several tables, and long as they are
included in the query, and properly related.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

HI I'm new to access and I need to to some calculations in it. I'm
doing
them
in a query. I have a few problems. I tried before to do the Iif
statement
but
somehow it returned almost 2 million results and then froze on me. I
want
to
end up with a query where 4 fields are calculated simultanously. 2 of
these
are Iif calculations and the others are simple multiplications or
subtractions. my problems are this:

1. in the design of the query what do I put in the field box? ( i want
this
to be a new field)
2. some of the calculations are based on values that are in two
separate
tables. (will this work)
3. calculations are cascading, ie the second one uses the result of
the
first and the 3rd uses the result of the 2nd etc. however all numbers
are
important and need to be viewable.

so can I do this? please help!! and include details in your answers
incase
it's something fiddly im just missing right now.

Thanks!

 

[Guest]

Thanks all! It works perfectly now. and the cascades all work fine incase you
get asked that again.

;-)

nevermind I figured out that one. but I do have one other question. I need to
make an expression that does one of three things depending on the value.
<20ha=1
21-50ha=2

50ha=3

so right now I have it written as:

iif([ha]<20, 1, iff([ha]<50, 2, iif([ha]>=50, 3)))

is this right??

Stephanie,
I stand corrected on my statement...

You CAN'T "chain" calculations in a query by calling the name of
another calculation... like this...

It appears (via Sharkbyte's post) that "chaining" is OK. Using my same
examples...
1. LineTotal : Price * Qty
2. Commision : [LineTotal] * .10
3. OtherCalc : [Commision] / 2
Mea Culpa!
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

Stephanie,
I take it you're using the query design grid...
I'll use an example...
(1. and 3.) In the next blank column, in the "Field:" box,
LineTotal : Price * Qty
That calculates the LineTotal, and binds the calculation to that field
name.

You CAN'T "chain" calculations in a query by calling the name of
another calculation... like this...
Commision : LineTotal * .10 (won't work)
You have to call the complete calculation... like this... in another
blank column
Commission : (Price * Qty) * .10

If you had another calculation that relied on LineTotal and Commision,
again... you would have to refer them by their full calculation, and not
by their name.
OtherCalc : ((Price * Qty) * .10) / 2

(2.) Yes you can use values from several tables, and long as they are
included in the query, and properly related.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

HI I'm new to access and I need to to some calculations in it. I'm doing
them
in a query. I have a few problems. I tried before to do the Iif statement
but
somehow it returned almost 2 million results and then froze on me. I want
to
end up with a query where 4 fields are calculated simultanously. 2 of
these
are Iif calculations and the others are simple multiplications or
subtractions. my problems are this:

1. in the design of the query what do I put in the field box? ( i want
this
to be a new field)
2. some of the calculations are based on values that are in two separate
tables. (will this work)
3. calculations are cascading, ie the second one uses the result of the
first and the 3rd uses the result of the 2nd etc. however all numbers are
important and need to be viewable.

so can I do this? please help!! and include details in your answers
incase
it's something fiddly im just missing right now.

Thanks!

 

[Al Camp]

No...
Your second condition will return any value less than 50 (including less
than 20)
And, if your IFF fails the first two conditions... then it must be a 3.
Try...
IIf([ha] < 20, 1, IIf([ha] >= 20 And [ha] < 50, 2, 3))
assuming [ha] is never null...
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

nevermind I figured out that one. but I do have one other question. I need
to
make an expression that does one of three things depending on the value.
<20ha=1
21-50ha=2

50ha=3

so right now I have it written as:

iif([ha]<20, 1, iff([ha]<50, 2, iif([ha]>=50, 3)))

is this right??

Stephanie,
I stand corrected on my statement...

You CAN'T "chain" calculations in a query by calling the name of
another calculation... like this...

It appears (via Sharkbyte's post) that "chaining" is OK. Using my
same
examples...
1. LineTotal : Price * Qty
2. Commision : [LineTotal] * .10
3. OtherCalc : [Commision] / 2
Mea Culpa!
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

Stephanie,
I take it you're using the query design grid...
I'll use an example...
(1. and 3.) In the next blank column, in the "Field:" box,
LineTotal : Price * Qty
That calculates the LineTotal, and binds the calculation to that field
name.

You CAN'T "chain" calculations in a query by calling the name of
another calculation... like this...
Commision : LineTotal * .10 (won't work)
You have to call the complete calculation... like this... in another
blank column
Commission : (Price * Qty) * .10

If you had another calculation that relied on LineTotal and
Commision,
again... you would have to refer them by their full calculation, and
not
by their name.
OtherCalc : ((Price * Qty) * .10) / 2

(2.) Yes you can use values from several tables, and long as they are
included in the query, and properly related.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

HI I'm new to access and I need to to some calculations in it. I'm
doing
them
in a query. I have a few problems. I tried before to do the Iif
statement
but
somehow it returned almost 2 million results and then froze on me. I
want
to
end up with a query where 4 fields are calculated simultanously. 2 of
these
are Iif calculations and the others are simple multiplications or
subtractions. my problems are this:

1. in the design of the query what do I put in the field box? ( i want
this
to be a new field)
2. some of the calculations are based on values that are in two
separate
tables. (will this work)
3. calculations are cascading, ie the second one uses the result of
the
first and the 3rd uses the result of the 2nd etc. however all numbers
are
important and need to be viewable.

so can I do this? please help!! and include details in your answers
incase
it's something fiddly im just missing right now.

Thanks!