Update Query Help

[Guest]

I have a macro that is clicked daily that imports a .txt file into my
database to a new table called NewExceptions (no primary key). The macro
then appends new records from the NewExceptions table to a table called
Exceptions (primary key is PartNo, ExceptionDate & Ind). My questions is:
the next thing I need to do is create an update query that populates a text
field called Closed in the Exceptions table with "yes" if there is not a
matching record in the NewExceptions table.

In a nut shell, if at the time of importing NewExceptions, there's not a
matching records in the Exceptions table then the record in the Exceptions
table is no longer valid and needs to be marked closed. Then I will append
the closed records to another table called ClosedExceptions and delete the
Closed records in the Exceptions table.

Thanks for your help

 

[David Lloyd]

Alex:

Your update query might look something like the following:

UPDATE Exceptions LEFT JOIN NewExceptions ON Exceptions.PartNo =
NewExceptions.PartNo
SET Exceptions.Closed = 'Yes'
WHERE (((NewExceptions.PartNo) Is Null));

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I have a macro that is clicked daily that imports a .txt file into my
database to a new table called NewExceptions (no primary key). The macro
then appends new records from the NewExceptions table to a table called
Exceptions (primary key is PartNo, ExceptionDate & Ind). My questions is:
the next thing I need to do is create an update query that populates a text
field called Closed in the Exceptions table with "yes" if there is not a
matching record in the NewExceptions table.

In a nut shell, if at the time of importing NewExceptions, there's not a
matching records in the Exceptions table then the record in the Exceptions
table is no longer valid and needs to be marked closed. Then I will append
the closed records to another table called ClosedExceptions and delete the
Closed records in the Exceptions table.

Thanks for your help