Student Progress Database Application

Ok the first subform is the student listing, correct? That should only be:

SELECT tblStudents.CenterCode, tblStudents.StudentID, tblStudents.StudentName, tblStudents.StudentGender, tblStudents.DOB FROM tblStudents;

In fact, the Recordsource need only be tblStudents.

The Second subform is where the grades should be and that should be just tblStudentProgress. No need for a SQL statement.  I said before that the filtering by Centercode is a function of the link between the Main form and subform, nothing else. 

But this is not what I asked for. I told you to create a query that includes tblStudentProgress and tblCourse. Filter that query for a specific CenterCode. That query should return a record for each student for each course. So if there are 4 courses, then there should be 4 records per student.

Okay, first things first, straight off, here's the SQL from the first sun form:

SELECT tblStudents.CenterCode, tblStudents.StudentID, tblStudents.StudentName, tblStudentProgress.Grade, tblStudents.StudentGender, tblStudents.DOB

FROM tblStudents LEFT JOIN (tblCenterDetails RIGHT JOIN tblStudentProgress ON tblCenterDetails.CenterCode = tblStudentProgress.CenterCode) ON tblStudents.StudentID = tblStudentProgress.StudentID;

The second sub form has the following SQL :SELECT tblStudents.CenterCode, tblStudents.StudentID, tblStudentProgress.CourseID, tblStudentProgress.Grade, tblStudentProgress.GradeMonth, tblCourse.CourseName

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

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

The above is the SQL for the 2 different sub forms. If these 2 are right, only then I would like to proceed further.

I'm adamant to learn and persevere through the process. Yes, your're right, I am inexperienced and a novice, but nobody ever came from above learning about everything there is to know., Everyone has learned the way I am learning now. But then, if I have experts like you around, I am determined to be guided by you and finish the application no matter how long it takes for me to get there. Let me go through what you just instructed and then I'll get back to you.

Ok, That paragraph outlines what needs to be done. It tells you that an Append query would be used to populate the table. 

To add the table without a join, just Add it from the table pane but don't join it to the student table. Like I said this is a step by step process and you should be concentrating on getting the first step right before thinking about the next steps. Once you get the first step right the next steps will become clearer. The first step is creating a query that returns a record for each student in the center for each course. Have you done that? If not what is not working? Show me the SQL.

"This is taking more time than I expected. "

Then your expectations were off. Developing an Access database is not a simple task, especially for the novice. There is a pretty steep learning curve for Access.  While an experienced developer could churn out your app in a couple of days, it may take weeks for the inexperienced.

Hi

I'm having an extremely hard time understanding your instructions in the last paragraph. I fiddled with the tables and queries the whole day and still cant do it right.

*Now, you can pre-populate all the records if you want.*What you want to do is create a query that returns all the student in a Center. Add the Course table without a join. This should give a listing of all the students with one record per course (so each student would have 4 records.) You would then add a column for the month and turn it into an Append query. When you run that query it should add a record so you can enter the marks.

Firstly, how would you populate the sub form with all the students from a particular query? This can be done when the user makes a choice from the center combo box.  The how would you add a Course table without a join to the query? Finally, like I said earlier, I did not want to add one record at a time for one student, I wanted the sub form to populate with students and then just go ahead and add the marks. This is all so confusing at this stage. This is taking more time than I expected. Do you want me to mail you the database and you have a look at it? If you do want to have a look, please let me know how to send it to you.

Thank you.