Prevent Appending Certain Records

[Kirk P.]

I trying to prevent appending any records from my temp table into my perm
table that have the same run date. I thought this would do it, but no luck

INSERT INTO RunDate, Customer, City, State
SELECT RunDate, Customer, City, State
FROM tblTemp
WHERE tblTemp.RunDate<>[tblPerm]![RunDate];

I know identifying a primary key in my Perm table would prevent duplicates,
but in this case getting a primary key is impossible and the best I can do is
prevent the user from appending records that have been "run" on the same date
twice.

 

[John W. Vinson]

I trying to prevent appending any records from my temp table into my perm
table that have the same run date. I thought this would do it, but no luck

INSERT INTO RunDate, Customer, City, State
SELECT RunDate, Customer, City, State
FROM tblTemp
WHERE tblTemp.RunDate<>[tblPerm]![RunDate];

I know identifying a primary key in my Perm table would prevent duplicates,
but in this case getting a primary key is impossible and the best I can do is
prevent the user from appending records that have been "run" on the same date
twice.

You can't reference tblPerm because it's not *in the query*. You're also not
specifying the name of the table into which you wish to insert! Use a
"Frustrated Outer Join" query instead:

INSERT INTO tblPerm(RunDate, Customer, City, State)
SELECT tblTemp.RunDate, tblTemp.Customer, tblTemp.City, tblTemp.State
FROM tblTemp LEFT JOIN tblPerm
ON tblTemp.RunDate = tblPerm.RunDate
WHERE tblPerm.RunDate IS NULL;


Note that you're - in essence - defining RunDate as a primary key. Are you
quite certain that you can NEVER have two customers entered on the same date??
Maybe you want to join on all four fields instead of just the one.

John W. Vinson [MVP]