I am stuck creating an Append query

[Dudley Wright]

I am a new user of Access with no VB experience. Started with it
about a month ago, and my eyes are bloodshot from reading. I have
reached a roadblock in developing my application. Any help will be
appreciated.
I receive a comma delimited text file from a mainframe download.
Records can hang around for months (this is the beginning of my
difficulty). I have worked out an import procedure to a table which
runs from a macro. I then run a query which isolates a special group
of records which are part of the download. These are deleted.
Now comes my problem. I need to append the remaining records to
another table which contains many thousands of records. The table has
a three field primary key: Order, Project, and Lot. Any of them can
be appear in the table any number of times, but obviously the
combination of the three can not be repeated. In addition to the
three key fields, there are a number of other fields, such as Date1,
Date2, Comments (this is a Memo field), Ticket1 and Ticket2.

I need to create an append query which will append the second table,
but will not overwrite pre-existing records in the table. For
example, the record for Order = 5, Project = QRB7 and Lot = 35A can
already exist, with date and comment data having been entered and can
be included in the downloaded text file. When the table is appended,
I can not lose the comment and date data. The comments and dates are
not part of the download but is entered in the database, using a form
with subform.
I hope I have explained my self clearly.
Many thanks for any guidance.
Dudley

 

[Randy Harris]

I am a new user of Access with no VB experience. Started with it
about a month ago, and my eyes are bloodshot from reading. I have
reached a roadblock in developing my application. Any help will be
appreciated.
I receive a comma delimited text file from a mainframe download.
Records can hang around for months (this is the beginning of my
difficulty). I have worked out an import procedure to a table which
runs from a macro. I then run a query which isolates a special group
of records which are part of the download. These are deleted.
Now comes my problem. I need to append the remaining records to
another table which contains many thousands of records. The table has
a three field primary key: Order, Project, and Lot. Any of them can
be appear in the table any number of times, but obviously the
combination of the three can not be repeated. In addition to the
three key fields, there are a number of other fields, such as Date1,
Date2, Comments (this is a Memo field), Ticket1 and Ticket2.

I need to create an append query which will append the second table,
but will not overwrite pre-existing records in the table. For
example, the record for Order = 5, Project = QRB7 and Lot = 35A can
already exist, with date and comment data having been entered and can
be included in the downloaded text file. When the table is appended,
I can not lose the comment and date data. The comments and dates are
not part of the download but is entered in the database, using a form
with subform.
I hope I have explained my self clearly.
Many thanks for any guidance.
Dudley

In Access, the default behavior of an append query, is to only add the
records that won't violate the primary key. (Or any other unique
index/constraint). Records that already contain the combination of Order,
Project, Lot will be unchanged.

 

[Dudley Wright]

Thanks, it looks like I didnt need to ask.
I didnt understand the message I received about Key Violations when I
ran the query. You put me on the right track.