Results 1 to 2 of 2
  1. #1
    Join Date
    May 2003
    Posts
    19

    Unanswered: Problem in a Totals Query

    My query uses some code for removing duplicates from a table. The field “Expr1” is the ID which identifies the tables that each of the records comes from.

    When I use the following code, Access says: “You tried to execute a query that does not include the specified expression Expr1 as part of an aggregate function.”

    How can I tell Access that I don’t want Expr1 to be changed at all?.

    If I try it as a totals QBE, it still won't work. Does anyone know the secret to get a field passed a totals query without stipulating “group” or “sum” or whatever, please?

    The code that isn’t working is:

    SELECT [qryUnionJM].[Expr1], [qryUnionJM].[ShortUrl], Sum([qryUnionJM].[SumOfHits]) AS SumOfSumOfHits
    FROM qryUnionJM
    GROUP BY [qryUnionJM].[ShortUrl]
    HAVING (((Sum(qryUnionJM.SumOfHits))>2));

  2. #2
    Join Date
    Aug 2002
    Posts
    29

    Re: Problem in a Totals Query

    It seems to me that you can either remove the [qryUnionJM].[Expr1] field or add it to the GROUP BY clause to get the results you are looking for...

    Code:
    SELECT [qryUnionJM].[ShortUrl], Sum([qryUnionJM].[SumOfHits]) AS SumOfSumOfHits 
    FROM qryUnionJM 
    GROUP BY [qryUnionJM].[ShortUrl] 
    HAVING (((Sum(qryUnionJM.SumOfHits))>2));
    OR

    Code:
    SELECT [qryUnionJM].[Expr1], [qryUnionJM].[ShortUrl], Sum([qryUnionJM].[SumOfHits]) AS SumOfSumOfHits 
    FROM qryUnionJM 
    GROUP BY [qryUnionJM].[ShortUrl], [qryUnionJM].[Expr1]
    HAVING (((Sum(qryUnionJM.SumOfHits))>2));

Posting Permissions

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