MS Access table design question

Question

Friends,

I am working with a .accdb file and am working on changing some things. As it stands, it is for student's scores for some drills. We have a series of tables, tblRoster, tblScores, and tblCourses. Relationships are setup for the tables to one another. Each student has a unique ID for them we will call ID. This ID is the primary key for tblRoster and tblScores. TblScores only contains the columns "ID", "CourseID", and "Score", and is blank by default. I've designed a query which pulls all the students and all the courses into one page, so we can see ID, name, course number, course name, and score.

The issue I'm having is it is giving me the "record set is not updateable" in the query. I suspect with two primary keys in the columns it is giving me a problem. I want to be able to add the entries for the score so it will update the tblScores table. As it is currently, the data set is built now by typing in the ID and course number one at a time, then the score. The goal is to be able to pull it up, and have the fields "ID" and "CourseID" filled, with the "Score" entry to be filled in. In the event I have 100 drills and 40 students, that would be 4,000 rows.

Edit: I've added AutoNumber as a field name in tblScores, tblCourses, and tblRoster. Because of the relationships, I couldn't change the PK for the last, but the prior two PKs were changed to the new AutoNumber.

Thank you so much for any insight.

Answer 1

It is coming from both. I think it may have something to do with the keys. I'm going to fiddle with the fields in the table to see if I can see why. It may have something to do with that. Thanks!

Answer 2

There are certain design principles for relational database applications in general, and Access in particular, which are well understood and recommended.

Duane and Scott have mentioned some of those, including the use of a main form/sub form design.

A second one is the use of multiple-table queries for data entry. Almost always a less than desirable approach. One should reserve multiple table queries for reporting, where the goal is to pull together a lot of data for a summary view that doesn't need to be updateable.

The exact reason the query is not updateable is not particularly important, in fact. What matters is that this query design itself is simply inappropriate for data entry. One can dig into the "why" of it and probably gain important insight into how SQL works, of course. But the immediate point is that until you become a master of SQL, a much better design approach is the good, old-fashioned, "KISS principle". One form, bound to a one-table query, for data entry. A subform, bound to a second one-table query for the related table. Boring, but effective.

Answer 3

We have a series of tables, tblRoster, tblScores, and tblCourses. Relationships are setup for the tables to one another. Each student has a unique ID for them we will call ID. This ID is the primary key for tblRoster and tblScores. TblScores only contains the columns "ID", "CourseID", and "Score", and is blank by default.

...

Edit: I've added AutoNumber as a field name in tblScores, tblCourses, and tblRoster. Because of the relationships, I couldn't change the PK for the last, but the prior two PKs were changed to the new AutoNumber.

First, I think you have been confused by the use of non descriptive field names. You CANNOT have the unique Student ID as the PK for both tblRoster and tblScores. If you did you could only enter one score per student. I always name my PKs as tablenameID. So tblRoster should have the PK RosterID and tbleScores should have the PK ScoreID. And these should be autonumbers. Your tblScores should have the structure:

tblScores

ScoreID (PK Autonumber)

RosterID (FK)

CourseID (FK)

Score

Second, You should not be entering data into a query. You should be using a form and it should be a maifform/subform. The mainform should be bound to tblRoster and the subform to tblScores and linked on StudentID. The subform, should have 2 visible controls. A combobox to select the CourseID and a textbox to enter the score. By linking on StudentID that will be automatically populated and the Autonumber ScoreID will also be automatic, so you don't need to make those controls visible.

This is pretty much standard design of tables and forms.

Answer 4

It would help if you provided the SQL view of the query.

I would create a main form using the courses as the record source. Then create a sub form based solely on the TblScores. Use a combo box to select the student and a text box to enter the score. The Link Master/Child properties will automatically supply the CourseID.

Answer 5

When you say record set is not updateable, where exactly is the error coming from ? The query or the form?