Student Progress Database Application

Anonymous
2016-11-08T11:25:10+00:00

Hello everyone!

I want to make an application for keeping track of students’ progress marks each month of the year using Access 2010. Suppose I have 10 literacy centers in 10 different locations each with 35-40 students enrolled. I want to generate a monthly Access  report showing the progress of each student in each of the subjects taught.

I have designed forms for entering details of each center, instructor and student saved in separate tables. All I need to do is find a way to generate month-wise students’ progress reports for each of the 10 literacy centers to be handed over to the center manager.

What would be the best way this could be done using Access 2010?

Thank you.

~~Maneesh

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

139 answers

Sort by: Oldest
Most helpful Newest Oldest
  1. ScottGem 68,840 Reputation points Volunteer Moderator
    2017-01-01T13:43:03+00:00

    Show me the SQL! I need to see the SQL for the first query that shows the repeating records.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2017-01-01T13:55:36+00:00

    Here's the SQL for the first query:

    TRANSFORM Sum(tblStudentProgress.GradeScore) AS SumOfGradeScore

    SELECT tblStudentProgress.CenterCode, tblStudentProgress.StudentID, tblStudents.StudentName

    FROM tblStudents INNER JOIN (tblCourse INNER JOIN tblStudentProgress ON tblCourse.CourseID = tblStudentProgress.CourseID) ON tblStudents.StudentID = tblStudentProgress.StudentID

    GROUP BY tblStudentProgress.CenterCode, tblStudentProgress.StudentID, tblStudents.StudentName

    PIVOT tblCourse.CourseName;

    Was this answer helpful?

    0 comments
  3. ScottGem 68,840 Reputation points Volunteer Moderator
    2017-01-01T14:07:52+00:00

    And this query produces multiple records? Try this:

    SELECT tblStudentProgress.CenterCode, tblStudentProgress.StudentID, tblStudentProgress.CourseID, tblStudentProgress.GradeScore

    FROM tblStudentProgress

    WHERE CenterCode = Forms!formname!CenterCode AND GradeMonth = Forms!formname!GradeMonth

    ORDER BY StudentID, CourseID;

    This should give you 4 records per student. One for each subject. If that works, add in the table to get the subject name. instead of the ID.

    If that works, then use that query as the source of your crosstab, which should get you 1 record per student.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2017-01-01T16:16:06+00:00

    Wait a minute, before even delving into this, I thought you first told me to create a crosstab query and then use that as the source for another simple select query. Now, I see you reversed the process and created a Simple Select query first and using that as a source for a Crosstab query? I'm confused.

    Was this answer helpful?

    0 comments
  5. ScottGem 68,840 Reputation points Volunteer Moderator
    2017-01-01T17:03:20+00:00

    Yes I did, but I also told you to create a query that filters for the records you want and then use that as the source for the crosstab.

    This is a concept I call "interim" queries. Sometimes you need to build several queries to get to the final result set that you need. I also told you, that you need to work through this progression of queries to determine where the problems lie. If you determine that the Crosstab is producing duplicate rows, then you need to do something to filter the rows before you use a crosstab.

    So there is no reversal here. There is simply analyzing the queries and making adjustments to get the result set you want.

    Was this answer helpful?

    0 comments