Student Progress Database Application

Okay I changed the query into an append query, and the SQL is like so:

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

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

FROM tblStudents, tblCourse

WHERE (((tblStudents.CenterCode)=[Forms]![Production_CenterDetails_Main_Form]![CenterCode]));

and this output gives this:

There is still something wrong and needs to be fixed before we move on.

Something is not right. If you followed my instructions correctly then you shoukd not have duplicates like that.

You can upload your file to my OneDrive and I will take a look and see if I can spot what is wrong.

Hi Scott,

Here's the link to the database I've uploaded: https://1drv.ms/u/s!Aix108ZSfLMKhZ4OHy366HGDYxgnJA on OneDrive.  The database is known as Students Database. Please let me know what was not working.

Thank you.

OK, I found the problem and it wasn't your fault. Everything was set right, but there was some weirdness in that the query was reading the Text control for the month as a number rather than a text string. So it wasn't adding the Month value to the table. I fixed that by converting the value to a string. And then everything worked.

Also you will note that I entered the Month (for Center 1 & 12) as June 2016. You should enter Both Month and Year to distinguish from one year to the next.

I also made a couple of other changes. DOB did not belong in the Progress table it should only be in the Student table. 

I set the controls in the second subform so they can't be edited except for the score. So the user can just type in the score, hit enter and it will go to the next row to enter the next score. 

I also modified the code behind the Add Progress Records button so it doesn't display any warning messages.

So I reuploaded to My OneDrive. 

Note: I'm leaving on vacation today, so I probably won't be able to respond as quickly for the next week.

But I think you understand more of how this works now.

Everything was set right, but there was some weirdness in that the query was reading the Text control for the month as a number rather than a text string.

Sounds like a good example of why parameters should always be declared, other than when testing for OR <parameter> IS NULL to make them optional.   In such cases a declared parameter can't evaluate to NULL, except where declared as DATETIME.  Date parameters are different from other data types in this respect and can be NULL, so should always be declared to avoid their inadvertently being interpreted as arithmetical expressions and thus returning the wrong values.