Link to home
Start Free TrialLog in
Avatar of bjv211
bjv211

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of Om Prakash
Om Prakash
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bjv211
bjv211

ASKER

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

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

Avatar of bjv211

ASKER

Well for whatever reason, it started working, but it takes about 40 seconds to run.  Any idea on the slowness?
Avatar of bjv211

ASKER

Just needed an index on payPeriod. Thanks :)