468,554 Members | 1,693 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,554 developers. It's quick & easy.

Multi-level GROUP BY Error 3612

I get the following error when trying to run a report: Multi-level
GROUP BY clause is not allowed in a subquery. (Error 3612)

The report's recordsource is a query based on a single table. However,
one of the fields in the query is a subquery. This field is also used
in the calculations for a few other fields in the query.

I'm able to run the report with no group levels. But the addition of
even one group level causes the error.

Any help would be greatly appreciated. The query's SQL can be found
below...

SELECT PaymentID, OfferingID, Date, PeriodStart, PeriodEnd, Payment,
(SELECT -Sum([tblPayments1.Payment]) AS NetInvCap
FROM tblPayments AS P1
WHERE P1.OfferingID = OfferingID AND P1.Date < Date AND
(P1.PaymentTypeID = 4 OR P1.PaymentTypeID = 6)) AS NetInvCap,
IIf(Nz([NetInvCap])>0,[Payment]/[NetInvCap],0) AS Return,
[PeriodEnd]-[PeriodStart]+1 AS Days,
(360/IIf([Days]>360,360,[Days]))*[Return] AS APR
FROM tblPayments;

Nov 13 '05 #1
4 3430
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Yeah. For some reason Report's don't "like" subqueries in the SELECT
clause of it's RecordSource. Try a DSum() instead or create a Public
VBA function that returns the same value as the subquery.

Also, shud not name columns w/ keyword "Date." If you do use Date as a
column name, be sure to delimit it w/ brackets [].

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

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

iQA/AwUBQgrz4IechKqOuFEgEQKUUwCgsn5wGNQPNoa7ipfNZAAr3h 9PXSsAoPhf
Y3wz33q7uPniVzy+AEVeXCyJ
=rdCd
-----END PGP SIGNATURE-----
eliffman wrote:
I get the following error when trying to run a report: Multi-level
GROUP BY clause is not allowed in a subquery. (Error 3612)

The report's recordsource is a query based on a single table. However,
one of the fields in the query is a subquery. This field is also used
in the calculations for a few other fields in the query.

I'm able to run the report with no group levels. But the addition of
even one group level causes the error.

Any help would be greatly appreciated. The query's SQL can be found
below...

SELECT PaymentID, OfferingID, Date, PeriodStart, PeriodEnd, Payment,
(SELECT -Sum([tblPayments1.Payment]) AS NetInvCap
FROM tblPayments AS P1
WHERE P1.OfferingID = OfferingID AND P1.Date < Date AND
(P1.PaymentTypeID = 4 OR P1.PaymentTypeID = 6)) AS NetInvCap,
IIf(Nz([NetInvCap])>0,[Payment]/[NetInvCap],0) AS Return,
[PeriodEnd]-[PeriodStart]+1 AS Days,
(360/IIf([Days]>360,360,[Days]))*[Return] AS APR
FROM tblPayments;

Nov 13 '05 #2
Thanks. Do you mean to use DSum instead of the subquery or just as the
ControlSource for the report control?

Nov 13 '05 #3
eliffman wrote:
Thanks. Do you mean to use DSum instead of the subquery or just as the
ControlSource for the report control?


I meant instead of the subquery. You can try it as a ControlSource -
see which runs fastest.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
Nov 13 '05 #4
I put the DSum into the query in place of the subquery. Works great.
Thanks.

Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

37 posts views Thread by ajikoe | last post: by
4 posts views Thread by Frank Jona | last post: by
5 posts views Thread by bobwansink | last post: by
17 posts views Thread by =?Utf-8?B?R2Vvcmdl?= | last post: by
2 posts views Thread by Aussie Rules | last post: by
reply views Thread by NPC403 | last post: by
1 post views Thread by UniDue | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.