syntax error in error of Total of field

[Guest]

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
SELECT Sum(Rent) as RentSum from [PO 1189-616] Group By Sum(Rent)

where should the parathases go?

 

[Michel Walsh]

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

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
SELECT Sum(Rent) as RentSum from [PO 1189-616] Group By Sum(Rent)

where should the parathases go?

 

[Guest]

I have used your expression and I still get "check the subquery's syntax and
enclose the subquery in parentheses"

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

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?

 

[Guest]

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

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

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?

 

[Guest]

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.

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

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

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?

 

[Guest]

Can someone PLEASE help me with this problem????

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.

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

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




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?

 

[John Spencer]

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
'====================================================

Can someone PLEASE help me with this problem????

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.

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


:

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




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?

 

[Guest]

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.

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
'====================================================

Can someone PLEASE help me with this problem????

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.


:

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


:

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




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?

 

[Guest]

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

I got the above expression to work. It came back with a total but it put
the total on each line for each record. I want 1 line with the total on it,
is it possible?

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
'====================================================

Can someone PLEASE help me with this problem????

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.


:

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


:

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




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?

 

[John Spencer]

Table and field names with spaces must be surrounded with [] so they will
properly be interpreted. Personally I NEVER use names with spaces.

The correct syntax for what you posted would be

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

Also the use of non-alpha non-number characters will cause problems.
For instance 1189-616 would probably be interpreted as subtracting 616 from
1189 if it were a field name without the brackets around it.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

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.

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
'====================================================

Can someone PLEASE help me with this problem????

:

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.


:

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


:

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




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?