filter report by query row and column

[usmcgrunt]

Ok, I have a grades database with a main table that contains a list of all the students in our school. I have also made a query that contains all of the same information as the table. The fields of concern are:

StudentName
Classroom
Exam #1
Exam #2
Exam #3
Exam #4
Exam #5

For the sake of brevity, I am using "Exam" in place of the real exam name. There are actually 15 exams but for this question 5 will do.

What I am trying to do is create a form with a classroom combobox and an exam combobox that will allow the user to generate a grades report for all the students in a particular class for a particular exam.

I am moderately familiar with access and vba but I have never attempted to do something like this. Don't laugh if this is simple.

So far I have created the form with the necessary fields but I am at a loss as to how to create the report so that when I select a classroom and an exam and click a button to view the report I only see the students in that class in one column and there test score for the selected exam in the other column of the report detail.

I can easily write the code to filter the rows to only display the students in the selected classroom but I have no clue what code I need to only display the column that contains the test scores for the selected test.

Any and all help would be greatly appreciated.

Thanks, Sean

[orange]

Sean,

I'm not following exactly what you have.
For example , a form with a classroom combobox and an exam combobox that will allow the user to generate a grades report for all the students in a particular class for a particular exam.

The classroom (Class) is a Table of Students in that Class?
The Exam table contains what exactly?

Seems there is/should be a table (a junction table) between Student and Exam that has the mark/grade that student got on that Exam.

Perhaps you could tell us more, or show us a picture of your tables and relationships.

There is a model here concerning Students and Exams that may be useful.
http://www.databaseanswers.org/data_...s_physical.htm

[usmcgrunt]

Orange,

Thanks for the response. After looking at the link you provided, I might be creating more work for myself. I was attempting to make this database as simple as possible.

My database structure is as follows:

1 table that contains all the information.

Table Name: tblStudents
StudentName (243 students)
Classroom (16 available classrooms to choose from)
Exam #1 Score
Exam #2 Score
Exam #3 Score
.....
Exam #15 Score

Query Name: qryStudents
Contains all the same fields as tblStudents

form Name: frmGradeReport
recordsource: unbound
combobox 1 - cboClassroomSelect: a field list to select one of the 16 classrooms.
combobox 2 - cboExamSelect: a field list to select one of the 15 exams.
button Control - btnViewReport: view grades report filtered by class and exam.

Report Name: rptGradeReport
recordsource: qryStudents
detail section has studentName and exam score.

I am currently able to filter the report by classroom (using the combobox 1) so that I get a list of all the student in the class but I cannot figure out how to filter so that I only get the grades for the exam that I select in combobox 2.

If there is no easy solution to this problem I will like follow the instructions in the link that you provided. What do you think?

Thanks, Sean

[orange]

Sean,
The link I provided is a best guess for a generic student /exam/ results data structure.
It isn't the only answer, but if you have nothing else, it's a good place to start. But it may not fit your needs exactly, so some changes, additions etc may be necessary.

I can assure you that putting all the info into 1 table is working against you.

It is more important, for reuse and maintenance, to get your tables and relationships correct. You can "play" with a Form and its buttons and controls to make it real pretty, but if the table structures are not correct you're in deep doo-doo.

I don't know if you grasp concepts by reading, watching video or working thru examples but here are a few recommendations.

Reading:

This link
http://www.rogersaccesslibrary.com/forum/topic238.html

read the first 3 topics. Normalization, Normal Forms and the Entity Relationship Diagramming.
Download the Class Information Database. It has a number of steps leading to the structures.

Video:

http://www.accessmvp.com/strive4peace/
Crystal has some videos and lots of best practices type of information.


Working example:

There is a student database at microsoft. It's free to download.
http://office.microsoft.com/en-us/te...001225355.aspx

Good luck on whatever you decide.

[usmcgrunt]

Orange, again thank you for taking the time to respond to my post. I somehow figured out how to get it to work. What i did was make the exam grade field on the report unbound and then set its source using vba in the on load event.

the code looks something like this:

Me.gradedEvent.ControlSource = "=Avg([" & forms!frmGradeReport.cboGradedEvent & "])"

Now, the report shows the students in the classroom and the grade they received for the event selected in the graded event combobox on the form.

However, I am not satisfied with the database normalization as I expect it to grow in both form and function. So this is a short-term fix until I can further research all the links that you have provided.

Thanks for getting me going in the right direction.

Consider this post solved and closed.

Lighting fires not filling buckets.....

Sean