sql - Search for missing records for the week -

i having troubles formulating sql query search missing records week. using access 2007. following shows tables have.

employee table         id     name   1      john   2      peter   

---

time cards   id     name     date        week            cardnum   1      john     15/06/2015  21/06/2015  1234   1   2      peter    16/06/2015  21/06/2015  1233   2   1      john     16/06/2015  21/06/2015  1231   3   1      john     17/06/2015  21/06/2015  1230   4   

i have managed search missing records particular day, using following sql query.

    select distinct         employee.name         employee.id       employee     (((employee.name) not in ( select tc.name  [time card] tc  tc.name = employee.name , tc.date = [forms]![missing production time card]![text12] ))); 

i trying search records such days within week eg 15/06/2015 21/06/2015 employee did not key in time cards. there way can modify query or create new 1 such following results?

the results above time card table should have

id  name date   1   john 18/06/2015   1   john 19/06/2015   1   john 20/06/2015   1   john 21/06/2015   2   peter 15/06/2015   2   peter 17/06/2015   2   peter 18/06/2015   2   peter 19/06/2015   2   peter 20/06/2015   2   peter 21/06/2015   

one way create temporary table, , populate dates , employees searching for. can use outer join join time card table, , rows temp table don't have matching row in time card missing.

it useful have table list of dates particular week - make below code easier, , allow deal bank holidays , weekends.

here's stab @ code:

create table ##missing (     date datetime,     id int )  insert ##missing select '20150605', id employee -- repeat other dates in week, or use table proposed above  select     m.date,     e.name ##missing m join employee e     on m.id = e.id left outer join [time card] tc     on m.id = tc.id     , m.date = tc.date tc.id null