By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,647 Members | 1,706 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,647 IT Pros & Developers. It's quick & easy.

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

P: n/a
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));

Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
How about using the Find Unmatched query wizard and then turning it
into an append query?

Nov 13 '05 #2

P: n/a
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?


Nov 13 '05 #3

P: n/a
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
Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.