• ** There has been a recent site upgrade. Please clear your browser cache to avoid issues. **
  • New forum feature - post voting and best solution

    Check out this thread for the details: https://www.access-programmers.co.uk/forums/threads/new-forum-feature-post-voting-and-best-answer.314134/

    This new feature looks great to me! :)

  • We now have 3 forum themes

    Go for the default (light) theme, Shades of Grey or Shades of Blue. I just added the Blue one.

    The thread about it is here: https://www.access-programmers.co.uk/forums/threads/new-forum-theme-shades-of-blue.314136/

Solved Filtering a table to exclude records based upon two criteria (1 Viewer)

SurreyNick

Member
Local time
Today, 21:47
Joined
Feb 12, 2020
Messages
120
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

I’m here to help
Staff member
Local time
Today, 14:47
Joined
Oct 29, 2018
Messages
12,624
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

Member
Local time
Today, 21:47
Joined
Feb 12, 2020
Messages
120
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

I’m here to help
Staff member
Local time
Today, 14:47
Joined
Oct 29, 2018
Messages
12,624
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

Banishment Pending
Local time
Today, 16:47
Joined
May 11, 2011
Messages
10,083
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

Member
Local time
Today, 21:47
Joined
Feb 12, 2020
Messages
120
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

I’m here to help
Staff member
Local time
Today, 14:47
Joined
Oct 29, 2018
Messages
12,624
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

Member
Local time
Today, 21:47
Joined
Feb 12, 2020
Messages
120
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

Banishment Pending
Local time
Today, 16:47
Joined
May 11, 2011
Messages
10,083
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

Member
Local time
Today, 21:47
Joined
Feb 12, 2020
Messages
120
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

Banishment Pending
Local time
Today, 16:47
Joined
May 11, 2011
Messages
10,083
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

Member
Local time
Today, 21:47
Joined
Feb 12, 2020
Messages
120
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

Super Moderator
Staff member
Local time
Today, 17:47
Joined
Feb 19, 2002
Messages
29,803
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

Member
Local time
Today, 21:47
Joined
Feb 12, 2020
Messages
120
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

Super Moderator
Staff member
Local time
Today, 17:47
Joined
Feb 19, 2002
Messages
29,803
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.
 

Users who are viewing this thread

Top Bottom