Multiple decision criteria

[beckyr]

I am trying to move students from one table to another if they have a certain course code. This works by using multiple sql statements but is there a quicker way that combines these?

strsql = "Insert into tblWorking ( STU_ID, STU_FORENAME, STU_SURNAME, STU_COURSE_CODE, STU_STANDING ) "
strsql = strsql & "Select tblIncoming.STU_ID, tblIncoming.STU_FORENAME, tblIncoming.STU_SURNAME, tblIncoming.STU_COURSE_CODE, tblIncoming.STU_STANDING "
strsql = strsql & "From [tblIncoming] "
strsql = strsql & "Where [tblIncoming].STU_COURSE_CODE = 'TR091' "
db.Execute (strsql)

strsql = "Insert into tblWorking ( STU_ID, STU_FORENAME, STU_SURNAME, STU_COURSE_CODE, STU_STANDING ) "
strsql = strsql & "Select tblIncoming.STU_ID, tblIncoming.STU_FORENAME, tblIncoming.STU_SURNAME, tblIncoming.STU_COURSE_CODE, tblIncoming.STU_STANDING "
strsql = strsql & "From [tblIncoming] "
strsql = strsql & "Where [tblIncoming].STU_COURSE_CODE = 'TR092' "
db.Execute (strsql)

Ive tried "Where [tblIncoming].STU_COURSE_CODE = 'TR091', 'TR092' " but thats wrong

 

[ajetrumpet]

is there a quicker way that combines these?

Yes, because both of the statements are exactly the same!

Ive tried "Where [tblIncoming].STU_COURSE_CODE = 'TR091', 'TR092' " but thats wrong

That's because you forgot to add the appropriate operator. Use this instead:

"Where [tblIncoming].STU_COURSE_CODE = 'TR091' [B][U][COLOR="Red"]OR[/COLOR][/U][/B] 
   [tblIncoming].STU_COURSE_CODE = 'TR092'"

 

[pbaldy]

Alternatively if there were several:

Where [tblIncoming].STU_COURSE_CODE IN( 'TR091', 'TR092', ...)

I can't say there's any science behind it, but I typically use OR when there are 2 options, IN when there are more than 2.