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!

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



"Stephanie" wrote:

> 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!

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

"Stephanie" <(E-Mail Removed)> wrote in message
news:037F264D-A468-4DDE-9B8D-(E-Mail Removed)...
> 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!

> 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

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!
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions


"Sharkbyte" <(E-Mail Removed)-> wrote in message
news:87A615BF-E693-4BFE-B978-(E-Mail Removed)...
>
>> 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

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


"Al Camp" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
> "Stephanie" <(E-Mail Removed)> wrote in message
> news:037F264D-A468-4DDE-9B8D-(E-Mail Removed)...
>> 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!
>
>

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

"Al Camp" wrote:

> 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
>
>
> "Al Camp" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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
> >
> > "Stephanie" <(E-Mail Removed)> wrote in message
> > news:037F264D-A468-4DDE-9B8D-(E-Mail Removed)...
> >> 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!
> >
> >
>
>
>

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??


"Al Camp" wrote:

> 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
>
>
> "Al Camp" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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
> >
> > "Stephanie" <(E-Mail Removed)> wrote in message
> > news:037F264D-A468-4DDE-9B8D-(E-Mail Removed)...
> >> 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!
> >
> >
>
>
>

Not quite. Try this:

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

"Stephanie" <(E-Mail Removed)> wrote in message
news:C780A4FE-7845-40A3-9E81-(E-Mail Removed)...
> 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??
>
>
> "Al Camp" wrote:
>
> > 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
> >
> >
> > "Al Camp" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > 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
> > >
> > > "Stephanie" <(E-Mail Removed)> wrote in message
> > > news:037F264D-A468-4DDE-9B8D-(E-Mail Removed)...
> > >> 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!
> > >
> > >
> >
> >
> >

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


"Stephanie" <(E-Mail Removed)> wrote in message
news:610D9517-A938-4194-AC54-(E-Mail Removed)...
> 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
>
> "Al Camp" wrote:
>
>> 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
>>
>>
>> "Al Camp" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > 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
>> >
>> > "Stephanie" <(E-Mail Removed)> wrote in message
>> > news:037F264D-A468-4DDE-9B8D-(E-Mail Removed)...
>> >> 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!
>> >
>> >
>>
>>
>>