Add/Edit Records

  • Thread starter Thread starter Brian Branco
  • Start date Start date
B

Brian Branco

need a program that will take records from an input
file and add them to a table if they don't already
exist. If they exist, update just the description.

Example:

INPUT_TABLE
CODE DESCRIPTION

PERMANENT_TABLE
CODE DESCRIPTION

I need it to skip through the INPUT_TABLE and find the
record in the PERMANENT_TABLE. If it does not find it,
add the CODE and DESCRIPTION to the table, otherwise just
update the DESCRIPTION.

I am new to Access, but not new to programming. It's a
simple program in Visual Foxpro, but I am struggling with
it in ACCESS.

Thanks


..
 
Two queries will do it.

Update existing records ...

UPDATE tblPermanent INNER JOIN tblInput ON tblPermanent.Code = tblInput.Code
SET tblPermanent.Description = [tblInput].[Description]
WHERE (((tblPermanent.Description)<>[tblInput].[Description]));

Add new records ...

INSERT INTO tblPermanent ( Code, Description )
SELECT tblInput.Code, tblInput.Description
FROM tblInput
WHERE (((tblInput.Code) Not In (SELECT Code FROM tblPermanent)));

An alternative way to achieve the latter is ...

INSERT INTO tblPermanent ( Code, Description )
SELECT tblInput.Code, tblInput.Description
FROM tblInput LEFT JOIN tblPermanent ON tblInput.Code = tblPermanent.Code
WHERE (((tblPermanent.Code) Is Null));

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
Back
Top