-----Original Message-----
w said:
Hello again, I tried creating the report through wizard
and the same bad stuff happened again so this time I did
it from scratch. Everything was better except when I
added CountOfInsId in the details section, I got the error
message. SO I took it out and added Sum(CountOfInsId) in
the CourseID Footer and I also got the error message. Same
thing when Sum(CountOfInsId) was added in the report
footer. The error message was something like: "Multi-
level grouping is not allowed in a subquery." And I guess
since countOFInsId is from the subquery then that is
probably the problem.
Keep that original backup safe. It sounds like there was
some problems in it too. I'm concerned that whatever we do
will be confused or thwarted by the corruption or whatever
is causing the bizarre behavior. Did you try creating a
new, blank database and importing everything, yet? If you
get things to behave as expected, then we can proceed.
Back to original question. When you say you got the error
when you added Sum(CountOfInsId) to a footer, I interpret
that to mean a text box with the expression
=Sum(CountOfInsId)
If the report runs without that text box and won't run with
it, then (aside from the possible corruption) I think you've
confirmed my initial guess. Let's try this completely off
the wall, totally hokey workaround: Create a new table
named OneRow add a single field of any type, then save the
table, open it and add one record with any dummy value.
Next, create a new query based on the query with the
subquery and bring the * down to the field list. Now switch
to SQL view and change it so it looks like
SELECT thequery.* FROM thequery
UNION ALL
SELECT 1 ,2,3,4,5 FROM OneRow WHERE FALSE
Save the query, then open it and double check that you're
getting the same data as you do when you open "thequery" by
itself.
Finally, try changing the report's record source to this new
query and see how it runs with and without the Sum text box.
Keeping fingers crossed,--
Marsh
MVP [MS Access]
-----Original Message-----
w wrote:
Hi, Thank God, I had a backup copy of the database.
Infact, I made a backup of the backup and I tried
creating
the same query and the same report and the same problems
happened. I have a good copy of the database where the
report was at this level of the query:
SELECT Courses.courseID, Courses.level, Count
(Courses.cId)
AS CountOfcId, Sum(Courses.studcount) AS SumOfstudcount,
Count(Courses.InstId) AS CountOfInsId
FROM Courses
GROUP BY Courses.courseID, Courses.level;
This was before the subquery was added.
There is no problem with this part, the report opens and
I
did not attempt to modify this in any way. I am afraid
now if I try to modify it again that I may damage
another
mdb file. This may be a stupid question, but how would
I
compile the database? Do I still need to do that with
my
backup copy?
Frequemt backups are a GOOD THING ;-)
Don't do anything to a backup, if you do something, it
won't
be the backup any more. You can always experiment without
fear. Just make a copy of the database and play around
all
you want, you can't damage the one you're not using.
You need to get back to where you were before you started
getting the error message about the subquery. Put the
subquery back in and then make the other changes to the
report one at a time until you do get the error message.
The last change you made is the one I want to know about,
but I'll bet its something you added to the report's
Sorting
and Grouping.
To compile the code in a database, open any code module
and
use the Debug menu to select Compile Project, Compile and
Save All, or whatever it is in your version of access.
-----Original Message-----
w wrote:
Hello, I did what you asked me to do. However, I had
to
create another report based on the same query because
the
report I created would not open at all, not even in
design
view.
You can't open a report in design view??? This is not
good!
Did you get any kind of message?
You may have a corrupted mdb file. It would be best to
make a backup copy of the database before doing anything
else.
Try creating a new, blank database, set it's references
the
same as before and then import everything from the
problem
database. Immediately after the import finishes, do a
Compile, then close the db, reopen it and see if the
report
will open in design view.
The new report I created only opened up in
design
view and I got an error message with that.
Oh boy, now what??
What was the error message?
I removed all
of the sorting and grouping in the report and all of
the
aggregate fuctions like you said. The report then ran
fine but of course it was incomplete. I then added
just
the count and sum fields by dragging them from the
query
field list and that also ran fine. I would appreciate
it
if you could tell me what the next step is from this
point
on.
When I was talking about aggregate functions before, I
was
referring to text boxes that had controls source
expressions
such as =Count(*) or =Sum([field]), not calculated
fields
in the record source query.
Hold off on this problem because there is no next step
until
you resolve all those other issues.
--
Marsh
MVP [MS Access]
-----Original Message-----
Marshall Barton wrote:
I'm not prepared to go into that stuff yet. Don't
make
any
changes to the query until we know more about the
cause
of
the error message.
Please perform the experiment I ask you to run in my
previous response.
" . . . check the report (use a copy)
"and remove everything in Sorting and Grouping
"and any text boxes with aggregate functions and
"see if it runs and post back with the results.
w wrote:
Thank you for your response. I actually did add two
things to the query. I added this part seen
below "AND
X.level = Courses.level" I also added this to a
date
field in my query "Between [Enter Start Date] And
[Enter
End Date]." I tried redoing both the query and
report
again from scratch this morning and I am still
getting
the
same error message. When I removed the subquery
completely
the report ran fine. However, I really need to
process
that part for my report to be correct. Is there any
other
way that I can feed my report those other
requirements
without using a subquery? Or can the subquery be
modified
in some way?
Here is the basic explanation that I posted on the
site
before I received the query: " courses is the table
that
contains the information. CourseID is the title of
the
course like English or math etc. Level is like 101
102
etc. cId is the primary key for each record input
into
the system. InsId is the identifier for each
instructor
and studcount is the number of students in the each
class.
Basically I want to see how many courses were
taught,
how
many students in each class, and how many unique
instructors there were for each course. The result
of
my
query looks something like this:
CourseID Level CountOfcID SumOfstudcount
CountOfInsId
English 101 3 40 3
English 102 5 25 5
As you can see for the count of InsId in English
101,
I
get a value of 3 and I am also getting the
same number of instructors as there were courses
taught
(ie
countOfcID=3) when in reality there was one
instructor
for
all three courses. I want to be able to count the
unique
number of instructors within each set."
-----Original Message-----
w wrote:
Hello, I was given this query sometime last week
and
it
does exactly what I want it to do. I actually get
query
results and I also used this query for a report
and
it
gave me what I wanted.
SELECT Courses.courseID, Courses.level, Count
(Courses.cId)
AS CountOfcId, Sum(Courses.studcount) AS
SumOfstudcount,
(SELECT Count(*) FROM Instructors WHERE
Instructors.InstID IN
(SELECT InstID FROM Courses AS X WHERE X.CourseID
=
Courses.CourseID AND X.level=Course.level))
AS CountOfInsId
FROM Courses
GROUP BY Courses.courseID, Courses.level;
The problem is after a few days have passed I
tried
to
open the report and it will not open and I am
getting
the
following error message:
"Multilevel GROUP BY clause is not allowed in a
subquery"
How come I get results in my query, but I get this
error
message in the report. Also, why did it work when
I
used
it last week and not today?
Marshall Barton wrote:
As to the why last week and not this week, it's
probably
beause you changed the report to use something in
Sorting
and Grouping or you added a text box with an
aggregate
function (Count, Sum, etc). This is a complicated
issue
that happens because the complexity of the internal
query
Access generates to run your report has gone beyond
what
is
allowed.
To veryify that hypothesis, check the report (use a
copy)
and remove everything in Sorting and Grouping and
any
text
boxes with aggregate functions and see if it runs
and
post
back with the results.
.
.
