Need help with SQL statement

[Tara]

I need help with the structure of the following SQL statement. The Append
query works, but I only want it to append records that have not already been
appended. I know the WHERE statement needs to be adjusted, but I'm not sure
how. Thanks for any input!

INSERT INTO tblTransition( ScreenNum )
SELECT tblDemographics.ScreenNum
FROM tblDemographics
WHERE(((tblDemographics.ScreenNum)=[Forms]![frmTransition]![ScreenNum]));

 

[John W. Vinson]

I need help with the structure of the following SQL statement. The Append
query works, but I only want it to append records that have not already been
appended. I know the WHERE statement needs to be adjusted, but I'm not sure
how. Thanks for any input!

INSERT INTO tblTransition( ScreenNum )
SELECT tblDemographics.ScreenNum
FROM tblDemographics
WHERE(((tblDemographics.ScreenNum)=[Forms]![frmTransition]![ScreenNum]));

Actually, the WHERE clause is not the best place: use a JOIN instead to append
only UNMATCHED records:

INSERT INTO tblTransition(ScreenNum)
SELECT tblDemographics.ScreenNum
FROM tblDemographics LEFT JOIN tblTransition
ON tblDemographics.ScreenNum = tblTransition.ScreenNum
WHERE(((tblDemographics.ScreenNum)=[Forms]![frmTransition]![ScreenNum]))
AND tblTransition.ScreenNum IS NULL;

HOWEVER... since you're just inserting one record, and one field, why use an
append query at all? What's frmTransition's Recordsource - can't you just
update the table directly, perhaps using a combo box based on tblDemographics
to select the screennum?

 

[KARL DEWEY]

INSERT INTO tblTransition( ScreenNum )
SELECT tblDemographics.ScreenNum
FROM tblDemographics
WHERE ([tblDemographics].[ScreenNum] <> [Forms]![frmTransition]![ScreenNum]);

 

[Tara]

Thanks for getting back with me John. The recordsource for frmTransition is
a query that is based on a different table in the database, so unfortunately,
I can't update the table directly. The JOIN solution you gave worked
perfectly though. Thank you!

I need help with the structure of the following SQL statement. The Append
query works, but I only want it to append records that have not already been
appended. I know the WHERE statement needs to be adjusted, but I'm not sure
how. Thanks for any input!

INSERT INTO tblTransition( ScreenNum )
SELECT tblDemographics.ScreenNum
FROM tblDemographics
WHERE(((tblDemographics.ScreenNum)=[Forms]![frmTransition]![ScreenNum]));

Actually, the WHERE clause is not the best place: use a JOIN instead to append
only UNMATCHED records:

INSERT INTO tblTransition(ScreenNum)
SELECT tblDemographics.ScreenNum
FROM tblDemographics LEFT JOIN tblTransition
ON tblDemographics.ScreenNum = tblTransition.ScreenNum
WHERE(((tblDemographics.ScreenNum)=[Forms]![frmTransition]![ScreenNum]))
AND tblTransition.ScreenNum IS NULL;

HOWEVER... since you're just inserting one record, and one field, why use an
append query at all? What's frmTransition's Recordsource - can't you just
update the table directly, perhaps using a combo box based on tblDemographics
to select the screennum?

 

[KARL DEWEY]

Did not read it correctly.
--
Build a little, test a little.

INSERT INTO tblTransition( ScreenNum )
SELECT tblDemographics.ScreenNum
FROM tblDemographics
WHERE ([tblDemographics].[ScreenNum] <> [Forms]![frmTransition]![ScreenNum]);

--
Build a little, test a little.

I need help with the structure of the following SQL statement. The Append
query works, but I only want it to append records that have not already been
appended. I know the WHERE statement needs to be adjusted, but I'm not sure
how. Thanks for any input!

INSERT INTO tblTransition( ScreenNum )
SELECT tblDemographics.ScreenNum
FROM tblDemographics
WHERE(((tblDemographics.ScreenNum)=[Forms]![frmTransition]![ScreenNum]));