Re: syntax error in Total of field - I need help ASAP, PLease

The error message that I am getting when entering the expression is:
"check the subquery's syntax and enclose the subquery in parentheses".
What I'm trying to do is get a total of 1 field. The field has an amount of
$29.50 and there are 90 records, I need a total of the 90 records in the
query.

Here is what I have now as the expression and it gives me the same error
message.

"SELECT Rent, (SELECT Sum(Rent) FROM PO 1189-616) as Rent Sum
FROM PO 1189-616"

Rent is the field that I want to total from, PO 1189-616 is the tablename
and Rent Sum is the field I would like for it to be.

"John Spencer" wrote:

You keep saying something about a subquery in the error message. Are
you posting the entire SQL (query) statement?

IF you are trying to use a subquery in the select clause of a query then
you need to enclose the subquery in parentheses.

SELECT FieldA
, (SELECT Sum(FieldX) FROM SomeTable) as FieldB
FROM SomeTable


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


dplove wrote:
Can someone PLEASE help me with this problem????

"dplove" wrote:

No matter what expression I use I get the syntax error. It wants me close the
subquery in parentheses". I tried to use different combinations and it will
not accept the syntax.


"KARL DEWEY" wrote:

I suggest this --
SELECT ClientID, Sum(Rent) as RentSum FROM [PO 1189-616] GROUP BY ClientID

Otherwise you will have multiple sums without an identifier.

--
KARL DEWEY
Build a little - Test a little


"Michel Walsh" wrote:

You cannot group by on the aggregate. If you have NO group, use:

SELECT Sum(Rent) as RentSum FROM [PO 1189-616]

That will consider the whole table is a single group. That query returns
just one row.



If you have a group, make the expression making the group different than the
aggregate, like:

SELECT Sum(Rent) as RentSum FROM [PO 1189-616] GROUP BY ClientID


Hoping it may help,
Vanderghast, Access MVP




"dplove" <dplove@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C20400D2-94CE-4AAF-AD9D-920818C68F88@xxxxxxxxxxxxxxxx
I have the following expression for a total of a field has has 90 records
with each having a figure of 29.50 in them. I want to total this field
and
the syntax error is saying "check the subquery's syntax and enclose the
subquery in parentheses"
SELECT Sum(Rent) as RentSum from [PO 1189-616] Group By Sum(Rent)

where should the parathases go?





.

Relevant Pages

  • RE: Textbox control source headache
    ... When I put an equal sign I get a syntax error: "The syntax of the subquery in ... subquery in parentheses." ... If I include the equal sign and then enclose the rest of it in parentheses I ... control boxes' control sources don't we have to add some extra 's? ...
    (microsoft.public.access.forms)
  • Re: Expression using multiple conditions
    ... The syntax of the subquery in this expression is incorrect. ... subquery's syntax and enclose the subquery in parentheses. ...
    (microsoft.public.access.queries)
  • RE: expression question
    ... "The syntax of the subquery in this expression is incorrect. ... So I added the parentheses, ... but the query does not run. ...
    (microsoft.public.access.queries)
  • Re: word frequency counting
    ... the syntax? ... Would I put commas between like so?: ... You need to also enclose the entire range within a pair of ...
    (microsoft.public.excel.misc)
  • Date Query
    ... The syntax of the subquery in this expression is incorrect. ... Check te subquery's syntax and enclose the subquery in parentheses. ... John, I could hit a subquery with my car and not recognize it! ...
    (microsoft.public.access.queries)
Loading