Problem creating data entry form

[rb608]

I've solved what I thought were tougher problems that this, but the
seeming simplicity of the concept here has me stumped. Here's the
problem:

I've created a little database for my spouse, a school teacher. In
this database are simple tools for keeping track of student info and
their parents or guardians. There's a table for keeping track of the
various tests & assignments and their respective values and
weightings. I now need to create a form by which the test scores can
be entered.

Firstly, each student is identified by a unique StudentID autonumber
in the StudentInfo table. The various assignments are similarly
assigned a ScoreID autonumber in the ScoreItems table. I have another
StudentScores table with only the fields ScoreID, StudentID, and Score
in which I had planned to simply match the various student scores to
the various tests by their respective ID numbers.

Now, to make thinks simple for the spouse, I'd like to create a form
(and underlying operations) by which clicking on a specific test will
open a (continuous) form listing all of the students assigned to that
respective class (by a ClassID field), along with a blank field for
entering the grades. It sounded simple until I tried to do it.
Here's what I ran into (and, finally, the real question):

In order to create a data source that includes both the students'
whole names and the ScoreID's, I need to use inner joins in the query;
and I end up with a recordset that is not updatable, so I can't use
direct entry on the form.

Okay I think, when a test is selected, I'll generate a new table (Make
Table Query) containing the names of all students in the applicable
class, then use that table as the Data Source for the form. That hit
a snag because the Make Table Query will not allow me to create a
table that contains two AutoNumber fields (StudentID and ScoreID), but
both of these fields are necessary.

I thought I was being smart by setting up the data tables the way I
did, but now I'm re-thinking that. I'm only an advanced novice at
this stuff, so entirely open to the thought I'm overlooking something
simple. Any good ideas on how to facilitate this?

 

[Allen Browne]

The basic idea will be to execute an Append query statement that adds the
students and the ScoreID to the StudentScores table.

If every student must complete every assessment, that would be something
like this:

strSql = "INSERT INTO StudentScores ( StudentID, ScoreID ) " & _
"SELECT StudentInfo.StudentID, " & 99 & " AS ScoreID " & _
"FROM StudentInfo;"
dbEngine(0)(0).Execute strSql, dbFailOnError
Me.Requery

In practice, it probably needs a WHERE clause to ensure:
a) only the desired students are added, and
b) a student is not added more than once.

To achieve (a), the database will need to know which students need to
complete which assignments.

To achieve (b), you probably want to add a subquery to the WHERE clause.