Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004
    Posts
    5

    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?

    Steve

  2. #2
    Join Date
    Feb 2004
    Location
    UK
    Posts
    246
    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!!!

    Regards
    John

  3. #3
    Join Date
    Apr 2004
    Posts
    5
    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!!!

    Regards
    John
    John,

    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.

    Steve

  4. #4
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Can you post the SQL from both queries? It could be just a typo.

  5. #5
    Join Date
    Apr 2004
    Posts
    5
    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.

    First query:
    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
    FROM DATA_TABLE
    GROUP BY [PERIOD_NO]-1, DATA_TABLE.PERIOD_NO;

    Second query:
    SELECT HISTORICAL_TABLE.PERIOD_NO AS HISTORICAL_TABLE_PERIOD_NO, HISTORICAL_TABLE.HISTORICAL
    FROM HISTORICAL_TABLE
    GROUP BY HISTORICAL_TABLE.PERIOD_NO, HISTORICAL_TABLE.HISTORICAL
    HAVING (((HISTORICAL_TABLE.PERIOD_NO)=[PREVIOUS_PERIOD]));

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •