Access query help??

[Ynot]

Access query help??



I have a table made up of jobs. It is linked to customers for name/address
and linked to a schedule table for dates of tasks. The task table is linked
to a task description table that gives a full detailed description of what
the task entails.



As you can tell it is a one to many relationship, one job will have one
customer but many tasks. What I am try to do is produce a query or a report
using a query where I can list all the jobs that are absent a particular
task, like collect down payment. There may already be many tasks listed and
completed but someone forgot to collect or book the down payment.



Any help is greatly appreciated!!!

 

[[MVP] S.Clark]

The Find Unmatched query wizard can help.

Use the table of all tasks against the table of completed tasks, per job.

--
Steve Clark, Access MVP
FMS, Inc
http://www.fmsinc.com/consulting
Professional Access Database Repair
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

 

[Ynot]

I just looked at the wizard. Thanks but it doesn't give me what I need. It
will give me jobs that are missing some of the many possible tasks but
individual jobs may take a full set of different tasks.



This would truly be more like which programming jobs are missing
specifications, which database jobs are missing schemas, which
implementation jobs are missing work agreement, etc..



I haven't been able to figure out from what the wizard gave me a way to take
that and make it work for what I need.

 

[John Spencer]

Two query solution.
-- Create a query that shows all jobs that DO have the specific task.

With that query and your jobs table you can use the unmatched query wizard
to locate all jobs that don't have the specific task.

Generic SQL follows that might let you do this all in one query: I don't
know your table structure and field names

SELECT Jobs.*
FROM Jobs LEFT JOIN
(SELECT JobTasks.JobID
FROM JobTasks
WHERE JobTasks.TaskID = "Collect Down Payment") as Q
ON Jobs.JobID = Q.JobID
WHERE Q.JobID Is Null

OR (This is probably slower than the above, but will work if you have table
and field names with spaces or special characters)
SELECT Jobs.*
FROM Jobs
WHERE Not Exists
(SELECT *
FROM JobTasks
WHERE JobTasks.JobID = Jobs.JobID
AND JobTasks.TaskID = "Collect Down Payment")