Filter by absence of subform record

[BruceM]

I have a table (tblPlan) for general information about a Processing Plan for
machine parts. It has a related Plan Details table (tblPlanSteps) for the
individual steps. The individual steps are drawn from a list of steps
(contained in tblSteps) that are shared by several Plans.

tblPlan
PlanID (autonumber PK)
PlanNumber (in-house identifier)

tblSteps
StepID (autonumber PK)
StepDescription

tblPlanSteps
DetailsID (autonumber PK)
PlanID_ch (FK)
StepID_ch (FK)

Sample Plans:

PlanNumber: 100
Steps:
Weld
Grind
Polish
Coat

PlanNumber: 200
Steps:
Grind
Polish
Coat

What I need is a query that will produce a listing of just the plans that do
not contain the Weld step, and a second listing (maybe in another query) of
just the plans that do contain the Weld step. I have been playing around
with Count, DCount, criteria, and everything else I can think of, but
apparently I have neglected to think of the approach that will solve the
problem.

 

[Marshall Barton]

I have a table (tblPlan) for general information about a Processing Plan for
machine parts. It has a related Plan Details table (tblPlanSteps) for the
individual steps. The individual steps are drawn from a list of steps
(contained in tblSteps) that are shared by several Plans.

tblPlan
PlanID (autonumber PK)
PlanNumber (in-house identifier)

tblSteps
StepID (autonumber PK)
StepDescription

tblPlanSteps
DetailsID (autonumber PK)
PlanID_ch (FK)
StepID_ch (FK)

Sample Plans:

PlanNumber: 100
Steps:
Weld
Grind
Polish
Coat

PlanNumber: 200
Steps:
Grind
Polish
Coat

What I need is a query that will produce a listing of just the plans that do
not contain the Weld step, and a second listing (maybe in another query) of
just the plans that do contain the Weld step. I have been playing around
with Count, DCount, criteria, and everything else I can think of, but
apparently I have neglected to think of the approach that will solve the
problem.

I think you want a query that gets everything:

SELECT PlanNumber, StepDescription
FROM *tblPlanSteps INNER JOIN tblPlan
ON tblPlanSteps.PlanID = tblPlan.PlanID)
INNER JOIN tblSteps
ON tblPlanSteps.StepID_ch = tblSteps.StepID
ORDER BY (StepDescription = "Weld")

 

[John Spencer]

IF you are familar with SQL

SELECT tblPlan.*
FROM tblPlan
WHERE tblPlan.PlanID
NOT IN
(SELECT tblPlanSteps.PlanID_CH
FROM tblPlanSteps INNER JOIN tblSteps
ON tblPlanSteps.StepID_CH = TblSteps.StepID
WHERE tblSteps.StepDescription = "Weld")

NOT IN operator is slow, so a better solution is as follows (this solution
is not always available due to people naming fields and tables that require
the use of the bracket delimiters [].)

SELECT tblPlan.*
FROM tblPlan LEFT JOIN
(SELECT PS.PlanID_CH
FROM tblPlanSteps as PS INNER JOIN tblSteps as S
ON PS.StepID_CH = S.StepID
WHERE tblSteps.StepDescription = "Weld") as T
ON tblPlan.PlanID = T.PlanID_CH
WHERE T.PlanID_CH is NULL

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..