How to find records missing a record in a related table

[Guest]

Sorry for the subject description, I hope I can better explain my question
here.

I have a access 2003 database with related tables. What I am trying to do
is query for records that do not contain a record type in a related table.

Example:
Main table is jobs
Related table is tasks

There are many tasks related to any job (one to many relationship).

The linked field type in a number type.

I want to query for jobs that DO NOT have a specific task type.

I have a query like this:

"
SELECT DISTINCTROW Jobs.JobID, Jobs.JobNr, Jobs.RevisedInstallDate,
Jobs.JobName
FROM Jobs INNER JOIN JobTasks ON Jobs.JobID = JobTasks.JobID
WHERE (((Jobs.JobNr) Not Like "00/00000") AND ((Jobs.JobName) Not Like "Shop
Maintenance & Organization") AND ((JobTasks.TaskID) Not Like 2) AND
((Jobs.CompletedProduction)=False) AND ((Jobs.CompletedService)=False))
ORDER BY Jobs.RevisedInstallDate, Jobs.JobName;
"
However it still includes the task type 2 in some circumstances.

Any ideas?

Thanks,
John

 

[Marshall Barton]

Sorry for the subject description, I hope I can better explain my question
here.

I have a access 2003 database with related tables. What I am trying to do
is query for records that do not contain a record type in a related table.

Example:
Main table is jobs
Related table is tasks

There are many tasks related to any job (one to many relationship).

The linked field type in a number type.

I want to query for jobs that DO NOT have a specific task type.

I have a query like this:

"
SELECT DISTINCTROW Jobs.JobID, Jobs.JobNr, Jobs.RevisedInstallDate,
Jobs.JobName
FROM Jobs INNER JOIN JobTasks ON Jobs.JobID = JobTasks.JobID
WHERE (((Jobs.JobNr) Not Like "00/00000") AND ((Jobs.JobName) Not Like "Shop
Maintenance & Organization") AND ((JobTasks.TaskID) Not Like 2) AND
((Jobs.CompletedProduction)=False) AND ((Jobs.CompletedService)=False))
ORDER BY Jobs.RevisedInstallDate, Jobs.JobName;
"
However it still includes the task type 2 in some circumstances.

Change the INNER JOIN to LEFT JOIN and add the criteria
AND JobTasks.JobID Is Null to the WHERE clause.

 

[Smartin]

Sorry for the subject description, I hope I can better explain my question
here.

I have a access 2003 database with related tables. What I am trying to do
is query for records that do not contain a record type in a related table.

Example:
Main table is jobs
Related table is tasks

There are many tasks related to any job (one to many relationship).

The linked field type in a number type.

I want to query for jobs that DO NOT have a specific task type.

I have a query like this:

"
SELECT DISTINCTROW Jobs.JobID, Jobs.JobNr, Jobs.RevisedInstallDate,
Jobs.JobName
FROM Jobs INNER JOIN JobTasks ON Jobs.JobID = JobTasks.JobID
WHERE (((Jobs.JobNr) Not Like "00/00000") AND ((Jobs.JobName) Not Like "Shop
Maintenance & Organization") AND ((JobTasks.TaskID) Not Like 2) AND
((Jobs.CompletedProduction)=False) AND ((Jobs.CompletedService)=False))
ORDER BY Jobs.RevisedInstallDate, Jobs.JobName;
"
However it still includes the task type 2 in some circumstances.

Any ideas?

Thanks,
John

Hi John,

Have you explored the "unmatched query wizard"? This should help you
find jobs that do not have a matching "JobID" in the tasks table.

 

[Guest]

Thank you both for the prompt response.

I think you are on the right track with the LEFT JOIN instead of my INNER
JOIN query, however I think the reason that I am still not getting all the
records is due to a third table.

The Jobs table (simplified):
JobID - Autonumber (primary key)
JobName - Text
*various other fields*

The JobTasks table (simplified):
JobID - Number (long)
TaskID - Number (long)
StartDate - Date/Time
EndDate - Date/Time

The TaskTypes table:
TaskID - Autonumber
Description

The Jobs table is related to the JobTasks table via the JobID field. One to
many relationship with referential integrity enforced.

The TaskType table is related to the JobTasks table with a one to many
relationship via the TaskID field. Referential integrity is enforced.

I wish my query to find Jobs that do not contain a specific TaskID (TaskID =
2).
There are many other TaskID record types for the jobs, but I want to only
find records that do not contain TaskID = 2.

The LEFT JOIN still gives me Job records that have a TaskID 2 type related
to the Job, it only does not display them.

I hope my explaination helps.

Thanks,
John

 

[Marshall Barton]

I think you are on the right track with the LEFT JOIN instead of my INNER
JOIN query, however I think the reason that I am still not getting all the
records is due to a third table.

The Jobs table (simplified):
JobID - Autonumber (primary key)
JobName - Text
*various other fields*

The JobTasks table (simplified):
JobID - Number (long)
TaskID - Number (long)
StartDate - Date/Time
EndDate - Date/Time

The TaskTypes table:
TaskID - Autonumber
Description

The Jobs table is related to the JobTasks table via the JobID field. One to
many relationship with referential integrity enforced.

The TaskType table is related to the JobTasks table with a one to many
relationship via the TaskID field. Referential integrity is enforced.

I wish my query to find Jobs that do not contain a specific TaskID (TaskID =
2).
There are many other TaskID record types for the jobs, but I want to only
find records that do not contain TaskID = 2.

The LEFT JOIN still gives me Job records that have a TaskID 2 type related
to the Job, it only does not display them.

I think I oversimplified the problem. A Join is not going
to do the job. I think this should be closer:

SELECT Jobs.JobID, Jobs.JobNr,
Jobs.RevisedInstallDate, Jobs.JobName
FROM Jobs
WHERE Jobs.JobNr <> "00/00000")
AND Jobs.JobName <> "Shop Maintenance & Organization"
AND Jobs.CompletedProduction = False
AND Jobs.CompletedService = False
AND Not Exists (SELECT JobTasks.TaskID
FROM JobTasks
WHERE Jobs.JobID = JobTasks.JobID
AND JobTasks.TaskID = 2)
ORDER BY Jobs.RevisedInstallDate, Jobs.JobName