I just revised the script as follows and the where clause works. I'm not sure why two LOADS are required to make the WHERE clause work.
WHERE ([Entity Status2] = 'A' AND Studflag='STUD' AND Profession = 'NUR' AND Yrsins2=1);
LOAD [Account No],[Entity Status2],Studflag,Profession,Yrsins2
Drop Table T1_ActiveTerm2
The records from that load statement will be appended to the table T1_ActiveTerm2 because you load exactly the same fields. That's why you see all the records.
Add the NOCONCATENATE keyword before the LOAD. And drop the table T1_ActiveTerm2 afterwards or you'll get a massive synthetic key which will likely cripple performance.
You don't need two. But your modification specifically list fields. That list of fields is a subset of all the fields that exists in T1_ActiveTerm2. Since that will create a table with a different structure (less fields than the source table) the records will end up in the new table instead of being appended to the source table.