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]

Re: Difficulty listing vehicle records that do not have a relatedrecord of a specified type in another table

On May 1, 10:35 am, MLH <C...@NorthStat e.netwrote:

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.tblV ehicleJobs
(double click on the join in the query window and select "Show all
records from tblVehicleJobs. .."). Make the criteria for
tblinLttrs.tblV ehicleJobs Null.

Add additional fields to taste.

Tim Mills-Groninger

[MLH]

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

Thx, will have a lash at it.