Calculation In A Query

J

jutlaux

In a query I have 2 Fields that has the following expressions

Field 1 -> Sum_Of_Count: Count
Field 2 -> Sum_Of_Failures: Failures

Both of these fields Totals = Sum

What I want to do in this query is create a new field with the expression

% Failure: [Sum_Of_Failures]/[Sum_Of_Count]

When I enter this into a new field and run the query i get a pop up asking
for "Sum_Of_Failures" and another one asking for "Sum_Of_Count". If I enter
in numbers (doesn't matter what the numbers are) in both these pop ups the
query runs and I do get the correct values in the new field % Failure for
each record.

What can I do to not have these pop ups appear within this query?

I know that if I create a new query build upon this first one and move the
expression to that one all is well. The problem is that I am modifying and
existing query that has a bunch dependencies to it and I don't want to have
to go through them all and change them.
 
K

KARL DEWEY

If it tries to do [Sum_Of_Failures]/[Sum_Of_Count] before it has performed
the underlying math it will be confused.

In my limited experience it seems to perform the actions last specified in a
SQL statement before those in the first part. But I would not put too much
money on it always working.

So, if your SQL was like this --
SELECT [ABC], [Sum_Of_Failures]/[Sum_Of_Count] AS [% Failure], Sum([Count])
AS Sum_Of_Count, Sum([ Failures]) AS Sum_Of_Failures
FROM XYZ
GROUP BY [ABC];
..... it might work.
 
J

Jeff Boyce

Access isn't smart enough to realize that you've created new fields and are
referring to them.

You'll have to use the same "calculation" you used to create those fields in
your new [% Failure] calculation, rather than referring to the new fields by
name.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
J

jutlaux

That worked, but I have to ask why?

Thanks,

Golfinray said:
try ([sum of failures]/[sum of count])

jutlaux said:
In a query I have 2 Fields that has the following expressions

Field 1 -> Sum_Of_Count: Count
Field 2 -> Sum_Of_Failures: Failures

Both of these fields Totals = Sum

What I want to do in this query is create a new field with the expression

% Failure: [Sum_Of_Failures]/[Sum_Of_Count]

When I enter this into a new field and run the query i get a pop up asking
for "Sum_Of_Failures" and another one asking for "Sum_Of_Count". If I enter
in numbers (doesn't matter what the numbers are) in both these pop ups the
query runs and I do get the correct values in the new field % Failure for
each record.

What can I do to not have these pop ups appear within this query?

I know that if I create a new query build upon this first one and move the
expression to that one all is well. The problem is that I am modifying and
existing query that has a bunch dependencies to it and I don't want to have
to go through them all and change them.
Click to expand...
Click to expand...
 
Top