Query Calculations

On Fri, 3 Oct 2008 13:44:01 -0700, Katherine
<Katherine.TakeThisOut@discussions.microsoft.com> wrote:

>Can a calculation in a query use information from a second calculation in the
>same query?

Generally, no.

>I am trying to calculate an average rate. I have one calculation that gives
>me the time, but when I try to include that field in a [volume] / [time]
>calculation, it just shows up as #ERROR. How should I write the calculations
>to make this work?
>
>This is what they look like now:
>
>Calculation 1: ElapsedTimeString([Start time],[Stop time]) (I got the
>calculation from somewhere on this site and put it in a module)
>Calculation 2: [Volume] / [Calculation 1]

Recapitulate the calculation rather than using the fieldname - i.e. call the
function a second time.

Based on the name of the function, it sounds like it returns a String - which
cannot be used in a calculation (or which may not give the desired results if
it is). What is the function you're using? Does it return a string or a
number? Is there some reason you can't use the builtin DateDiff() function,
which does return a number? Note that 315 minutes is a number and can be used
in a calculation, but 5:15 is NOT.

--

John W. Vinson [MVP]

On Oct 3, 1:44 pm, Katherine <Kather....TakeThisOut@discussions.microsoft.com>
wrote:
> Can a calculation in a query use information from a second calculation in the
> same query?
>
> I am trying to calculate an average rate.  I have one calculation that gives
> me the time, but when I try to include that field in a  [volume] / [time]
> calculation, it just shows up as #ERROR.  How should I write the calculations
> to make this work?
>
> This is what they look like now:
>
> Calculation 1: ElapsedTimeString([Start time],[Stop time]) (I got the
> calculation from somewhere on this site and put it in a module)
> Calculation 2: [Volume] / [Calculation 1]
>
> Thanks,
> Katherine

The function ElapsedTimeString() appears to return a string. That
string is stored in the string variable [Calculation 1].

I would expect a type mismatch or division by zero error in the
calculation
[Volume] / [Calculation 1].

Can you substitute a call to the DateDiff for the [Calculation 1]?

> Can a calculation in a query use information from a second calculation in
the
> same query?

Yes, I have many very complex queries like that.

When you include a calculation like that, it is calculated over and
over. At least one re-calculation for everytime the value is used,
and possibly more.

If you have a line like

c1: ElapsedTimeString([Start time],[Stop time])
v1/ [C1]
v2/ [C1]
v3/ [C1]
v3/ [C1]

where [start time] and [stop time] are field in the record,
then C1 is recalculated (at least) once every time it is used.

So you don't gain any speed by using the alias c1 for the
calculation, it just makes the query easier to read.

On the other hand, there is no possibility that the query is
failing because 'calculation1' has not been calculated yet:
it is calculated in every field where it is used.

So it may be that your query is failing because [start time] and
[stop time] are the same, or because you have a problem in
your calculation function, or because the return value of the
function is not a number.

Doing divisions like that, I always guard the division. Perhaps
with something like this:

IIF(val(calculation1)<>0,[volume]/calculation1,Null)

When you have more complex calculations that have internal
database lookups, it is a good idea to check the function
parameters inside the function, and use a saved value if the
parameters have not changed. If that was a very slow function,
using a saved value would automatically make your query
twice as fast, even if the value was just used twice! In practice,
a User Defined Function like that is called even more often
than once every time it is used, so the speed of a slow function
is even more critical.

(david)


"Katherine" <Katherine DeleteThis @discussions.microsoft.com> wrote in message
news:AA3885CB-FB2B-4DEE-91F5-AF1BAB655474@microsoft.com...
> Can a calculation in a query use information from a second calculation in
the
> same query?
>
> I am trying to calculate an average rate. I have one calculation that
gives
> me the time, but when I try to include that field in a [volume] / [time]
> calculation, it just shows up as #ERROR. How should I write the
calculations
> to make this work?
>
> This is what they look like now:
>
> Calculation 1: ElapsedTimeString([Start time],[Stop time]) (I got the
> calculation from somewhere on this site and put it in a module)
> Calculation 2: [Volume] / [Calculation 1]
>
> Thanks,
> Katherine

The function returns a string, I think, based on the name. It gives a number
(say, 22 minutes), not a time. Can the DateDif function be used to calculate
time in the same way as the string? Would that work?

I tried entering the formula for the first calculation rather than just its
name in the second calculation, but it still didn't work.

Thanks,
Katherine

"John W. Vinson" wrote:

> On Fri, 3 Oct 2008 13:44:01 -0700, Katherine
> <Katherine.RemoveThis@discussions.microsoft.com> wrote:
>
> >Can a calculation in a query use information from a second calculation in the
> >same query?
>
> Generally, no.
>
> >I am trying to calculate an average rate. I have one calculation that gives
> >me the time, but when I try to include that field in a [volume] / [time]
> >calculation, it just shows up as #ERROR. How should I write the calculations
> >to make this work?
> >
> >This is what they look like now:
> >
> >Calculation 1: ElapsedTimeString([Start time],[Stop time]) (I got the
> >calculation from somewhere on this site and put it in a module)
> >Calculation 2: [Volume] / [Calculation 1]
>
> Recapitulate the calculation rather than using the fieldname - i.e. call the
> function a second time.
>
> Based on the name of the function, it sounds like it returns a String - which
> cannot be used in a calculation (or which may not give the desired results if
> it is). What is the function you're using? Does it return a string or a
> number? Is there some reason you can't use the builtin DateDiff() function,
> which does return a number? Note that 315 minutes is a number and can be used
> in a calculation, but 5:15 is NOT.
>
> --
>
> John W. Vinson [MVP]
>

On Mon, 6 Oct 2008 09:08:01 -0700, Katherine
<Katherine.DeleteThis@discussions.microsoft.com> wrote:

>The function returns a string, I think, based on the name. It gives a number
>(say, 22 minutes), not a time.

Please copy and paste the VBA of the function, or at least its first line
(Public Function ElapsedTimeString...) to a message.

>Can the DateDif function be used to calculate
>time in the same way as the string? Would that work?

Open the VBA editor to get access to the VBA help file; press F1; and search
for DateDiff (note two f's) to find out how it works and what it can do.

>I tried entering the formula for the first calculation rather than just its
>name in the second calculation, but it still didn't work.

Please post the actual SQL of your query and specify in what way it "didn't
work". It might also help to indicate what result you want, you haven't
actually said. Volume per minute? per hour? per day?

Note that an Access Date/Time value is actually a number, a count of days and
fractions of a day (times) since midnight, December 30, 1899. It's best used
for specific points in time, not for durations.
--

John W. Vinson [MVP]

Thanks! Using DateDiff instead of the time string worked.

"John W. Vinson" wrote:

> On Fri, 3 Oct 2008 13:44:01 -0700, Katherine
> <Katherine.DeleteThis@discussions.microsoft.com> wrote:
>
> >Can a calculation in a query use information from a second calculation in the
> >same query?
>
> Generally, no.
>
> >I am trying to calculate an average rate. I have one calculation that gives
> >me the time, but when I try to include that field in a [volume] / [time]
> >calculation, it just shows up as #ERROR. How should I write the calculations
> >to make this work?
> >
> >This is what they look like now:
> >
> >Calculation 1: ElapsedTimeString([Start time],[Stop time]) (I got the
> >calculation from somewhere on this site and put it in a module)
> >Calculation 2: [Volume] / [Calculation 1]
>
> Recapitulate the calculation rather than using the fieldname - i.e. call the
> function a second time.
>
> Based on the name of the function, it sounds like it returns a String - which
> cannot be used in a calculation (or which may not give the desired results if
> it is). What is the function you're using? Does it return a string or a
> number? Is there some reason you can't use the builtin DateDiff() function,
> which does return a number? Note that 315 minutes is a number and can be used
> in a calculation, but 5:15 is NOT.
>
> --
>
> John W. Vinson [MVP]
>