Update query question

[tmar89]

I want to run a query between two tables to see if there is a matching record or not. If there is no record, I have a Yes/No field in the table called "notInSystem" that I want to be clicked YES then. How would I go about this?

ex:
student table:
ID NAME notInSystem
1 Tom []
2 Jim []
3 Bill []

current roster table:
old table:
ID NAME
1 Tom
2 Jim
4 Mike

After query:
student table:
ID NAME notInSystem
1 Tom []
2 Jim []
3 Bill [X]
4 Mike []

I have the append query working, i just need help doing that task of checking off the people that don't exist anymore.

 

[Fuga]

You can use the wizard to create a "find unmatched query".

But do you really need the yes/no field? Can´t you use the query?

Fuga.

 

[tmar89]

Yes, I need this Yes/No field because we need to have a way to display if the student is no longer in the university

 

[Fuga]

There are other ways to do that, but they are a bit more tricky. If your db isn´t too big, I guess you can use the yes/no field.

Does it work if you make a "find unmatched query", change it into an update query and put -1 in the "update to" row?

Fuga.

 

[tmar89]

hrmm.. thats not a bad idea.. take the unmatched query, append a blank table with the Yes/No field set to default YES and then do an update query on the table i am using.. that may work. that may be tricky.. ideas?

 

[tmar89]

thanks for the advice.. im a little confused about how to write this left join. I want to have the code under a button and was just wondering if you could help me write it out fully as I not that familiar with this.