Report problem with GROUP BY and SUM() => Error 3612

[Hans]

Hi,

I want to create a report.
The query looks like this:
SELECT A.Name, Sum(A.Hours) AS FROM A
GROUP BY A.Name;

Now I want in my report to have a last line with the total hours from
all. It doesn't work with an simple SUM-Text-filed, I will see an
error message (3612) that this is nor possible with GROUP BY.

Is it possible to generate with SQL a total sum?
Greets
Hans

[DFS]

"Hans" <ha*****@hotmail.com> wrote in message
news:1e**************************@posting.google.c om...

Hi,

I want to create a report.
The query looks like this:
SELECT A.Name, Sum(A.Hours) AS FROM A
GROUP BY A.Name;

Now I want in my report to have a last line with the total hours from
all. It doesn't work with an simple SUM-Text-filed, I will see an
error message (3612) that this is nor possible with GROUP BY.
Hmmm. You should be able to drop a Sum(NameOfSumFieldFromQuery) control on
the report and have it work OK.

Another option: In the Total control on the report, set the ControlSource =
DSum("Hours","Table")

Is it possible to generate with SQL a total sum?
Greets
Hans

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Not w/ JET (Access) SQL.

First off, your query is incorrect. It should be like this:

SELECT A.Name, Sum(A.Hours) AS TotHrs
FROM A
GROUP BY A.Name;

You can create a totals text box on a Report (we are talking about an
Access Report object, aren't we?) in the report footer that has a
ControlSource of:

=Sum(TotHrs)

HTH,

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP/zaY4echKqOuFEgEQI+AACg9M5z4OZ8/swJNxRwVkSWt6BEx2oAmwWF
k4rLpNlQdhg6v7IOq/w3Sc4E
=pXxN
-----END PGP SIGNATURE-----
Hans wrote:

Hi,

I want to create a report.
The query looks like this:
SELECT A.Name, Sum(A.Hours) AS FROM A
GROUP BY A.Name;

Now I want in my report to have a last line with the total hours from
all. It doesn't work with an simple SUM-Text-filed, I will see an
error message (3612) that this is nor possible with GROUP BY.

Is it possible to generate with SQL a total sum?
Greets
Hans

[Hans]

>

You can create a totals text box on a Report (we are talking about an
Access Report object, aren't we?) in the report footer that has a
ControlSource of:

=Sum(TotHrs)

The Problem is, that this is not possible, because of the error 3612.
You can't have a textbox with a sum in the footer, when you have an
SQL group by statement.
greets
Hans

[DFS]

"Hans" <ha*****@hotmail.com> wrote in message
news:1e**************************@posting.google.c om...

You can create a totals text box on a Report (we are talking about an
Access Report object, aren't we?) in the report footer that has a
ControlSource of:

=Sum(TotHrs)

The Problem is, that this is not possible, because of the error 3612.
You can't have a textbox with a sum in the footer, when you have an
SQL group by statement.

Hans,

Not sure what you're trying to do.

You can definitely have a textbox with a sum in the report footer (and group
footers) of a report having a GROUP BY query as its recordsource.

Are you trying to use a GROUP BY query as the control source for the sum
textbox?


greets
Hans