Multi-level GROUP BY Error 3612

[eliffman]

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;

[MGFoster]

-----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;

[eliffman]

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

[MGFoster]

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)

[eliffman]

I put the DSum into the query in place of the subquery. Works great.
Thanks.