Grouped Summary Report

[hmayasi]

Hello All,

I have a database with thousands of student records. What I would like to do is create a report that adds the number of students that tested and compares it to the number passed and give me a percentage. When I create the report it always presents me with all the students listed. I don't need to see the student info, I just want a one page report to give me the summary of the numbers I need. How do I go about doing that. Also, how do you create a report based on a query? I can't seem to get that to work properly.

Thanks for any help.

 

[jal]

Hello All,

I have a database with thousands of student records. What I would like to do is create a report that adds the number of students that tested and compares it to the number passed and give me a percentage. When I create the report it always presents me with all the students listed. I don't need to see the student info, I just want a one page report to give me the summary of the numbers I need. How do I go about doing that. Also, how do you create a report based on a query? I can't seem to get that to work properly.

Thanks for any help.

Not sure where the problem is. The general format for such a query (assuming you have a table called TestResults) is this:

SELECT COUNT(StudentID) as NumStudents, (SELECT Count(Passed) FROM TestResults WHERE Passed = True) as Passes,
NumStudents - Passes as Failures, 100 * Passes/NumStudents as Percent_Of_Students_That_Passed
FROM TestResults

Then save the query (I pasted this query into sql view and then saved it under the name qryStudents).
Then go to the report wizard, select your query from the dropdown ("qryStudents") and select all the columns. Click finish.

I'm just a beginner, but that should get you started.

 

[jal]

I just wanted to add that you make your query a Make-Table query, that is, output your results into a new table by using INTO keyword.

SELECT .....

INTO NewTable
FROM TestResults

Then you can use NewTAble as your report (much the same way as I described above)

 

[hmayasi]

Thanks a lot jal. that really helped. I have another question, in the following statement:
SELECT COUNT(StudentID) as NumStudents, (SELECT Count(Passed) FROM TestResults WHERE Passed = True) as Passes,
NumStudents - Passes as Failures, 100 * Passes/NumStudents as Percent_Of_Students_That_Passed
FROM TestResults
I only need the system to count those with a special score code. I tried doing countif, but it wasn't recognised. and I tried doing: SELECT COUNT([score_code="S") as NumStudents, but it didn't take it either. Do you have any suggestions on how to do that?

 

[jal]

As for this new criteria you are now adding to the query, the solution might be simple if it applies to the whole query. That is to say, I am assuming that you want this:

- The total count of students will include only those with score code "S".
- The total count of passes will include only those with score code "S".
- The total count of failures will include only those with score code "S".

If so, this would make things pretty uniform, so I should think that a simple WHERE clause should do the trick, something like this:

SELECT....
FROM.....
WHERE score_code = "S"

If the query is more complicated than that (i.e. less uniform), let us know.

 

[jal]

Just to be clear

SELECT COUNT(StudentID) as NumStudents, (SELECT Count(Passed) FROM TestResults WHERE Passed = True AND score_code = "S") as Passes,
NumStudents - Passes as Failures, 100 * Passes/NumStudents as Percent_Of_Students_That_Passed
FROM TestResults
WHERE score_code = "S"