Student Progress Database Application

It is working for the SELECT DISTINCT query and not for the SELECT query. 4 records are showing for each student for each course on the CenterCode.

We need to go back a bit. We went down the wrong road with the Append query. If you review my instructions, I said to add the Students and Courses table to a query without a join. Then to include the StudentID, CenterCode and CourseID in the query, Then to set the criteria to the CenterCode control from the main form.

Somewhere along the line you changed the query and I missed it. So we need to go back to that query and test it as a SELECT query.

SELECT CenterCode, StudentID, CourseID

FROM tblStudents, tblCourse

WHERE CenterCode=Forms![Production_CenterDetails_Main_Form]![CenterCode];

Test that query to make sure it has 4 records per student 1 for each course. If it doesn't then try it this way:

SELECT DISTINCT CenterCode, StudentID, CourseID

FROM tblStudents, tblCourse

WHERE CenterCode=Forms![Production_CenterDetails_Main_Form]![CenterCode];

There are some discrepancies in the data that is being stored in the tblStudentProgress table. Though I have corrected the append query and changed it show the tblStudents and tblCourse. Here's the SQL of the query:

INSERT INTO tblStudentProgress ( CenterCode, StudentID, CourseID, GradeMonth )

SELECT tblStudents.CenterCode, tblStudents.StudentID, tblCourse.CourseID, [Forms]![Production_CenterDetails_Main_Form]![Text37] AS Mth

FROM tblStudents, tblCourse;

However the way in which the data is being stored in tblStudentProgress is wrong. Take a look at this screenshot for the month of March:

There are gaps in the rows. And the month is not in the same row as the marks.  And this is what I filtered the month on:

While you can see the StudentID is 222 for the courses above, the screenshot below stores the month in some other StudentID:

These need to be corrected.

***Personal information deleted by the moderator. Please see the Microsoft Community Frequently Asked Questions for more information on how you can protect your privacy.***

I'm sorry, I missed something. That query is wrong.

You should be pulling from the STUDENT table and the Course table, NOT StudentProgress. You are Appending TO StudentProgress.. Also where did that WHERE clause come from?

You need to redo the Append query.

Here's the SQL of the Append Query with month entered as "June":

INSERT INTO tblStudentProgress ( CenterCode, StudentID, CourseID, GradeMonth )

SELECT tblStudentProgress.CenterCode, tblStudentProgress.StudentID, tblStudentProgress.CourseID, tblStudentProgress.[Forms]![Production_CenterDetails_Main_Form]![Text37] AS Mth

FROM tblStudentProgress, tblCourse

WHERE (((tblStudentProgress.[Forms]![Production_CenterDetails_Main_Form]![Text37])="June"));