Difficulty listing vehicle records that do not have a related record of a specified type in another table

[MLH]

Suppose you have tblVehicleJobs with [VehicleJobID] keyfield.
And say there's another table used to keep records of inbound
letters you receive on each car. That table is named tblinLttrs
and has [InLttrID] as it's keyfield and has [VehicleJobID] field
which is used to link it to tblVehicleJobs.

Since many letters (of several categories) could be sent in over
time pertaining to a given vehicle, there is a hard-coded One To
Many relationship from tblVehicleJobs to tblinLttrs respectively.

Supposing I have 5 distinct InLttr types: A, B, C, D and E and
that I wish to list all vehicles in a query that do NOT have a Type
C inbound letter record in tblinLttrs. How might I do that?

[timmg]

On May 1, 10:35*am, MLH <[e-mail]:

Suppose you have tblVehicleJobs with [VehicleJobID] keyfield.
And say there's another table used to keep records of inbound
letters you receive on each car. That table is named tblinLttrs
and has [InLttrID] as it's keyfield and has [tblVehicleJobs ] field
which is used to link it to tblVehicleJobs.

Since many letters (of several categories) could be sent in over
time pertaining to a given vehicle, there is a hard-coded One To
Many relationship from tblVehicleJobs to tblinLttrs respectively.

Supposing I have 5 distinct InLttr types: A, B, C, D and E and
that I wish to list all vehicles in a query that do NOT have a Type
C inbound letter record in tblinLttrs. How might I do that?

Create a query on tblinLttrs for all of the tblVehicleJobs with the
InLttr type of "C." Call that q_tblinLttrsC.

Create a query using tblVehicleJobs and q_tblinLttrsC with a Left
Join from tblVehicleJobs.tblVehicleJobs to tblinLttrs.tblVehicleJobs
(double click on the join in the query window and select "Show all
records from tblVehicleJobs..."). Make the criteria for
tblinLttrs.tblVehicleJobs Null.

Add additional fields to taste.

Tim Mills-Groninger

[MLH]

Thx, will have a lash at it.