total all records in a query

[dplove]

I have a table that has the field "Rate". I am creating a query where I want
to total the 90 records of the "rate" field. When creating my query I chose
the total and group by sum and get no results. All 90 records has a rate of
29.50 but I get no total for this. The Total should be $2655. What am I
doing wrong?

Help Please!

 

[S.Clark]

SELECT Sum(Rate) as RateSum from [tablename] Group By Sum(Rate)

Your SQL should look something like this.

 

[dplove]

I'm getting a syntax error: "check the subquery's syntax and enclose the
subquery in parentheses"

Here is what I have:
SELECT Sum(Rent) as RateSum from [PO 1189-616] Group By Sum(Rate)
Rent is the name of the field, PO 1189-616 is he name of the table.

SELECT Sum(Rate) as RateSum from [tablename] Group By Sum(Rate)

Your SQL should look something like this.

--
Steve Clark,
Former Access MVP
FMS, Inc
http://www.fmsinc.com/consulting

I have a table that has the field "Rate". I am creating a query where I want
to total the 90 records of the "rate" field. When creating my query I chose
the total and group by sum and get no results. All 90 records has a rate of
29.50 but I get no total for this. The Total should be $2655. What am I
doing wrong?

Help Please!

 

[dplove]

I have tried putting brackets around Rent and parentheses around [PO....] and
I'm still getting the same error message.

What is wrong?

I'm getting a syntax error: "check the subquery's syntax and enclose the
subquery in parentheses"

Here is what I have:
SELECT Sum(Rent) as RateSum from [PO 1189-616] Group By Sum(Rate)
Rent is the name of the field, PO 1189-616 is he name of the table.

SELECT Sum(Rate) as RateSum from [tablename] Group By Sum(Rate)

Your SQL should look something like this.

--
Steve Clark,
Former Access MVP
FMS, Inc
http://www.fmsinc.com/consulting

I have a table that has the field "Rate". I am creating a query where I want
to total the 90 records of the "rate" field. When creating my query I chose
the total and group by sum and get no results. All 90 records has a rate of
29.50 but I get no total for this. The Total should be $2655. What am I
doing wrong?

Help Please!

 

[dplove]

Here is what I have now and I still get the syntax error:
SELECT Sum([Rent]) as RateSum from [PO 1189-616] Group By Sum(Rent)

Can someone please let me know what is wrong, Thanks!

I have tried putting brackets around Rent and parentheses around [PO....] and
I'm still getting the same error message.

What is wrong?

I'm getting a syntax error: "check the subquery's syntax and enclose the
subquery in parentheses"

Here is what I have:
SELECT Sum(Rent) as RateSum from [PO 1189-616] Group By Sum(Rate)
Rent is the name of the field, PO 1189-616 is he name of the table.

SELECT Sum(Rate) as RateSum from [tablename] Group By Sum(Rate)

Your SQL should look something like this.

--
Steve Clark,
Former Access MVP
FMS, Inc
http://www.fmsinc.com/consulting



:

I have a table that has the field "Rate". I am creating a query where I want
to total the 90 records of the "rate" field. When creating my query I chose
the total and group by sum and get no results. All 90 records has a rate of
29.50 but I get no total for this. The Total should be $2655. What am I
doing wrong?

Help Please!

 

[dplove]

Can someone PLEASE help me????

Here is what I have now and I still get the syntax error:
SELECT Sum([Rent]) as RateSum from [PO 1189-616] Group By Sum(Rent)

Can someone please let me know what is wrong, Thanks!

I have tried putting brackets around Rent and parentheses around [PO....] and
I'm still getting the same error message.

What is wrong?

I'm getting a syntax error: "check the subquery's syntax and enclose the
subquery in parentheses"

Here is what I have:
SELECT Sum(Rent) as RateSum from [PO 1189-616] Group By Sum(Rate)
Rent is the name of the field, PO 1189-616 is he name of the table.

:

SELECT Sum(Rate) as RateSum from [tablename] Group By Sum(Rate)

Your SQL should look something like this.

--
Steve Clark,
Former Access MVP
FMS, Inc
http://www.fmsinc.com/consulting



:

I have a table that has the field "Rate". I am creating a query where I want
to total the 90 records of the "rate" field. When creating my query I chose
the total and group by sum and get no results. All 90 records has a rate of
29.50 but I get no total for this. The Total should be $2655. What am I
doing wrong?

Help Please!

 

[John W. Vinson]

Here is what I have now and I still get the syntax error:
SELECT Sum([Rent]) as RateSum from [PO 1189-616] Group By Sum(Rent)

This makes no sense. It sounds like you're grouping by the wrong field - you
can't group by the sum of Rent and within that group sum the Rent!

Might you be intending to group by some other field, perhaps the tenant ID?

John W. Vinson [MVP]

 

[John Spencer]

Perhaps what you want is

SELECT Sum(Rent) as RateSum from [PO 1189-616]

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

Here is what I have now and I still get the syntax error:
SELECT Sum([Rent]) as RateSum from [PO 1189-616] Group By Sum(Rent)

This makes no sense. It sounds like you're grouping by the wrong field - you
can't group by the sum of Rent and within that group sum the Rent!

Might you be intending to group by some other field, perhaps the tenant ID?

John W. Vinson [MVP]

 

[dplove]

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

The expression above worked it gave me the total. But it is putting the
total on each line per record. I just want 1 line to be the total. Is that
possible?

Perhaps what you want is

SELECT Sum(Rent) as RateSum from [PO 1189-616]

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

Here is what I have now and I still get the syntax error:
SELECT Sum([Rent]) as RateSum from [PO 1189-616] Group By Sum(Rent)

This makes no sense. It sounds like you're grouping by the wrong field - you
can't group by the sum of Rent and within that group sum the Rent!

Might you be intending to group by some other field, perhaps the tenant ID?

John W. Vinson [MVP]

 

[John Spencer]

Why do you want to do this? If you are going to use the results in a
report, it is simpler to do it there using the report's functionality.

IF you have to do it in a query, then you will need to use a union query

SELECT Rent, "Detail" as LineType
FROM [PO 1189-616]
UNION ALL
SELECT Sum(Rent) as RentSum, "Total" as LineType
FROM [PO 1189-616]
GROUP BY "Total"
ORDER BY LineType

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

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

The expression above worked it gave me the total. But it is putting the
total on each line per record. I just want 1 line to be the total. Is
that
possible?

Perhaps what you want is

SELECT Sum(Rent) as RateSum from [PO 1189-616]

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

On Fri, 14 Sep 2007 09:24:01 -0700, dplove
<[email protected]>
wrote:

Here is what I have now and I still get the syntax error:
SELECT Sum([Rent]) as RateSum from [PO 1189-616] Group By Sum(Rent)


This makes no sense. It sounds like you're grouping by the wrong
field - you
can't group by the sum of Rent and within that group sum the Rent!

Might you be intending to group by some other field, perhaps the tenant
ID?

John W. Vinson [MVP]

 

[dplove]

You are so right, didn't think about I am going to use reports and the
report's functionality is much simpler. Thanks for turning on the lightbulb
in my head.

Why do you want to do this? If you are going to use the results in a
report, it is simpler to do it there using the report's functionality.

IF you have to do it in a query, then you will need to use a union query

SELECT Rent, "Detail" as LineType
FROM [PO 1189-616]
UNION ALL
SELECT Sum(Rent) as RentSum, "Total" as LineType
FROM [PO 1189-616]
GROUP BY "Total"
ORDER BY LineType

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

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

The expression above worked it gave me the total. But it is putting the
total on each line per record. I just want 1 line to be the total. Is
that
possible?

Perhaps what you want is

SELECT Sum(Rent) as RateSum from [PO 1189-616]

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


John W. Vinson wrote:
On Fri, 14 Sep 2007 09:24:01 -0700, dplove
<[email protected]>
wrote:

Here is what I have now and I still get the syntax error:
SELECT Sum([Rent]) as RateSum from [PO 1189-616] Group By Sum(Rent)


This makes no sense. It sounds like you're grouping by the wrong
field - you
can't group by the sum of Rent and within that group sum the Rent!

Might you be intending to group by some other field, perhaps the tenant
ID?

John W. Vinson [MVP]