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

Multi-level GROUP BY Error 3612

P: n/a
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
Share this Question
Share on Google+
4 Replies


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

P: n/a
Thanks. Do you mean to use DSum instead of the subquery or just as the
ControlSource for the report control?

Nov 13 '05 #3

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

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