Student Progress Database Application

The second query is giving an error : qryProgress_Crosstab.Englishm qryProgress_Crosstab.Hindi, qryProgress_Crosstab.Math and qryProgress_Crosstab.Science is not a valid expression. This is the same error I was struggling with previously. Seems it doesn't like the subjects at all. Do you think we should include the CourseName instead of GradeScore for the percentage. I think I can fix that but the above error for expression needs to be fixed.

Frankly, I'm a little stumped. I don't really understand why the first query works and the second one doesn't. However, there is a work around. 

What I would do is create a table to be used temporarily to store the data used for the crosstab. 

You will then need to expand the code behind your Generate Report button:

Dim strSQL as String

strSQL = "DELETE * FROM tmpProgress;"

CurrentDB.Execute strSQL, dbFailOnError

strSQL = "INSERT INTO tmpProgress (CenterCode, StudentID, GradeScore, CourseName) " & _

               "SELECT CenterCode, StudentID, GradeScore, CourseName " & _

               "FROM qryStudentProgress_FIRST_SIMPLE_QUERY;"

CurrentDB.Execute strSQL, dbFailOnError

DoCmd.OpenReport "frmSimpleUsingCrosstabasSource", acViewPreview, , "CenterCode= " & Me.Centercode

I would create a new crosstab query using the temp table:

TRANSFORM Sum(tmpProgress.GradeScore) AS SumOfGradeScore

SELECT tmpProgress.CenterCode, tmpProgress.StudentID, Sum(tmpProgress.GradeScore) AS [Total Of GradeScore]

FROM tmpProgress

GROUP BY tmpProgress.CenterCode, tmpProgress.StudentID

PIVOT tmpProgress.CourseName;

I would also create the following query that would be the recordsource of the report:

SELECT qryProgress_Crosstab.StudentID, tblStudents.StudentName, qryProgress_Crosstab.CenterCode, qryProgress_Crosstab.English, qryProgress_Crosstab.Hindi, qryProgress_Crosstab.Math, qryProgress_Crosstab.Science, [total Of GradeScore]/4 AS Percentage

FROM tblStudents INNER JOIN qryProgress_Crosstab ON tblStudents.StudentID = qryProgress_Crosstab.StudentID;

Note: I don't think I have the expression for percentage correct. so you may need to change that. 

So with the temp table, the 2 new queries and the changes to the code, it should work. 

By the way, GradeScore should be a number datatype, not text.

Okay Scott, here's the file after compact and repair on my OneDrive : https://1drv.ms/u/s!AgqxHM1EYAf9g2PG8A4Fx5hgWjp7

called School Database copy. Hope you are able to access it.

Everything looks OK. There shouldn't be an issue with uploading the file. You can try zipping it after a Compact and Repair. or make a copy that only include the tables, forms. queries and report. And delete all but a smallsample of records

The RecordSource for the report is :

The code behind the generate report button is:

Private Sub Command41_Click()

DoCmd.OpenReport "frmSimpleUsingCrosstabasSource", acViewPreview, , "CenterCode= " & Combo15

End Sub