CODEWITHSUNDEEP
sqlms-accessms-access-2007
maxtwoknight
maxtwoknight

Reputation: 5346

Issues with query using Having clause in Access

I'm having some problems getting a query to run in MS Access 2007. I have the following query that works fine:

  SELECT boq.PIPE_AG, 
         boq.PIPE_UG, 
         boq.Pipe, 
         boq.Unit, 
         SUM(boq.Quantity) AS SumOfQuantity
    FROM [Total - BOQ] boq
GROUP BY boq.PIPE_AG, boq.PIPE_UG, boq.Pipe, boq.Unit
  HAVING boq.PIPE_AG In (-1,1) OR boq.PIPE_UG In (-1,1);

When I add boq.Pipe ='1' to the HAVING caluse like this:

HAVING boq.Pipe ='1' AND (boq.PIPE_AG In (-1,1) OR boq.PIPE_UG In (-1,1))

I get an error that says:

You tried to execute a query that does not include the specified expression 'boq.Pipe='1' And (boq.PIPE_AG In (-1,1) Or boq.PIPE_UG In (-1,1))' as part of an aggregate function.

I'm a bit perplexed as to why it's complaining. I do have all three fileds in the GROUP BY clause of my query. The query works fine if I change it up to use a WHERE clause, but I'm trying to understand why it's complaining when I add the Pipe field to the HAVING clause.

Upvotes: 1

Views: 1952

Answers (2)

Igor Turman
Igor Turman

Reputation: 2205

What version of MS Access do you use? I just replicated your exact table/query in my copy of Access 2007 and it worked perfectly fine (as expected). I assumed all your columns to be of Number type (long) and Pipe column to be of Text

Upvotes: 3

THEn
THEn

Reputation: 1936

Use WHERE clause instead of HAVING. You should use HAVING only with aggregate functions. For example you could use 

HAVING Sum(boq.Quantity) > 1000

that should do it

Upvotes: 5

Related Questions