query exclusion code

[IfButOnly]

I am having a problem getting my mind around a query.

I have a SITE file and a JOB file - each site can have multiple jobs. JOBS looks like JobID, SiteID, JobDate ....

What I need to do is select all sites that haven't had a job assigned for 13 months. I can select jobs over 13 months but haven't been able to exclude sites that have had a job assigned since.

I would appreciate if someone can help or point me in the right direction.

 

[simongallop]

I would create a query from the 13 months worth of jobs but only report on the sites. I would group by site so that all I get is a list of sites in use. Then I would compare that against my site table, linking on sites and showing all sites in site table that are not present in my query

HTH

 

[IfButOnly]

Harry, thanks for the reply.

Two problems.

1.. some sites do not have any jobs.

2.. How do I show sites that are not present in the query?

 

[simongallop]

Sorry did the reply in a rush and didn't explain it thoroughly enough.

1/ Create a query which selects all records that are less than or equal to 13 months old.

2/ Create a query that takes the information from 1/ and select the sites column only. Group on this info (View / Totals)

3/ Using the Find Unmatched Query wizard, compare the list of sites in TABLE Sites against the info from query 2/

HTH

 

[IfButOnly]

Thanks Harry - that did the trick.