How to Select Records not in table with criteria

I have an Access 2007 db that manages school tutors timesheets (see relationship image below).  

When inputting timesheets, an admin uses a combobox to select tutors for recording hours worked.  However, to prevent any duplicates from occuring, the admins should only see tutors whom do not already have a timesheet entered for the current payperiod (a value we can call currPayPeriod).  

For example, an admin inputs timesheets for payperiod 3 and the combobox they use to select the tutor should not show tutors whom have already have a timesheet entered for payperiod 3.

Combobox1 format:  ID number, Firstname, Lastname
Relationship.PNG
LVL 1
bjv211Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Om PrakashCommented:
SELECT ID, number, Firstname, Lastname FROM tbl_tutors WHERE ID not in (SELECT ID FROM tbl_Timesheet WHERE payPeriod = 3)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bjv211Author Commented:
This query works great, thanks so much!  The one issue is that I want the combobox to be sorted by lastname, and when I add ORDER BY to the code below, it freezes.  Any idea why?

SELECT tbl_tutors.ID, tbl_tutors.Firstname, tbl_tutors.Lastname
FROM tbl_tutors
WHERE (((tbl_tutors.ID) Not In (SELECT ID FROM tbl_Timesheet WHERE payPeriod = 3)))
ORDER BY tbl_tutors.Lastname;

Open in new window

0
Om PrakashCommented:
I tried this query locally and it is working
SELECT tbl_tutors.ID, tbl_tutors.Firstname, tbl_tutors.Lastname 
FROM tbl_tutors 
WHERE tbl_tutors.ID Not In (SELECT ID FROM tbl_Timesheet WHERE payPeriod = 3)
ORDER BY tbl_tutors.Lastname;

Open in new window

0
bjv211Author Commented:
Well for whatever reason, it started working, but it takes about 40 seconds to run.  Any idea on the slowness?
0
bjv211Author Commented:
Just needed an index on payPeriod. Thanks :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.