Student Progress Database Application

Ok, this is great. That is what I had hoped would happen (I expected it but wasn't 100% sure). We are doing this in steps, so I had to make sure that step 1 worked before we can proceed. 

So the next step is to make this more dynamic. You want to be able to run this query for any Center and any month. So the next step is to add an UNBOUND textbox on the Main form for the Month you want to add the records for. Then change the criteria in the query so it references the CenterCode from the main form. The Criteria should look like this:

=Forms!mainformname!controlname

where mainformname is the name of the main form and Controlname the name of the control that is bound to the CenterCode. One last thing is to add a column to query:

Mth: Forms!mainformname!controlname

where controlname is the name of the unbound control you just added for the month. 

Now when you run the query, it should show records for the Center currently being displayed on the main form with a field for the month.

Let me know once that is working and we go on to the final step.

Okay Scott. Thank you for the patience.

I forgot not to join the 2 tables so I have removed the join. Next, do you remember you told me to add a field for the marks (or score) for each student? I added a field for that but in the wrong table. I have corrected that as well.

Also I ran the query and the query is running well. It is displaying 4 records for each student based on the Center Code selected from the combo box.

However, I think this is only a select query and so I cannot add or append a record to the table. If everything seems to be okay, let us proceed.

Okay, right away making the changes and getting back to you in an hour.

Thank you.

First, Ok, the subforms are fixed and working. Now forget them for the moment. Now we have to concentrate on getting a set of records to Append to tblStudentProgress.

So, the SQL statement you posted has some issues. First, I told you NOT to join tblCourse to tblstudentProgress.. Second, what is CourseScore? If that is the score for the current period, then it is in the wrong table. It needs to be in tblStudentProgress.. I've said this all along. We had the table structure set, why did you change it?

Finally, I said the query should produce a record for each student for each course. Did you run the query? I'm sure it didn't so that should have told you something was not right.

Again, the first step is getting this query right. Make those changes and let me know if the query now works the way I said it should.

Okay, I've tried to create sub forms and quires according to your guidelines. The first sub-form's recordsource is only tblStudents.

The Second sub-form is where the tblStudentProgress. Both these 2 forms are filtered by CenterCode by the automatic link between them.

After this I created a query based on tblStudentProgress and tblCourse. Both these 2 sub-forms are filtered on the CenterCode selected from the combo box on the main form.

Here's the SQL of the query:

SELECT tblStudentProgress.CenterCode, tblStudentProgress.StudentID, tblStudentProgress.CourseID, tblStudentProgress.Grade, tblStudentProgress.GradeMonth, tblCourse.CourseName, tblCourse.CourseScore

FROM tblCourse INNER JOIN tblStudentProgress ON tblCourse.CourseID = tblStudentProgress.CourseID;

Now when I select a centercode from the combo box, the first sub-form filters the students from the center. I can now enter 4 records for each StudentID by just pressing the tab key and moving on to the next StudentID. Also I can select the grade month and CourseID. You also mentioned an append query.

Please explain the process of creating an append query once again.

Thank you.