Avoid Duplicates with an Append Query

[Amy Adams via AccessMonster.com]

The following SQL statement is an Append Query that I am running in my
database.

When someone adds a New Physician into the database, this append query adds
plans and groups to the new physician. The problem I am having is that the
end user is sometimes running this append on exsisting physicians who
already have plans and groups attached. When they run an append on an
exisiting physiscian duplicate records are created. Is it possible to add
something to the below SQL statement that would prevent the append from
running if physician already has plans and groups loaded?

Example:
INSERT INTO PhysicianPlan ( UPIN, PlanID, GroupID )
SELECT Physicians.UPIN, Plans.PlanID, PhysicianGroup.GroupID
FROM Plans, TempPhyPlan, Physicians INNER JOIN PhysicianGroup ON
Physicians.UPIN = PhysicianGroup.UPIN
WHERE (((Physicians.LastName) Like "*" & [LN] & "*") AND (
(Physicians.FirstName) Like "*" & [FN] & "*"));

Any advice is greatly appreciated.

Thanks so much
Amy

 

[John Spencer (MVP)]

Well, I don't think I would be using LIKE and Wildcards to do this at all. You
are liable to be adding records for the wrong doctors.

Given Paul Johns and Pauline Johnson, your code could add records to both of
them if Paul Johns was the doctor you were attempting to add records for.

Now to your question.

One method would be to build a unique compound index in PhysicianPlan on the
three fields. This would automatically prevent any addition of duplicate
information, however, it would give you an error message.

Otherwise you will need to modify your SELECT Statement to exclude any values
that already exist.