473,378 Members | 1,390 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,378 software developers and data experts.

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 3720
-----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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

37
by: ajikoe | last post by:
Hello, Is anyone has experiance in running python code to run multi thread parallel in multi processor. Is it possible ? Can python manage which cpu shoud do every thread? Sincerely Yours,...
4
by: Frank Jona | last post by:
Intellisense with C# and a multi-file assembly is not working. With VB.NET it is working. Is there a fix availible? We're using VisualStudio 2003 Regards Frank
6
by: cody | last post by:
What are multi file assemblies good for? What are the advantages of using multiple assemblies (A.DLL+B.DLL) vs. a single multi file assembly (A.DLL+A.NETMODULE)?
6
by: Joe | last post by:
I have 2 multi-list boxes, 1 displays course categories based on a table called CATEGORIES. This table has 2 fields CATEGORY_ID, CATEGORY_NAME The other multi-list box displays courses based on...
4
by: mimmo | last post by:
Hi! I should convert the accented letters of a string in the correspondent letters not accented. But when I compile with -Wall it give me: warning: multi-character character constant Do the...
5
by: Shane Story | last post by:
I can seem to get the dimensions of a frame in a multiframe tiff. After selecting activeframe, the Width/Height is still really much larger than the page's actual dimensions. When I split a...
5
by: bobwansink | last post by:
Hi, I'm relatively new to programming and I would like to create a C++ multi user program. It's for a project for school. This means I will have to write a paper about the theory too. Does anyone...
17
by: =?Utf-8?B?R2Vvcmdl?= | last post by:
Hello everyone, Wide character and multi-byte character are two popular encoding schemes on Windows. And wide character is using unicode encoding scheme. But each time I feel confused when...
0
by: Sabri.Pllana | last post by:
We apologize if you receive multiple copies of this call for papers. *********************************************************************** 2008 International Workshop on Multi-Core Computing...
2
by: Aussie Rules | last post by:
Hi, I have a site that Iwant to either display my text in english or french, based on the users prefernces ? I am new to webforms, but I know in winforms, this is pretty easy with a resource...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.