Solved Filtering a table to exclude records based upon two criteria

[SurreyNick]

Hoping someone can help.

I have a junction table which contains records of students who have been allocated class year groups. It contains 3 fields; StudentID, ClassID, and AcademicYearID. A student can only be allocated one class year group in any academic year, so the table has a composite primary key on StudentID and AcademicYearID

I am trying to create a query which filters the records in this junction table to show me just those students who were allocated in a class in 2019 and have not yet been allocated to in a class in 2020.

I can easily find all those students allocated classes in 2019, but as I allocate these students a class in 2020 and I re-run the query I want the record list to reduce showing me just those students who still need to be allocated a class in 2020 and I can’t work out how to do this, i.e. I want to filter out those records where the student ID appears twice in the table, once for 2019 and once for 2020.

I must be missing something because I’ve searched through my Access manuals and I can’t find the solution. Can anyone help?

Thank you.

 

[theDBguy]

Hi. I suppose you can use just the junction table to filter the students. But the proper way to do this is to use the students table, so you don't miss any new students.

 

[SurreyNick]

Yeah, I have already caught the new students with a separate query used when new students are added to the database and prompts the user to allocate them to a year group class. The issue I have is just limited to finding an easy way to pick up those students who attended the previous year and have yet to be allocated to a year group class in the current year. I have tried numerous approaches without success. I thought a duplicates query might be the way but this has proved fruitless too. I'm wondering if I need to use an SQL SELECT DISTINCT statement, but not sure how to proceed.

 

[theDBguy]

Yeah, I have already caught the new students with a separate query used when new students are added to the database and prompts the user to allocate them to a year group class. The issue I have is just limited to finding an easy way to pick up those students who attended the previous year and have yet to be allocated to a year group class in the current year. I have tried numerous approaches without success. I thought a duplicates query might be the way but this has proved fruitless too. I'm wondering if I need to use an SQL SELECT DISTINCT statement, but not sure how to proceed.

Hi. How about you create one query to show all students for last year and another query to show all students for this year. Then, join the two, using outer join, to see who is missing?

 

[plog]

You do this with 3 queries:

sub1: Students allocated to 2019.

sub2: Students allocated to 2020.

Main: In the query designer bring in sub1 and sub2. Link them via their StudentID fields and change the link such that you show all records from from sub1. Bring down the StudentID of both tables, under sub2.StudentID add criteria so that it shows just NULL values. Run it and that's your list. When you add records to 2020 rerun this query and it will be updated.

 

[SurreyNick]

Ahh, thank you both that's really helpful. I have to pop out now but I'll revisit the task in the morning and will report back how I get on.
Thanks again.

 

[theDBguy]

Ahh, thank you both that's really helpful. I have to pop out now but I'll revisit the task in the morning and will report back how I get on.
Thanks again.

Good luck!

 

[SurreyNick]

Well, the solution worked exactly as you said it would and that's great thank you, but.....alas I've now encountered another problem. The recordset isn't updateable.

I was hoping to join this query to my students table on studentID and use a checkbox in my students table for the user to select students from the list and via a button on the form execute a bit of code to add the selected students to my student_classes junction table for the current year.

I was so hoping this would be a simple exercise.

 

[plog]

I don't entirely understand the interface you want to achieve, but you can use the query I provided to feed a combo box. Then users could select the correct student and it would be added to whatever table.

 

[SurreyNick]

Yes, that would work, but doing this for nearly 500 students one at a time will be a tiresome undertaking so what I was setting out to achieve was a continuous subform (of those students to be allocated to new year form classes) with a checkbox alongside each student (I have a Y/N field in my students table for this purpose) so the user can then select c.30 students from the list (appx. class size), choose a ClassID from a combo box on the main form and then execute a bit of code which appends that ClassID to the selected students StudentID and then adds them to my junction table along with the current AcademicYearID. The final line of code refreshes the subform thereby leaving just those students who still need allocating to a current year form group.

 

[plog]

Your method still allocates them 1 at a time, its just a checkbox instead of a drop down, it still requires user intervention in the allocation process. If you wanted to allocate them in groups of 30 you could build a query to put them into batches and have a query to the work for you.

 

[SurreyNick]

You are, of course, quite right and please don't think I'm ungrateful, because I'm not. On the contrary, I am very grateful for the help. I simply need to accept I have to do it a slightly different way to what I had planned.
Nick.

 

[Pat Hartman]

I know you have your answer but if Allocated is numeric, a simple way to get the studentID's is:

Select StudentID, Sum(Allocated) As YearSum
From YourTable
Group By StudentID
Where Sum(Allocated) = 2019 AND Allocated = 2019 OR Allocated = 2020

The value of Year sum will be 2019, 2020, or 4039 when both are allocated. You only want the ones where 2019 was allocated but not 2020. Notice that Allocated is NOT included in the Select clause since that would invalidate the Sum() function.

 

[SurreyNick]

Thanks Pat, I was just thinking I should perhaps have begun by asking the experts here (such as yourself, plog, theDBguy etc. etc.) what approach you would take to the task at hand rather than just asking for help with the approach I opted for. I probably would have learned more.

 

[Pat Hartman]

You're welcome. Some of us actually try to see past the question asked to the problem that needs to be solved. Others just load your gun with bullets and set you loose But in this case, there are simply several alternatives. The one you choose really depends on the next step you want to take. I wasn't offering my solution as "best", simply as yet another option that might be "best" in some situation.

Once you have the list of studentID's you can use them in an append query if you want to automate the process of adding 2020 records.