ms access - Query table based on results of another table -

i trying create query in access picks out rows in table based on existence of values in table. looking @ table contains state of different id numbers , checking whatever state id @ has progressed through lower states.

for example, each id goes apprentice journeyman master. if id @ master, noted in state table, there should record in events table id being @ apprentice , master well.

the query should return ids state table @ master state have not progressed through states based on entries in events table.

with 2 tables below, query should return ids 2. id 2 listed @ master never @ journeyman state.

state table: id status 1 master 2 master 3 apprentice 4 journeyman

events table: date status id 7/25/17 master 1 7/25/17 master 2 7/24/17 journeyman 1 7/24/17 journeyman 3 7/20/17 apprentice 4 7/20/17 apprentice 3 7/20/17 apprentice 2 7/20/17 apprentice 1

i have created query ids marked master. i'm not sure go there @ events table ids marked master in state table.

this simple query. first time using access, pointers appreciated!

if no journeyman event criteria consider:

select * state status="master" , not id in (select id events status="journeyman");

date reserved word. should not use reserved words names. better eventdate.