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, qryAppend30DayO ld260ies that attempts to append
records to tblCorresponden ce. 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 tblCorresponden ce 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 tblCorresponden ce 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 tblCorresponden ce ( VehicleJobID, OutType, ToWhom, UserID,
InProcessor, Tracked )
SELECT DISTINCTROW tblCorresponden ce.VehicleJobID , "01" AS OutType,
tblCorresponden ce.ToWhom, "System" AS UserID,
tblCorresponden ce.InProcessor, True AS Tracked
FROM tblVehicleJobs INNER JOIN tblCorresponden ce ON
tblVehicleJobs. VehicleJobID = tblCorresponden ce.VehicleJobID
WHERE (((tblCorrespon dence.InProcess or) Is Null) AND
((tblCorrespond ence.OutDate) Is Not Null) AND
((tblCorrespond ence.OutProcess or) Is Not Null) AND
((tblCorrespond ence.OutType)=" 00") AND
((DateDiff("d",[DateLeft],Now()))>=30) AND
((tblVehicleJob s.Reclaimed)=No ) AND
((tblVehicleJob s.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**@NorthStat e.net> wrote in
news:d0******** *************** *********@4ax.c om:

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