date - How do you check a field in a query for specific values if and only if another field had a specific value? -

i want write expression in access query check see if item running next week has run in 4 weeks before date. i'm having trouble understanding how can check in same query , use returned value of check check few more things. method i've tried write make query use results of first query (where items 7/20/2015 isolated) check dates of items, run problem because have tie query original table, duplicates data.

written in pseudo-code, following:

if item has job_date of 7/20/2015, check instances of item prior 7/20/2015 , if there no instance weeks 7/13/2015, 7/6/2015, 6/29/2015, or 6/22/2015, return item.

written in more visual way:

item          job_date             6/22/2015             6/29/2015             7/6/2015             7/13/2015             7/20/2015  item          job_date b             6/15/2015 b              b              b              b              b             7/20/2015  item          job_date c             6/22/2015 c              c              c             7/13/2015 c             7/13/2015 c             7/20/2015  item          job_date d              d              d              d              d              d              

item not returned because have been running continuously. item b return because though ran on 6/15/2015, more 4 weeks before 7/20/2015. item d not return because doesn't have row target date (7/20/2015).

notes:

• ideally, use expressions determine next week's date automatically, used hard dates here clarity. if want use hard dates please feel free.

• our weeks begin on mondays.

• an item can run multiple times in 1 week. example, item c can have 2 rows 7/13/2015 , 7/13/2015. job number (not shown, exists in same table) unique identifier in table. if item c run twice, jobs h000001584-0030 , h00001584a-0030.

the query below returns result set sample data when supply 7/20/2015 parameter value:

item ---- b 

the query includes subquery returns items have rows job_date values in 28 days before target date. note subquery not return item had no job_date values in range --- gives items did have job_date in range. therefore left joining subquery , asking rows "right side" null means exclude items final result set.

parameters ptarget datetime; select j.item     dbo_job j     left join     (         select [item]         dbo_job         job_date>=dateadd('d', -28, [ptarget]) , job_date<[ptarget]         group [item]     ) sub     on j.item = sub.item j.job_date=[ptarget] , sub.item null group j.item;