By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,264 Members | 1,277 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,264 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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

Nov 12 '05 #2

P: n/a
-----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


Nov 12 '05 #3

P: n/a
>
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
Nov 12 '05 #4

P: n/a
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

Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.