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.ApplyFilter , "Me.courseCode = '" &
Forms!frmSelectCourse!cboSelectCourse & "'"

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

....WHERE
(((studentsInCourses.courseCode)=[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!frmSelectCourse!cboSelectCourse
on the left side, and specify the data type on the right.

HTH

"Richard Hollenbeck" <ri****************@verizon.net> wrote in message
news:o5XTd.18050$uc.2699@trnddc09...

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.ApplyFilter , "Me.courseCode = '" &
Forms!frmSelectCourse!cboSelectCourse & "'"

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

...WHERE
(((studentsInCourses.courseCode)=[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.net> wrote in message news:o5XTd.18050$uc.2699@trnddc09... (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 (((studentsInCourses.courseCode)= _
[Forms]![frmSelectCourse]![cboSelectCourse] ))

(snip snip snip)
"MacDermott" <ma********@nospam.com> wrote in message
news:iC*************@newsread3.news.atl.earthlink. 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!frmSelectCourse!cboSelectCourse
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.net> wrote in message
news:xe0Ud.56253$uc.11359@trnddc04...

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

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

message

news:o5XTd.18050$uc.2699@trnddc09...

(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 (((studentsInCourses.courseCode)= _
[Forms]![frmSelectCourse]![cboSelectCourse] ))

(snip snip snip)
"MacDermott" <ma********@nospam.com> wrote in message
news:iC*************@newsread3.news.atl.earthlink. 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!frmSelectCourse!cboSelectCourse
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.net> wrote in message news:xe0Ud.56253$uc.11359@trnddc04...

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

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

message

news:o5XTd.18050$uc.2699@trnddc09... (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 (((studentsInCourses.courseCode)= _
>[Forms]![frmSelectCourse]![cboSelectCourse] ))

(snip snip snip)
"MacDermott" <ma********@nospam.com> wrote in message
news:iC*************@newsread3.news.atl.earthlink. 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********@nospam.com> wrote in message
news:Np****************@newsread2.news.atl.earthli nk.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 qryTableOfGrades:

PARAMETERS [Forms]![frmSelectCourse]![cboSelectCourse] Text (
255 );
TRANSFORM Sum(studentScores.score) AS SumOfscore
SELECT studentsInCourses.courseCode, courses.courseDescription,
[lName] & ", " & [fName] & " " & [StudentNumber] AS Student
FROM students INNER JOIN (((courses INNER JOIN groups ON
courses.courseCode = groups.courseCode) INNER JOIN
studentsInCourses ON courses.courseCode =
studentsInCourses.courseCode) INNER JOIN (activities INNER JOIN
studentScores ON activities.activityID =
studentScores.activityID) ON groups.groupID = activities.groupID)
ON (students.studentID = studentsInCourses.studentID) AND
(students.studentID = studentScores.studentID)
WHERE
(((studentsInCourses.courseCode)=[Forms]![frmSelectCourse]![cboSe
lectCourse]))
GROUP BY studentsInCourses.courseCode, courses.courseDescription,
[lName] & ", " & [fName] & " " & [StudentNumber]
ORDER BY studentsInCourses.courseCode
PIVOT activities.activityDescription;

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.net> wrote in message
news:0%fUd.63288$wc.59202@trnddc07...

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

:-) (see below)

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

message

news:xe0Ud.56253$uc.11359@trnddc04...

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

> "Richard Hollenbeck" <ri****************@verizon.net> wrote in message
> news:o5XTd.18050$uc.2699@trnddc09...
(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 (((studentsInCourses.courseCode)= _
>>[Forms]![frmSelectCourse]![cboSelectCourse] ))
(snip snip snip)
"MacDermott" <ma********@nospam.com> wrote in message
news:iC*************@newsread3.news.atl.earthlink. 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********@nospam.com> wrote in message
news:Np****************@newsread2.news.atl.earthli nk.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 qryTableOfGrades:

PARAMETERS [Forms]![frmSelectCourse]![cboSelectCourse] Text (
255 );
TRANSFORM Sum(studentScores.score) AS SumOfscore
SELECT studentsInCourses.courseCode, courses.courseDescription,
[lName] & ", " & [fName] & " " & [StudentNumber] AS Student
FROM students INNER JOIN (((courses INNER JOIN groups ON
courses.courseCode = groups.courseCode) INNER JOIN
studentsInCourses ON courses.courseCode =
studentsInCourses.courseCode) INNER JOIN (activities INNER JOIN
studentScores ON activities.activityID =
studentScores.activityID) ON groups.groupID = activities.groupID)
ON (students.studentID = studentsInCourses.studentID) AND
(students.studentID = studentScores.studentID)
WHERE
(((studentsInCourses.courseCode)=[Forms]![frmSelectCourse]![cboSe
lectCourse]))
GROUP BY studentsInCourses.courseCode, courses.courseDescription,
[lName] & ", " & [fName] & " " & [StudentNumber]
ORDER BY studentsInCourses.courseCode
PIVOT activities.activityDescription;

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.518805$Xk.120726@pd7tw3no...

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

[MacDermott]

When you say you "make this query the recordsource of the report", do you
mean that you have opened the report in design view and written (or
selected) the name of the report in the RecordSource property of the
property sheet?
Or are you somehow doing this programmatically?
I've often seen the "Too few parameters" error when Access couldn't resolve
a parameter, but never when the recordset wasn't being constructed
separately using DAO.

HTH

"Richard Hollenbeck" <ri****************@verizon.net> wrote in message
news:8x%Vd.74460$uc.57271@trnddc04...

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]

Yes, the query is the RecordSource property in the property
sheet. That's one way I tried it. On another occasion I
programatically set the Me.RecordSource property in the
Form_Load() subroutine, but that didn't help either so I took it
out, just leaving it in the property sheet. Still, I use a
DAO.Recordset to dynamically assign field names in the report.
Again, this works perfectly in the report without the parameter
in the query, but it returns zero records with the parameter.
The query runs perfectly from the database window with or without
the parameter.

"MacDermott" <ma********@nospam.com> wrote in message
news:Df*****************@newsread2.news.atl.earthl ink.net...

When you say you "make this query the recordsource of the report", do you mean that you have opened the report in design view and written (or selected) the name of the report in the RecordSource property of the property sheet?
Or are you somehow doing this programmatically?
I've often seen the "Too few parameters" error when Access couldn't resolve a parameter, but never when the recordset wasn't being constructed separately using DAO.

HTH

"Richard Hollenbeck" <ri****************@verizon.net> wrote in message news:8x%Vd.74460$uc.57271@trnddc04...

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."

[MacDermott]

Is this a form or a report?
You mostly talk about a report, but then you mention the Form_Load event.
I'm confused.
"Richard Hollenbeck" <ri****************@verizon.net> wrote in message
news:LXYWd.82706$uc.78755@trnddc04...

Yes, the query is the RecordSource property in the property
sheet. That's one way I tried it. On another occasion I
programatically set the Me.RecordSource property in the
Form_Load() subroutine, but that didn't help either so I took it
out, just leaving it in the property sheet. Still, I use a
DAO.Recordset to dynamically assign field names in the report.
Again, this works perfectly in the report without the parameter
in the query, but it returns zero records with the parameter.
The query runs perfectly from the database window with or without
the parameter.

"MacDermott" <ma********@nospam.com> wrote in message
news:Df*****************@newsread2.news.atl.earthl ink.net...

When you say you "make this query the recordsource of the

report", do you

mean that you have opened the report in design view and written

(or

selected) the name of the report in the RecordSource property

of the

property sheet?
Or are you somehow doing this programmatically?
I've often seen the "Too few parameters" error when Access

couldn't resolve

a parameter, but never when the recordset wasn't being

constructed

separately using DAO.

HTH

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

message

news:8x%Vd.74460$uc.57271@trnddc04...

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]

That's was a mistake. I work with more forms than reports. It
was probably a slip-up. I should have said something like
Report_Open.

"MacDermott" <ma********@nospam.com> wrote in message
news:Vu*****************@newsread2.news.atl.earthl ink.net...

Is this a form or a report?
You mostly talk about a report, but then you mention the Form_Load event. I'm confused.

[MacDermott]

OK, perhaps you can tell me more about this DAO recordset you're using...

"Richard Hollenbeck" <ri****************@verizon.net> wrote in message
news:TsBXd.31866$uc.19365@trnddc09...

That's was a mistake. I work with more forms than reports. It
was probably a slip-up. I should have said something like
Report_Open.

"MacDermott" <ma********@nospam.com> wrote in message
news:Vu*****************@newsread2.news.atl.earthl ink.net...

Is this a form or a report?
You mostly talk about a report, but then you mention the

Form_Load event.

I'm confused.

[Richard Hollenbeck]

In this report, "lblHeader" is the activities' names above the
columns and "txtData" is the scores.
Thanks for hanging in there with me! The code follows:

Private Sub Report_Open(Cancel As Integer)
Dim intColCount As Integer
Dim intControlCount As Integer
Dim i As Integer
Dim strName As String

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("qryTableOfGrades", dbOpenDynaset)

intColCount = rs.Fields.Count
intControlCount = Me.Detail.Controls.Count

If intControlCount < intColCount Then
intColCount = intControlCount
End If

' Fill in information for the necessary controls.
For i = 1 To intColCount

strName = rs.Fields(i - 1).Name
Me.Controls("lblHeader" & i).Caption = strName
Me.Controls("txtData" & i).ControlSource = strName
Next i

' Close the recordset.
rs.Close
End Sub

"MacDermott" <ma********@nospam.com> wrote in message
news:w9*****************@newsread2.news.atl.earthl ink.net...

OK, perhaps you can tell me more about this DAO recordset

you're using...