Complex queries & reports

[Darryl]

Greetings,
I have a complex query defined. Here is the sql:

SELECT (Select count(*) from NewClientCaseMasterTable WHERE
((([NewClientCaseMasterTable].[EntryDate]) Between
[Forms]![printnewcase]![text0] And [forms]![printnewcase]![text2])) ) AS
[Total New Cases], [sourcecode], count([sourcecode]) AS coderecs,
format(count(sourcecode)/(select count(*) from NewClientCaseMasterTable
WHERE ((([NewClientCaseMasterTable].[EntryDate]) Between
[forms]![printnewcase]![text0] And [forms]![printnewcase]![text2]))),
"PerCent") AS [Per Cent Breakdown]
FROM NewClientCaseMasterTable
WHERE ((([NewClientCaseMasterTable].[EntryDate]) Between
[forms]![printnewcase]![text0] And [forms]![printnewcase]![text2]))
GROUP BY [sourcecode];

I can put this as the data source for a report with no problem. The issue
becomes if I put anything in the page header, page footer, report header,
etc. If I do, Iget an error:
"Multi-Level Group by clause not allowed in a subquery".

If I run the query stand alone, it works fine. If I have it on a report as
the datasource, and nothing but static text is in the page header,footer and
report header it works fine. If I put ANY variable in those sections I get
the error.

Anybody know why ? Or how to get around this ?

thanks,
Darryl

 

[Duane Hookom]

Try this VERY slow alternative that use DCount() rather than a subquery.
Carriage returns added for visual only...
SELECT
DCount("*", "NewClientCaseMasterTable",
"[EntryDate] Between #" & [Forms]![printnewcase]![text0] &
"# And #" & [forms]![printnewcase]![text2] & "#" )
AS [Total New Cases],
[sourcecode], count([sourcecode]) AS coderecs,
format(count(sourcecode)/
DCount("*", "NewClientCaseMasterTable",
"[EntryDate] Between #" & [Forms]![printnewcase]![text0] &
"# And #" & [forms]![printnewcase]![text2] & "#" ),
"PerCent") AS [Per Cent Breakdown]
FROM NewClientCaseMasterTable
WHERE [EntryDate]) Between
[forms]![printnewcase]![text0] And [forms]![printnewcase]![text2]
GROUP BY [sourcecode];