Calculation In A Query

  • Thread starter Thread starter jutlaux
  • Start date Start date
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.
 
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.
 
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
 
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...
 
Back
Top