q. Append Query

[David Levins]

I am using an append query to append data from a query to an existing table
with the exact same fields. There is a field called Unique Identifier. It
contains only unique records in both the source data and the target data
(table being appended to). However when the two data sets are put together
(via an append query) there will be redundant values in the Unique
Identifier field. Would like to filter out the records with a redundant
values during the append query. Currently I run the append query then, group
the data by the Unique Identifer field. It works, but think/hope there is a
better way. Any suggestions? TIA David

 

[Vincent Johns]

I am using an append query to append data from a query to an existing table
with the exact same fields. There is a field called Unique Identifier. It
contains only unique records in both the source data and the target data
(table being appended to). However when the two data sets are put together
(via an append query) there will be redundant values in the Unique
Identifier field. Would like to filter out the records with a redundant
values during the append query. Currently I run the append query then, group
the data by the Unique Identifer field. It works, but think/hope there is a
better way. Any suggestions? TIA David

Here's an example, in which both Tables have a unique
[Unique_Identifier] field.


[Source] Table Datasheet View:

Unique_Identifier
-----------------
2
3
4
6
7
8

[Target] Table Datasheet View (before appending):

Unique_Identifier
-----------------
1
2
5
7

We want 2 and 7 not to be appended. The Append Query looks like this:

[Q_AppendUnique] SQL:

INSERT INTO Target
SELECT Source.*
FROM Source LEFT JOIN Target
ON Source.Unique_Identifier = Target.Unique_Identifier
WHERE (((Target.Unique_Identifier) Is Null));

[Q_AppendUnique] Query Datasheet View (list of records to be appended;
note that 2 and 7 are missing):

Unique_Identifier
-----------------
3
4
6
8

Running this Query adds 4 records to [Target].

[Target] Table Datasheet View (after appending):

Unique_Identifier
-----------------
1
2
3
4
5
6
7
8


-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[John Vinson]

I am using an append query to append data from a query to an existing table
with the exact same fields. There is a field called Unique Identifier. It
contains only unique records in both the source data and the target data
(table being appended to). However when the two data sets are put together
(via an append query) there will be redundant values in the Unique
Identifier field. Would like to filter out the records with a redundant
values during the append query. Currently I run the append query then, group
the data by the Unique Identifer field. It works, but think/hope there is a
better way. Any suggestions? TIA David

The simplest solution is to make Unique Identifier the Primary Key of
the target table. Duplicate data will be rejected (with a warning
message which can be suppressed if you like) and never make it into
the table in the first place.

John W. Vinson[MVP]