Help database setup please!

[clzhou]

Hi all,

I'm a new user to MS Access and I'm trying to create a database to record the grades of students in different subjects.

The final product I'd like to have is a form that displays a particular student's grades in every subject.

I have created 3 tables: tblStudent, tblSubjects, tblGrades
The relationships have been shown as attached picture. tblGrades acts as a link between student and subjects.

I've also attached a screenshot of an example of tblStudents, with data entered for each of the 5 subjects.

My questions:

1. When adding a new student, is there a way to automatically populate all subjects to him/her? ie. instead of me typing the subject IDs, all existing IDs would populate to the new student

2. If a new subject is added, how do I copy it to each and every student?



3. In the form view (please see attached), how do I create a subtotal field that calculates the total score for the student for the particular filter showing? (eg. total for science subjects, total for humanities, etc)

Thanks heaps in advance!

[June7]

1 and 2 require VBA code.

3. Can't do subtotals. Can do a total for each column. Assuming this form is in Datasheet view, place cursor in in any row of the form, click the Totals button (looks like sigma) on the Home tab of ribbon. Under each column select the calc function. Build a report to show subtotals on groups.

[clzhou]

thanks for the reply June7.
I suspected that VBA might be required, but I can't really do much more than open queries/forms with VBA codes. Would you point me in a direction as to what codes?

Would Append Queries work? I've tried it but always get the error message: MS Access cannot convert the data type.

1 and 2 require VBA code.

3. Can't do subtotals. Can do a total for each column. Assuming this form is in Datasheet view, place cursor in in any row of the form, click the Totals button (looks like sigma) on the Home tab of ribbon. Under each column select the calc function. Build a report to show subtotals on groups.

[June7]

Simple VBA to add new record would be like:
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO tablename (StudentID, Subject) VALUES(" & Me.studentID & ", 'Algebra')"
DoCmd.SetWarnings True

In your case this gets more complicated because of the multiple subjects. Because there are only 5, not hundreds, have a RunSQL for each of the subjects. The real trick is figuring out what event to put the code in - maybe a button Click.

Copying records for a new subject to each student is even more complicated. One way is to open in VBA a recordset of students, in a loop, read each record to get student ID and execute RunSQL. Or might be possible by a single SQL statement with nested query and the looping of recordset would not be needed.

Apostrophe delimiters are needed for text values, if date use #, nothing for numbers.

An Append query to add subtotal lines into table? No, do NOT EVER do this. Build a report.

[clzhou]

Thanks for the reply June. I've worked out how to do this without VBA. It's a combination of Make-Table query and Append Query.

1. Use Make table query to merge tblStudents and tblSubjects into tblJoint. This will create every combination of student and subjects
2. Use Append query to add the new combinations into the existing table tblGrades

I can present the data in a Form with subform, with a button click to run the queries and simple VBA command to turn off the warnings.