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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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;
ASKER
Well for whatever reason, it started working, but it takes about 40 seconds to run. Any idea on the slowness?
ASKER
Just needed an index on payPeriod. Thanks :)
ASKER
Open in new window