Desperately Seeking Help Filtering a Dynamic Crosstab Query Report

[Richard Hollenbeck]

I tried to ask this question before on the 14th of January but I never got a
reply. I'm still struggling with the problem. I'll try to rephrase the
question:

I have a crosstab query with rows of students and columns of activities and
the data are the students' scores in each activity. No problem, almost.
The problem is that there are five classes at the moment and will be more
classes (or courses) in future semesters. I don't want all the classes to
show up on the same report. I can filter the query but the report won't
recognize the filtered query. I could post the entire code, but the filter
inside the report would look like this:

DoCmd.ApplyFilt er , "Me.courseC ode = '" &
Forms!frmSelect Course!cboSelec tCourse & "'"

or, in the filtered version of my query, my condition looks like this:

....WHERE
(((studentsInCo urses.courseCod e)=[Forms]![frmSelectCourse]![cboSelectCourse]
))

This WHERE condition works fine in the query but my report doesn't like it.
The report works fine in the unfiltered version of my query, but it shows
every class, thus creating empty columns.

In all the examples I've seen of dynamic crosstab query reports all over the
web I've never seen one that was filtered. Could it be that this type of
report just doesn't like filtering? Any ideas?

Many thanks in advance,

Rich Hollenbeck

[MacDermott]

Crosstab queries like explicit parameters.
Open your query in design view, then click Query - Parameters.
In the dialog box that pops up, put
Forms!frmSelect Course!cboSelec tCourse
on the left side, and specify the data type on the right.

HTH

"Richard Hollenbeck" <ri************ ****@verizon.ne t> wrote in message
news:o5XTd.1805 0$uc.2699@trndd c09...

I tried to ask this question before on the 14th of January but I never got a reply. I'm still struggling with the problem. I'll try to rephrase the
question:

I have a crosstab query with rows of students and columns of activities and the data are the students' scores in each activity. No problem, almost.
The problem is that there are five classes at the moment and will be more
classes (or courses) in future semesters. I don't want all the classes to
show up on the same report. I can filter the query but the report won't
recognize the filtered query. I could post the entire code, but the filter inside the report would look like this:

DoCmd.ApplyFilt er , "Me.courseC ode = '" &
Forms!frmSelect Course!cboSelec tCourse & "'"

or, in the filtered version of my query, my condition looks like this:

...WHERE
(((studentsInCo urses.courseCod e)=[Forms]![frmSelectCourse]![cboSelectCourse] ))

This WHERE condition works fine in the query but my report doesn't like it. The report works fine in the unfiltered version of my query, but it shows
every class, thus creating empty columns.

In all the examples I've seen of dynamic crosstab query reports all over the web I've never seen one that was filtered. Could it be that this type of
report just doesn't like filtering? Any ideas?

Many thanks in advance,

Rich Hollenbeck

[Richard Hollenbeck]

(replies moved to bottom-posting (see below))

"Richard Hollenbeck" <ri************ ****@verizon.ne t> wrote in message news:o5XTd.1805 0$uc.2699@trndd c09... (snip)

I have a crosstab query with rows of students and columns of activities and the data are the students' scores in each activity. (snip)I can filter the query but the report won't recognize the filtered query.
(snip)
WHERE (((studentsInCo urses.courseCod e)= _
[Forms]![frmSelectCourse]![cboSelectCourse] ))

(snip snip snip)
"MacDermott " <ma********@nos pam.com> wrote in message
news:iC******** *****@newsread3 .news.atl.earth link.net... Crosstab queries like explicit parameters.
Open your query in design view, then click Query - Parameters.
In the dialog box that pops up, put
Forms!frmSelect Course!cboSelec tCourse
on the left side, and specify the data type on the right.

HTH

Thanks. Yes, I already did that. As I said, the query works.
It's just when I try to use the query in a form that I get into
trouble. For example, I currently have 216 students in the
students table. But all those students aren't in the same class.
There are currently five classes over two semesters. When I
change the RecordSource of the report to the version of the query
which doesn't have the parameters, I get records for all 216
students. When I run that query as a query I get the same
answer. But when I run the query with the parameters as a query
on a selected course with, say 30 students in it, I get the
correct records for the currently selected class, 30. When I go
back to the report and change the RecordSource back to the query
WITH the parameters, the report will run, and it will recognize
that it has the correct number of fields, etc., but it will
produce zero, not 30 records for the same selected class. The
only difference between the two version of this query is one has
parameters and the other doesn't. Should I post my query and the
report's code?

Thanks.

Rich Hollenbeck

[MacDermott]

Sure, post the SQL for the query.

BTW, is the form open when you run the report?
"Richard Hollenbeck" <ri************ ****@verizon.ne t> wrote in message
news:xe0Ud.5625 3$uc.11359@trnd dc04...

(replies moved to bottom-posting (see below))

"Richard Hollenbeck" <ri************ ****@verizon.ne t> wrote in

message

news:o5XTd.1805 0$uc.2699@trndd c09...

(snip)

I have a crosstab query with rows of students and columns of activities and the data are the students' scores in each activity. (snip)I can filter the query but the report won't recognize the filtered query.
(snip)
WHERE (((studentsInCo urses.courseCod e)= _
[Forms]![frmSelectCourse]![cboSelectCourse] ))

(snip snip snip)
"MacDermott " <ma********@nos pam.com> wrote in message
news:iC******** *****@newsread3 .news.atl.earth link.net...

Crosstab queries like explicit parameters.
Open your query in design view, then click Query - Parameters.
In the dialog box that pops up, put
Forms!frmSelect Course!cboSelec tCourse
on the left side, and specify the data type on the right.

HTH

Thanks. Yes, I already did that. As I said, the query works.
It's just when I try to use the query in a form that I get into
trouble. For example, I currently have 216 students in the
students table. But all those students aren't in the same class.
There are currently five classes over two semesters. When I
change the RecordSource of the report to the version of the query
which doesn't have the parameters, I get records for all 216
students. When I run that query as a query I get the same
answer. But when I run the query with the parameters as a query
on a selected course with, say 30 students in it, I get the
correct records for the currently selected class, 30. When I go
back to the report and change the RecordSource back to the query
WITH the parameters, the report will run, and it will recognize
that it has the correct number of fields, etc., but it will
produce zero, not 30 records for the same selected class. The
only difference between the two version of this query is one has
parameters and the other doesn't. Should I post my query and the
report's code?

Thanks.

Rich Hollenbeck

[Richard Hollenbeck]

(replies moved to bottom-posting (I just read a big lecture about
bottom posting))

:-) (see below)

"Richard Hollenbeck" <ri************ ****@verizon.ne t> wrote in message news:xe0Ud.5625 3$uc.11359@trnd dc04...

(replies moved to bottom-posting (see below))

"Richard Hollenbeck" <ri************ ****@verizon.ne t> wrote in

message

news:o5XTd.1805 0$uc.2699@trndd c09... (snip)

> I have a crosstab query with rows of students and columns

of activities

> and the data are the students' scores in each activity.

(snip)

>I can filter the query but the report won't recognize the

filtered query.
(snip)

> WHERE (((studentsInCo urses.courseCod e)= _
>[Forms]![frmSelectCourse]![cboSelectCourse] ))

(snip snip snip)
"MacDermott " <ma********@nos pam.com> wrote in message
news:iC******** *****@newsread3 .news.atl.earth link.net...

Crosstab queries like explicit parameters...

(snip) HTH

Thanks. Yes, I already did that. As I said, the query

works. It's just when I try to use the query in a form that I get into trouble. For example, I currently have 216 students in the
students table. But all those students aren't in the same class. There are currently five classes over two semesters. When I
change the RecordSource of the report to the version of the query which doesn't have the parameters, I get records for all 216
students. When I run that query as a query I get the same
answer. But when I run the query with the parameters as a query on a selected course with, say 30 students in it, I get the
correct records for the currently selected class, 30. When I go back to the report and change the RecordSource back to the query WITH the parameters, the report will run, and it will recognize that it has the correct number of fields, etc., but it will
produce zero, not 30 records for the same selected class. The only difference between the two version of this query is one has parameters and the other doesn't. Should I post my query and the report's code?

Thanks.

Rich Hollenbeck

"MacDermott " <ma********@nos pam.com> wrote in message
news:Np******** ********@newsre ad2.news.atl.ea rthlink.net... Sure, post the SQL for the query.

BTW, is the form open when you run the report?

Yes, frmSelectCourse is always open. It's like a switchboard
that gets the ball rolling. The teacher cannot enter or view
grades unless he or she first selects a course. That way I can
have Access always refer to this combo box containing the course
code. When this form closes, the application exits. Here's my
SQL for this query. The query is called qryTableOfGrade s:

PARAMETERS [Forms]![frmSelectCourse]![cboSelectCourse] Text (
255 );
TRANSFORM Sum(studentScor es.score) AS SumOfscore
SELECT studentsInCours es.courseCode, courses.courseD escription,
[lName] & ", " & [fName] & " " & [StudentNumber] AS Student
FROM students INNER JOIN (((courses INNER JOIN groups ON
courses.courseC ode = groups.courseCo de) INNER JOIN
studentsInCours es ON courses.courseC ode =
studentsInCours es.courseCode) INNER JOIN (activities INNER JOIN
studentScores ON activities.acti vityID =
studentScores.a ctivityID) ON groups.groupID = activities.grou pID)
ON (students.stude ntID = studentsInCours es.studentID) AND
(students.stude ntID = studentScores.s tudentID)
WHERE
(((studentsInCo urses.courseCod e)=[Forms]![frmSelectCourse]![cboSe
lectCourse]))
GROUP BY studentsInCours es.courseCode, courses.courseD escription,
[lName] & ", " & [fName] & " " & [StudentNumber]
ORDER BY studentsInCours es.courseCode
PIVOT activities.acti vityDescription ;

Thanks.

[Bri]

Richard,

You don't have to be so anal about bottom posting. If the guy that is
trying to help you prefers to top post, then let him, don't go moving
his stuff around.

Also, if you are going to bottom post the be sure to SNIP the previous
message to just what it pertinent to your post. Otherwise, its a huge
scroll down to get to where the new stuff is. This is a particular pain
for those that read this through Google where when a message is too
long, you have to go to another link to see the bottom post.

--
Bri
Richard Hollenbeck wrote:

(replies moved to bottom-posting (I just read a big lecture about
bottom posting))

:-) (see below)

[MacDermott]

So -
you say the query runs directly from the database window correctly?
But the report based on that same query returns no records?
How are you invoking the report?
Directly from the database window?
Through code from your interface?
If this latter, please post the code that calls the report.
"Richard Hollenbeck" <ri************ ****@verizon.ne t> wrote in message
news:0%fUd.6328 8$wc.59202@trnd dc07...

(replies moved to bottom-posting (I just read a big lecture about
bottom posting))

:-) (see below)

"Richard Hollenbeck" <ri************ ****@verizon.ne t> wrote in

message

news:xe0Ud.5625 3$uc.11359@trnd dc04...

(replies moved to bottom-posting (see below))

> "Richard Hollenbeck" <ri************ ****@verizon.ne t> wrote in message
> news:o5XTd.1805 0$uc.2699@trndd c09...
(snip)
> > I have a crosstab query with rows of students and columns of activities
> > and the data are the students' scores in each activity.
(snip)
>>I can filter the query but the report won't recognize the
filtered query.
(snip)

> > WHERE (((studentsInCo urses.courseCod e)= _
>>[Forms]![frmSelectCourse]![cboSelectCourse] ))
(snip snip snip)
"MacDermott " <ma********@nos pam.com> wrote in message
news:iC******** *****@newsread3 .news.atl.earth link.net...
> Crosstab queries like explicit parameters... (snip) > HTH

Thanks. Yes, I already did that. As I said, the query works. It's just when I try to use the query in a form that I get into trouble. For example, I currently have 216 students in the
students table. But all those students aren't in the same class. There are currently five classes over two semesters. When I
change the RecordSource of the report to the version of the query which doesn't have the parameters, I get records for all 216
students. When I run that query as a query I get the same
answer. But when I run the query with the parameters as a query on a selected course with, say 30 students in it, I get the
correct records for the currently selected class, 30. When I go back to the report and change the RecordSource back to the query WITH the parameters, the report will run, and it will recognize that it has the correct number of fields, etc., but it will
produce zero, not 30 records for the same selected class. The only difference between the two version of this query is one has parameters and the other doesn't. Should I post my query and the report's code?

Thanks.

Rich Hollenbeck

"MacDermott " <ma********@nos pam.com> wrote in message
news:Np******** ********@newsre ad2.news.atl.ea rthlink.net...

Sure, post the SQL for the query.

BTW, is the form open when you run the report?

Yes, frmSelectCourse is always open. It's like a switchboard
that gets the ball rolling. The teacher cannot enter or view
grades unless he or she first selects a course. That way I can
have Access always refer to this combo box containing the course
code. When this form closes, the application exits. Here's my
SQL for this query. The query is called qryTableOfGrade s:

PARAMETERS [Forms]![frmSelectCourse]![cboSelectCourse] Text (
255 );
TRANSFORM Sum(studentScor es.score) AS SumOfscore
SELECT studentsInCours es.courseCode, courses.courseD escription,
[lName] & ", " & [fName] & " " & [StudentNumber] AS Student
FROM students INNER JOIN (((courses INNER JOIN groups ON
courses.courseC ode = groups.courseCo de) INNER JOIN
studentsInCours es ON courses.courseC ode =
studentsInCours es.courseCode) INNER JOIN (activities INNER JOIN
studentScores ON activities.acti vityID =
studentScores.a ctivityID) ON groups.groupID = activities.grou pID)
ON (students.stude ntID = studentsInCours es.studentID) AND
(students.stude ntID = studentScores.s tudentID)
WHERE
(((studentsInCo urses.courseCod e)=[Forms]![frmSelectCourse]![cboSe
lectCourse]))
GROUP BY studentsInCours es.courseCode, courses.courseD escription,
[lName] & ", " & [fName] & " " & [StudentNumber]
ORDER BY studentsInCours es.courseCode
PIVOT activities.acti vityDescription ;

Thanks.

[Richard Hollenbeck]

Thanks, MacDermott, for your help. I'm sorry about moving your
posts to the bottom. I didn't mean to be anal, as somebody just
suggested, but I had recently been severely flamed about top
posting and I was attempting to correct the error of my ways.

You are exactly correct. The query runs correctly from the
database window, but when I make this query the recordsource of
the report, it returns no records. The main way I invoke this
report is by a menu item, which directly opens the report without
any filtering. Even when I open the report from the database
window there is no difference. I'm now getting an error that
says "Too few parameters. Expected 1."

[Richard Hollenbeck]

Bri,

Your point is well taken. Thank you. I'm still learning about
this stuff and I seem to get in trouble no matter what I do. I
can see that changing somebody's message around can be
offensive--especially when he's only trying to help me. I
recently began thinking about top and bottom posting issues and
I'm not yet set in my ways. I don't mean to be anal about it.
Please forgive my ignorance.

Rich Hollenbeck

"Bri" <no*@here.com > wrote in message
news:VSnUd.5188 05$Xk.120726@pd 7tw3no...

Richard,

You don't have to be so anal about bottom posting. If the guy that is trying to help you prefers to top post, then let him, don't go moving his stuff around.

Also, if you are going to bottom post the be sure to SNIP the previous message to just what it pertinent to your post. Otherwise, its a huge scroll down to get to where the new stuff is. This is a particular pain for those that read this through Google where when a message is too long, you have to go to another link to see the bottom post.

--
Bri
Richard Hollenbeck wrote:

(replies moved to bottom-posting (I just read a big lecture about bottom posting))

:-) (see below)

[Bri]

Richard,

No offense taken or intended. Just feedback to help you.

I don't understand why people get so wound up about the top/bottom
posting issue, but they do. There are pluses and minuses to both
methods. I just thought that I would point out that what you were doing
was sort of 'biting the hand that feeds you'. Also that if you are going
to bottom post, make sure that the quoted material above your post is
small enough that people will be able to find your bottom post.

--
Bri

Richard Hollenbeck wrote:

Bri,

Your point is well taken. Thank you. I'm still learning about
this stuff and I seem to get in trouble no matter what I do. I
can see that changing somebody's message around can be
offensive--especially when he's only trying to help me. I
recently began thinking about top and bottom posting issues and
I'm not yet set in my ways. I don't mean to be anal about it.
Please forgive my ignorance.

Rich Hollenbeck