Unanswered: Using calculated field as criteria in query.
I am looking at totals of a particular field (MINUTES_LATE) according to certain time periods specified by another field (PERIOD_NO). To look at totals for the previous to current period I have created a new field (PREVIOUS) providing the results of the expression PERIOD_NO - 1. OK this gives the correct period no when the query is run (ie 10 when PERIOD_NO is 11). If, however, I use this new field (PREVIOUS) as criteria for Sum of MINUTES_LATE in a new query I am prompted for a parameter value for PREVIOUS. Anyone got any ideas?
Originally posted by Sticker
I think you need to do this in a 2nd query, ie past the values to another query and then do the sum.
I think I have already done that. PERIOD_NO, Sum of MINUTES_LATE and PREVIOUS are fields shown in first query (PREVIOUS being the calculated one) and then Sum of MINUTES_LATE and PERIOD_NO with PREVIOUS as criteria are fields in the second query.
OK here is the SQL for both queries. Ignore the fact that it doesn't mention the Sum Of MINUTES_LATE field in the second query as previously mentioned, I am trying to do it using a different field from a different table now.
SELECT [PERIOD_NO]-1 AS PREVIOUS, Sum(DATA_TABLE.MINUTES_LATE) AS SumOfMINUTES_LATE, DATA_TABLE.PERIOD_NO, Count(DATA_TABLE.REF_NO) AS CountOfREF_NO
GROUP BY [PERIOD_NO]-1, DATA_TABLE.PERIOD_NO;
SELECT HISTORICAL_TABLE.PERIOD_NO AS HISTORICAL_TABLE_PERIOD_NO, HISTORICAL_TABLE.HISTORICAL
GROUP BY HISTORICAL_TABLE.PERIOD_NO, HISTORICAL_TABLE.HISTORICAL