Student Progress Database Application

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, right away making the changes and getting back to you in an hour.

Thank you.

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.

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, now I added an Unbound Textbox on the Main form and gave it a label of Month name. Then I went ahead and changed the query to reflect the CenterCode from the combo box like you said. Since I only see one query in my database, so I changed the query to the Center Code, if this is the query you are talking about.

"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."

I didn't understand this part above quite well. Are you talking about the same query that I added to reflect the Center Code wiz. Forms!MainFormname!Controlname which is the combo box? And where do you want me to add the column for the month ? Please clarify these in greater detail.

Thanks.