Editing a query based on a join

[Martynwheeler]

Just playing with putting together a UI for a student database (I have been helped tremendously so far on the design of the tables and relationships on another thread in the tables forum). I am now looking at the ui for entering and editing data. I thought I could run a query and edit the data in a field but because there are several joins it is not letting me edit the data.

I enclose a simplified version of what I am doing. I'd like to be able to change the mark field in this query

but it says the recordset is not updateable.

I can't see how i can build a nice clean datagrid view like this using a subform. The only thing i can think to do is create a temporary table to edit and then use the values to update the relevant table.

Am I missing something obvious.

Thank you

Martyn

 

[theDBguy]

Best practice is to base a form to a data source so you can make changes to the data one table at a time.

 

[tvanstiphout]

I did this with zero code. Select the Student table, and on the Create tab of the ribbon, click Form.
Design that form. Open it up a bit more vertically, and at the bottom drop Student_has_Class. A subform is created.
Change the ClassID control to a combobox, and set the Rowsource to: SELECT Class.class_id, Class.code FROM Class;
That's all. You can now enter students and their classes.

Of course in a real app you would use queries rather than tables for the RecordSource, but the important point here is: no joins. The form and subform are based on single tables (or queries on a single table).

 

[Martynwheeler]

View attachment 112320

I did this with zero code. Select the Student table, and on the Create tab of the ribbon, click Form.
Design that form. Open it up a bit more vertically, and at the bottom drop Student_has_Class. A subform is created.
Change the ClassID control to a combobox, and set the Rowsource to: SELECT Class.class_id, Class.code FROM Class;
That's all. You can now enter students and their classes.

Of course in a real app you would use queries rather than tables for the RecordSource, but the important point here is: no joins. The form and subform are based on single tables (or queries on a single table).

Thank you Tom. I was rather hoping for a more spreadsheet like look. I query students from a single class and get their test data and only want to edit their marks in a big list rather than moving through each record at a time, the rest of the data is read only. Maybe it is not possible and I will have to stick to spreadsheets.

 

[Pat Hartman]

The problem with your query is that it includes two parallel relationships.
Student-HasClass-Class
and
Student-HasAssessment

The assessment has nothing to do with the class. Having both relationships in the query is causing a Cartesian Product. The effect is relationship 1 is being multiplied by relationship 2 and that is what makes the join not updateable. I'll give you a silly example that will make the issue more clear. Students take classes and students have vehicles registered for parking.

John, Math, Ford
John, Science, Ford
Suzie, Math, Honda
Suzie, English, Honda
Suzie, Math, Harley
Suzie, English, Harley
Suzie, Math, Porsche
Suzie, English, Porsche

John has only one vehicle registered so there is no duplication. However, Suzie has a lot of rides so each of her rides is matched with each of her classes. This is the multiply effect. 2 Classes times 3 Vehicles = 6 rows in this case.

A query with Student-HasClass-Class would be updateable. I've attached a database that shows working with a m-m relationship from both directions. The Employee form works the way Tom's sample works. It has Employee as a main form with Classes he is taking as a subform. The other view, instead of using a form/subform, uses a form/popup form.

So, the question, you need to answer is WHY are you trying to process both paths in a single query?

Many-to-many example

I added a new version on 1/16/23 so choose that one rather than the old one unless you specifically want the old one. The old zip file contains both A97 and AXP versions of the database. It uses venue booking as the example and shows the relationship from both sides. A venue main form with...

www.access-programmers.co.uk

 

[Martynwheeler]

The problem with your query is that it includes two parallel relationships.
Student-HasClass-Class
and
Student-HasAssessment

The assessment has nothing to do with the class. A query with Student-HasClass-Class would be updateable. I've attached a database that shows working with a m-m relationship from both directions. The Employee form works the way Tom's sample works. It has Employee as a main form with Classes he is taking as a subform. The other view, instead of using a form/subform, uses a form/popup form.

So, the question, you need to answer is WHY are you trying to process both paths in a single query?

Many-to-many example

I added a new version on 1/16/23 so choose that one rather than the old one unless you specifically want the old one. The old zip file contains both A97 and AXP versions of the database. It uses venue booking as the example and shows the relationship from both sides. A venue main form with...

www.access-programmers.co.uk

I did notice I could edit a query of students and student_has_assessment but it became uneditable once I combined with the class table.

Maybe I am not understanding access. I would like to select (filter) only one class of students from a large number of students and edit just their marks. In order to do so I need to make reference to both the class and the assessment at the same time. Maybe I need to change the table design?

 

[Pat Hartman]

Maybe I am not understanding access.

It has nothing to do with Access. This is a concept issue with relational databases.

Maybe I need to change the table design?

If class is related to assessment, then the design is incorrect.

 

[Martynwheeler]

Okay, thank you. I think I need to redesign.

I had maybe separated things too far.

I thought students had many classes and classes having many students was unrelated to students having many assessments. But I guess the assessments are related to the classes because in order to take an assessment you must be part of a class.

 

[Martynwheeler]

Maybe this is a better design?

 

[Pat Hartman]

No, that design is not better. It has circular relationships and bad relationships. The relationships would be clearer if they used autonumber PKs for the junction tables. That way tests, instead of having the FK of courseID would have the FK of EnrollmentID. Scores should be embedded with tests unless you want to be able to take the same test multiple times. But in any case, TestScores has the FK that goes to tests and doesn't include StudentID or CourceID or CycleID at all. This is a reallllly bad design. Do not use it.

Technically, junction tables can use a compound PK which is the FK to one table and the FK to the other table. And I frequently leave it at that. But, some schemas have child tables for a junction table. When that happens, you would need to join on two fields. Technically legal but it gets awkward, especially when there are three tables in the junction. So, my rule is - if the junction table has child tables, then the PK of the junction table is a surrogate key (autonumber) BUT, I use a unique index to enforce the constraint that the Student can only be related to the course for this cycle ONCE. So since the junction table of students-courses has child tables, it has a PK that is an autonumber and a unique index for the StudentID + CourseID (you must make this index using the indexes dialog).

Basically, you have students and classes. Then you have the junction table that connects a student to a class. If there are assignments, tests, assessments, associated with that class, then they are not related to the student, they are related to the junction table which is this class for this student.

I don't know what Cycle means in that schema. It could be semester or school year. If you want to track courses over time, and group things by semester/schoolyear, then you need the Cycle concept because a student might need to take the same course a second time if he flunks it the first time so he would take it in cycle x and in cycle y.

 

[Martynwheeler]

No, that design is not better. It has circular relationships and bad relationships. The relationships would be clearer if they used autonumber PKs for the junction tables. That way tests, instead of having the FK of courseID would have the FK of EnrollmentID. Scores should be embedded with tests unless you want to be able to take the same test multiple times. But in any case, TestScores has the FK that goes to tests and doesn't include StudentID or CourceID or CycleID at all. This is a reallllly bad design. Do not use it.

Technically, junction tables can use a compound PK which is the FK to one table and the FK to the other table. And I frequently leave it at that. But, some schemas have child tables for a junction table. When that happens, you would need to join on two fields. Technically legal but it gets awkward, especially when there are three tables in the junction. So, my rule is - if the junction table has child tables, then the PK of the junction table is a surrogate key (autonumber) BUT, I use a unique index to enforce the constraint that the Student can only be related to the course for this cycle ONCE. So since the junction table of students-courses has child tables, it has a PK that is an autonumber and a unique index for the StudentID + CourseID (you must make this index using the indexes dialog).

Basically, you have students and classes. Then you have the junction table that connects a student to a class. If there are assignments, tests, assessments, associated with that class, then they are not related to the student, they are related to the junction table which is this class for this student.

I don't know what Cycle means in that schema. It could be semester or school year. If you want to track courses over time, and group things by semester/schoolyear, then you need the Cycle concept because a student might need to take the same course a second time if he flunks it the first time so he would take it in cycle x and in cycle y.

Thank you for your detailed answer. I will try and see how to model the relationship between classes and tests.
I will make sure I look at the junction fk pairs and enforce the unique constraint.

The cycle was to represent the academic year. I wish to be able to access results of tests from previous years so I thought I would need a reference to this in the class enrollment junction table.

Thank you again

 

[Pat Hartman]

Most schools, at least in the US, break down an academic year into semesters. Fall, Spring, Summer. So, you need two levels. AcademicYear and Semester. Each is a separate table. Then you have students, classes, and teachers.

You connect classes to a semester. You connect teachers to Class/Semester and that is a 1-m relationship. If you need to support multiple instances of a class for a semester, you should add a third level so you can assign a class to a meeting place. Then you would join teachers at this level rather than to the class/semester level

You connect Students to the Class/Semester or the Class/Semester/Room depending on which you use. This makes the junction table that is a parent to all the class assignments and test results.

We need a lot more information regarding how you intend to use this application because there are lots more tables required if your example you want to manage fees.

 

[tvanstiphout]

Thank you Tom. I was rather hoping for a more spreadsheet like look. I query students from a single class and get their test data and only want to edit their marks in a big list rather than moving through each record at a time, the rest of the data is read only. Maybe it is not possible and I will have to stick to spreadsheets.

I showed you a parent form with Student, and subform with classes.
Nothing prevents you from turning that around and make Class (beware: a Reserved Word) the parent form, and Student_has_Assessment the subform.

 

[Martynwheeler]

Most schools, at least in the US, break down an academic year into semesters. Fall, Spring, Summer. So, you need two levels. AcademicYear and Semester. Each is a separate table. Then you have students, classes, and teachers.

You connect classes to a semester. You connect teachers to Class/Semester and that is a 1-m relationship. If you need to support multiple instances of a class for a semester, you should add a third level so you can assign a class to a meeting place. Then you would join teachers at this level rather than to the class/semester level

You connect Students to the Class/Semester or the Class/Semester/Room depending on which you use. This makes the junction table that is a parent to all the class assignments and test results.

We need a lot more information regarding how you intend to use this application because there are lots more tables required if your example you want to manage fees.

Thank you Pat. I will take a step back and have a good think. The model in the UK is very different. Students are with us for two years at my college. All I am interested in is keeping track of the grades of pupils in the department that I manage (Science). Let me have a better think about stuff.

 

[Pat Hartman]

The two years is probably not one lump. How is it divided? It really doesn't matter what they are called or whether there are 2, 3, or 4 per year.

Are you using the application to just track grades? That simplifies the model.

Why not have your class learn Access with you. Surely if they are interested in pursuing a science degree, they would have occasion to collect data for a study somewhere along the way.

 

[Martynwheeler]

The two years is probably not one lump. How is it divided? It really doesn't matter what they are called or whether there are 2, 3, or 4 per year.

Are you using the application to just track grades? That simplifies the model.

Why not have your class learn Access with you. Surely if they are interested in pursuing a science degree, they would have occasion to collect data for a study somewhere along the way.

I am a subject lead. Students study with us for two years and do final exams. They move between classes as they move from one year to the next. We have a school management system that deals with the day to day. I want to input and track grades in the 12 classes of students of (at first just one subject but potentially more in the future).

I have had a good think about your comments and I think I have come up with a working system. Just need to test a few things. I will post it later and let you cast a critical eye over it.

Once again, thanks for the input