Aggregate Function and Subqueries

  • Thread starter Thread starter Bill B.
  • Start date Start date
B

Bill B.

I am trying to run the query below and I keep getting the error message

The syntax of the sub query in this expression is incorrect. Check the
subquery's syntax and enclose the subquery in parentheses.

SELECT Sum([2003 Count]) as 2003 Total FROM (SELECT Count(claims.claim_id)
AS [2003 Count], claims.[Date of Injury], claims.Department
FROM claims
GROUP BY claims.[Date of Injury], claims.Department
HAVING (((claims.[Date of Injury]) Between #1/1/2003# And #1/1/2004#) AND
((claims.Department)="114511"))) as sub;

I can't find the syntax error TIA. Bill
 
I think a subquery can only return a single value. Your subquery has 3
fields returned. Either that or the 'as sub' at the end.
 
Thanks! It's been a while since I wrote a subquery. I forgot they can only
return a single value.

Bill

S.Clark said:
I think a subquery can only return a single value. Your subquery has 3
fields returned. Either that or the 'as sub' at the end.

Bill B. said:
I am trying to run the query below and I keep getting the error message

The syntax of the sub query in this expression is incorrect. Check the
subquery's syntax and enclose the subquery in parentheses.

SELECT Sum([2003 Count]) as 2003 Total FROM (SELECT Count(claims.claim_id)
AS [2003 Count], claims.[Date of Injury], claims.Department
FROM claims
GROUP BY claims.[Date of Injury], claims.Department
HAVING (((claims.[Date of Injury]) Between #1/1/2003# And #1/1/2004#) AND
((claims.Department)="114511"))) as sub;

I can't find the syntax error TIA. Bill
Click to expand...
Click to expand...
 
If what you posted was the entire query, then you could use something simpler
to get the desired result.

SELECT Count(claim_id) AS [2003 Count]
FROM claims
WHERE [Date of Injury] Between #1/1/2003# And #1/1/2004# AND Department="114511"

As far as I know, you cannot use a subquery in a from clause if you are
required to use square brackets in the subclause. You are required to use
square brackets if your field names have spaces (among other characters).
That is one reason that Table and field names should consist of only letters,
numbers, and the underscore character.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top