Share via

Calculated fields in query not working

Anonymous
2010-09-15T19:04:47+00:00

I have a query which is calculating totals on several fields.  The query has a field called site (which is the customer name) and then several fields which calculate totals using SUM. This works nicely.

However when I add a new calculated field with the following calculation (using the total fields from the same query) it doesn't work, I know that I'm doing something wrong but I just can't work out what, it's complicated I appreciate but can anyone help?

Combined spot: 100-([SumOfSpot Effluent Failures]+[SumOfSpot Air Failures])/([SumOfSpot Effluent]+[SumOfSpot Air])*100

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2010-09-15T19:20:33+00:00

    Post the SQL of your queries.

    Build a little, test a little.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2010-09-15T19:17:51+00:00

    I've tried creating a new query and basing this query on the query containing the totals, then creating a new calculated field in the new query - still no joy

    Was this answer helpful?

    0 comments
  3. Anonymous
    2010-09-15T19:13:09+00:00

    However when I add a new calculated field with the following calculation (using the total fields from the same query) it doesn't work,

    In most cases you can not use a calculated field in a calculated field of the same query.  The reason is that it ain't happened yet!

    You have to repeat the same calculation again in your second calculation like this --

    First_Cal: Field1 + Field2

    Second_Cal:  (Field1 + Field2) * Field3     --- You can not use    ((First_Cal) * Field3)

    Build a little, test a little.

    Was this answer helpful?

    0 comments