Insert into unless?

[beckyr]

Wondering if someone could help me out here. My query is trying to insert students into another table if their standing is 4 but not if those students with standing 4 have a course code equal to TR051 or Tr052. At the moment its inserting all students not doing TR051 or TR052 regardless of standing. Is there such a thing as insert into if 4 unless TR051 or TR052??

INSERT INTO tblFinalYrs ( STU_ID, STU_FORENAME, STU_SURNAME, STU_STANDING, STU_COURSE_CODE, STU_TU_CODE, STU_FAC_NO, STU_EMAIL )
SELECT tblStudents.STU_ID, tblStudents.STU_FORENAME, tblStudents.STU_SURNAME, tblStudents.STU_STANDING, tblStudents.STU_COURSE_CODE, tblStudents.STU_TU_CODE, tblStudents.STU_FAC_NO, tblStudents.STU_EMAIL
FROM tblStudents
WHERE (((tblStudents.STU_STANDING)=4) AND ((tblStudents.STU_COURSE_CODE)<>"TR051")) OR (((tblStudents.STU_COURSE_CODE)<>"TR052"));

 

[ByteMyzer]

Try changing your WHERE clause to:

WHERE tblStudents.STU_STANDING=4
AND tblStudents.STU_COURSE_CODE NOT IN ('TR051','TR052')

 

[GolferGuy]

INSERT INTO tblFinalYrs ( STU_ID, STU_FORENAME, STU_SURNAME, STU_STANDING, STU_COURSE_CODE, STU_TU_CODE, STU_FAC_NO, STU_EMAIL )
SELECT tblStudents.STU_ID, tblStudents.STU_FORENAME, tblStudents.STU_SURNAME, tblStudents.STU_STANDING, tblStudents.STU_COURSE_CODE, tblStudents.STU_TU_CODE, tblStudents.STU_FAC_NO, tblStudents.STU_EMAIL
FROM tblStudents
WHERE (((tblStudents.STU_STANDING)=4) AND ((tblStudents.STU_COURSE_CODE)<>"TR051")) OR (((tblStudents.STU_COURSE_CODE)<>"TR052"));

This is a very common "GotYa". You need three ANDs and no OR in your criteria.

To try to explain. You want to insert Standing = 4 AND Code <> "TR051" AND Code <> "TR052)
Before inserting this record, you want all three tests to be TRUE. What you actually had was this: If Standing = 4 AND Code <> "TR051" **OR** If Code <> "TR052"
You actually do not have two criteria for inserting a record, you have three. It must be a Standing of 4, AND it can NOT be (Code = "TR051" OR Code = "TR052")
So, if (Code = "TR051" OR Code = "TR052") returns a TRUE you have the condition you have said will prevent the insertion of a new record. So, to insert a new record this condition must be false, so we put the NOT in front of that condition.
I know, it's complicated, but when the light dawns and we can see the the oposite of one condition with ANDs is the negative of that condition with ORs.

 

[Moniker]

INSERT INTO tblFinalYrs 
    (STU_ID
    ,STU_FORENAME
    ,STU_SURNAME
    ,STU_STANDING
    ,STU_COURSE_CODE
    ,STU_TU_CODE
    ,STU_FAC_NO
    ,STU_EMAIL)
SELECT 
    tblStudents.STU_ID
    ,tblStudents.STU_FORENAME
    ,tblStudents.STU_SURNAME
    ,tblStudents.STU_STANDING
    ,tblStudents.STU_COURSE_CODE
    ,tblStudents.STU_TU_CODE
    ,tblStudents.STU_FAC_NO
    ,tblStudents.STU_EMAIL
FROM 
    tblStudents
WHERE 
    tblStudents.STU_STANDING=4 AND 
    tblStudents.STU_COURSE_CODE NOT IN ('TR051','TR052')
;

Quick point though. You're denormalizing the DB by doing this. You're storing the student name, standing, course code, etc. in two different places. If you change the standing in the tblFinalYrs table, then that student has one standing in tblStudents and another in tblFinalYrs -- denormalization.

You want to store the student ID only in tblFinalYrs and have it reference tblStudents through a join on STU_ID to get the remainder of the fields. That way, the change is propagated correctly.

 

[beckyr]

Thanks for all your suggestions but unfortunately none of them are working, I have no idea why this is. I have test data in there that should be moving into tblFinalYrs but its not. Any ideas why?

p.s: i going to subsequently delete these students from tblStudents Monkier, just moving them first

 

[beckyr]

Sorry guys, i hadnt put in course codes for some of my testing students, it now works. One problem with this is that occassionally there will be a student who does not have a course code - how do i get around the problem of them having to have a course code to get appended?

 

[WayneRyan]

Becky,

WHERE tblStudents.STU_STANDING = 4 AND 
     (tblStudents.STU_COURSE_CODE NOT IN ('TR051','TR052') Or
      tblStudents.STU_COURSE_CODE Is Null)

Wayne

 

[beckyr]

Great thanks Wayne!

 

[Moniker]

One other way (performs quicker than a WHERE statement):

.
.
SELECT 
    tblStudents.STU_ID
    ,tblStudents.STU_FORENAME
    ,tblStudents.STU_SURNAME
    ,tblStudents.STU_STANDING
    ,[COLOR="red"]Nz([/COLOR]tblStudents.STU_COURSE_CODE[COLOR="Red"],"")[/COLOR]
    ,tblStudents.STU_TU_CODE
    ,tblStudents.STU_FAC_NO
    ,tblStudents.STU_EMAIL
FROM 
    tblStudents
.
.