Student Progress Database Application

Hi,

I tried to get an understanding of the how Crosstab queries are done by looking at examples off the web. So I have the rows being Month, Center Code and StudentID and score in the columns. The only difficulty I'm having right now is creating a calculated field to sum all the 4 courses together, divide the total by 400 (aggregate of each course) and multiply the outcome by 100 to get the percentage marks (=([English]+[Hindi]+[Math]+[Science])/400*100) obtained by each student. There are no good videos to show how this can be done.

Please guide me.

First, create a query that returns the data you want to display in the report. You might want to start with a Crosstab query of the StudentProgress table with the rows being the Month, Center and StudentID and the Columns the Score. Then use that to join with other tables to get the Student name and course name.

Once you have a query displaying all the data you want, including any calculations, you can then use the Report Wizard to design your report.

Hello Scott,

Thanks for the explanation, its working now. I'd like to move ahead and now make a report with the marks of each student. I'd like the name of the month on top of the progress report with the Center Code and names of all the students belonging to the center and marks for each course. Finally, a percentage column in the report showing percentage aggregate each student got in their course.

Can you please guide me on how to design this?

This was working fine the last time I looked at it. What did you change?

Also you are not making sense here; "If you look at the second Sub form, the StudentProgress, immediately after you choose a Center from the drop down, you find names of students repeated 8 times instead of just 4." Are you referring to the CenterCode1 combobox at the top of the form? I just tried it and selecting a Center code from that control only changes focus of the form to that Center Code. I don't see any records added in the Second subform. The only time  you would get records added to the Second subform is if you press the Add progress Records button.

"there is really no point in having Combo boxes on the sub form when that feature is not usable and the user is not allowed to select anything" Actually there is a point. By using comboboxes you avoid having to use a multi-table query to display the data you want. Remember, what's stored in the table is the Student and Course ID values, not the text names. By using a combobox with the first column hidden, you are able to display the text value instead of the ID. If you want you can hide the drop down arrow by putting a rectangle control over just the arrow part.

Hi Scott,

Hope you had a great vacation. I definitely need some help on this application. I was away myself for a couple of weeks but now I'm back. Since the time I came back I worked on the application and tried to understand why its not working as it should. I downloaded a fresh copy of it from your OneDrive. If you look at the second Sub form, the StudentProgress, immediately after you choose a Center from the drop down, you find names of students repeated 8 times instead of just 4. I dont know where to begin to correct this. Plus, there is really no point in having Combo boxes on the sub form when that feature is not usable and the user is not allowed to select anything other than enter marks of the student.

Please give me a start on how I should go about solving this problem?

Thanks.