Filtering recordset using field not in the recordset (1 Viewer)

[SurreyNick]

I’m not even sure I’m asking the question the right way but hopefully you’ll get the gist.

I have a form (pic attached) from which tutors can alter the details of tests that students are scheduled to take. The subform recordset shows only those students enroled on a test where there are no test results. Tutors can select individual students in the subform and from the boxes and buttons on the main form can change the scheduled date, change the exam paper or cancel the test altogether. All very straightforward stuff, but the problem I have is I can’t include the classID in the query that makes up the recordset of the subform because if I do the recordset becomes not updateable and that means I then can’t use the YN check box to select students.

If there are only a few students in the recordset the absence of the classID isn’t a problem, but if there are lots it will be hard to distinguish which students are in which class and this will cause problems for the tutors. I would like to add a “select all students in class” option too.

The classID is in my Classes_Students table and this table also contains a studentID field which is present in my recordset but as have explained I can’t add this table to the query that provides the data for my subform.

Is there a way to filter a recordset using a field not included in the recordset or within the underlying tables that make up the recordset?

Perhaps I’m just approaching this all wrong and there’s a better way to go about the whole thing.

I’d welcome some thoughts or solutions.

 

[MajP]

Can you provide the query SQL string? The easy solution is to make the query editable. I mentioned this earlier that your relationships are all correct, but you cannot include all of the joins in a query without tweaking to ensure it is editable. I know I had to modify some queries to make them editable.

 

[CJ_London]

so basically you want the classiD/name to appear in the subform?

three possible ways
change the subform recordset type to dynaset - inconsistent updates. Might work, might not, depends on how complex your query is.
use dlookup or a subquery in your recordsource to get the classID/Name
use a combox or listbox in your subform to display the classID/Name

advisable to make the control enabled/locked so users can still filter/sort on it but not able to change it - particularly for the first option

 

[SurreyNick]

Can you provide the query SQL string? The easy solution is to make the query editable. I mentioned this earlier that your relationships are all correct, but you cannot include all of the joins in a query without tweaking to ensure it is editable.

This is the SQL string for the query which is not updateable.

SELECT tblClasses.ClassRef, tblStudents.FullName, TestsNoResultsQ.TestID, tblExamPapers.ExamPaperRef, TestsNoResultsQ.TestDate, TestsNoResultsQ.[Select]
FROM (tblExamPapers INNER JOIN (tblStudents INNER JOIN TestsNoResultsQ ON tblStudents.StudentID = TestsNoResultsQ.StudentID) ON tblExamPapers.ExamPaperID = TestsNoResultsQ.ExamPaperID) INNER JOIN (tblClasses INNER JOIN tblClasses_Students ON tblClasses.ClassID = tblClasses_Students.ClassID) ON tblStudents.StudentID = tblClasses_Students.StudentID;

You'll spot there is another query that forms part of this query. This is an outer join query between the Tests and Results table to determine which students are enroled on a test but for whom there are no results. The SQL string for this query is:

SELECT tblTests.StudentID, tblTests.ExamPaperID, tblTests.TestID, tblTests.TestDate, tblTests.[Select]
FROM tblTests LEFT JOIN tblResults ON tblTests.TestID = tblResults.TestID
WHERE (((tblResults.TestID) Is Null));

I don't think it's the joins between tables but a factor of the one-to-many relationship between the Classes_Students and the other tables that is the root of the problem. However, my knowledge isn't good enough to be confident of that assertion, so I have attached the relevant tables and queries. The query I'd like to use to populate the subform is called NotUpdateableQ.

N.

 

[SurreyNick]

so basically you want the classiD/name to appear in the subform?

Ideally yes, but as long as I can filter the subform records by the classID that's sufficient. I'm not conversant with dynasets so I'll go away and study to see if this provides a possible solution. Thanks.
N.

 

[CJ_London]

storing a select field is generally not a good idea - what if more than one user is selecting at the same time?

You've also not set any relationships so difficult to work out what's what. However since you have not set them up - it is the joins, basically the query is too complex to be updateable. So suggest remove the two tables from your query and use a combo or listbox on your form with a rowsource of

SELECT tblStudents.StudentID, tblClasses.ClassRef
FROM (tblStudents INNER JOIN tblClasses_Students ON tblStudents.StudentID = tblClasses_Students.StudentID) INNER JOIN tblClasses ON tblClasses_Students.ClassID = tblClasses.ClassID; and the controlsource set to studentID. Make sure your set the locked property to yes

 

[SurreyNick]

storing a select field is generally not a good idea - what if more than one user is selecting at the same time?

Very good point. I don't foresee a problem in this instance because the Db is for my son and just for his Biology department. Each tutor is responsible for their own cohorts of students so the chance of any one student's records being worked on by two different people at the same time is very slim. Nevertheless I take the point on board and if there's an alternative method of selecting student records which avoids the need for a select field I would be glad to hear of it.

You've also not set any relationships so difficult to work out what's what.

Oops! I exported the tables and queries to a temp Db and forgot to add the joins back in. Sorry. I have attached a pic which shows the joins that do exist in the actual Db. I had to delete a ton load of records to export the tables and wasn't careful enough when doing so and so it now won't be possible to enforce referential integrity between the Tests and Results tables because I left some records in the Results table without corresponding parent records in the Tests table.

....basically the query is too complex to be updateable. So suggest remove the two tables from your query and use a combo or listbox on your form with a rowsource of

SELECT tblStudents.StudentID, tblClasses.ClassRef
FROM (tblStudents INNER JOIN tblClasses_Students ON tblStudents.StudentID = tblClasses_Students.StudentID) INNER JOIN tblClasses ON tblClasses_Students.ClassID = tblClasses.ClassID; and the controlsource set to studentID. Make sure your set the locked property to yes

Thanks, I will give this solution a try.

 

[CJ_London]

if there's an alternative method of selecting student records which avoids the need for a select field I would be glad to hear of it.

Depends on what you are doing with the data once you have selected records but you might find this of interest https://www.access-programmers.co.uk/forums/threads/multi-select-form.289116/

 

[SurreyNick]

Oh yes, I remember seeing this a few weeks ago but I hadn't fully appreciated what it could be used for. Thanks CJ I will have another look.
Nick.

 

[zeroaccess]

Based on your Relationships, are any of these statements incorrect?

Each Class can have many Students
Each Student can have Many Classes

Each Student can have many Tests
Each Test can have many Results
Each Exam Paper can have many Tests

 

[SurreyNick]

All of the statements are true

As you will have gathered a test is an instance of an exam being taken by a student.