Can a query "look" at whats already in 2 table fields before appending a rec w/ redundant values in those two fields?

[MLH]

I have a query, qryAppend30DayOld260ies that attempts to append
records to tblCorrespondence. When run, it can result in any of the
following: appending no records, appending 1 record or appending
many records. Two of the target fields in tblCorrespondence receiving
values in the append operation are [VehicleJobID] and [OutType]. For
any given VehicleJobID value, I want only ONE record in correspondence
table to have an [OutType] value of "01". This query blindly appends
to the correspondence table without regard to whether there is already
a record in tblCorrespondence having say a value of 100 in
[VehicleJobID] and "01" in [OutType]. How can I make this query "look"
at what's in correspondence table while appending to determine that no
record in correspondence table already has matching values in the
[VehicleJobID] and [OutType] fields, avoiding putting in redundant
records?

INSERT INTO tblCorrespondence ( VehicleJobID, OutType, ToWhom, UserID,
InProcessor, Tracked )
SELECT DISTINCTROW tblCorrespondence.VehicleJobID, "01" AS OutType,
tblCorrespondence.ToWhom, "System" AS UserID,
tblCorrespondence.InProcessor, True AS Tracked
FROM tblVehicleJobs INNER JOIN tblCorrespondence ON
tblVehicleJobs.VehicleJobID = tblCorrespondence.VehicleJobID
WHERE (((tblCorrespondence.InProcessor) Is Null) AND
((tblCorrespondence.OutDate) Is Not Null) AND
((tblCorrespondence.OutProcessor) Is Not Null) AND
((tblCorrespondence.OutType)="00") AND
((DateDiff("d",[DateLeft],Now()))>=30) AND
((tblVehicleJobs.Reclaimed)=No) AND
((tblVehicleJobs.ENF260Written)=True));

[pietlinden]

How about using the Find Unmatched query wizard and then turning it
into an append query?

[MLH]

I ended up using an index on 2 fields of the table, setting it to
Unique. That prevented addition of a second record with same
values in the two fields already held by an existing record. That
did the trick w/o a lot of fancy preventive coding.

How about using the Find Unmatched query wizard and then turning it
into an append query?

[David W. Fenton]

MLH <CR**@NorthState.net> wrote in
news:d0********************************@4ax.com:

I ended up using an index on 2 fields of the table, setting it to
Unique. That prevented addition of a second record with same
values in the two fields already held by an existing record. That
did the trick w/o a lot of fancy preventive coding.

But the cost of discarding the index collisions may be very high. I
don't believe in letting errors happen that I know how to prevent,
so I always use an outer join to exclude the records that already
exist. This seems like good preventive coding to me.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc