Append only new records/entire record makes unique identifier

Hello,
Â*
I have been searching the existing posts for an answer to my issue.Â* Many
have similar suggestions, but none of them quite fit my problem, so I
apologize if you feel this is redundant.Â* Here we go:
Â*
The ultimate goal is to append new records (and new records only) to an
existing table.Â* When the query runsÂ*to append records which do not already
exist in the table, I need the query to compare the entire recordÂ*in the
table to the entire record in the query (as opposed to one field where an
unmatched query would be useful).Â* My reason for this isÂ*that it is the
entire combination of fields in each record which make the records unique.
Â*
For example,Â*the records in the existing Table a
A-B-C-D
B-C-D-E
(With each letter being a field).
Â*
The Query will produce record results of:
A-B-C-D
B-C-D-E
C-D-E-F
Â*
I only wantÂ*C-D-E-F to append to the table.Â* I want the query to "see" that
the combination ofÂ*C-D-E-F does not exist in the table, and to append it.
Â*
I am a fairly new user to Access, so simplified explanations would be
greatlyÂ*appreciated.Â* Thank you so much.

I am guessing but the following would allow you to append --
d-e-f-a
f-s-f-a
but not --
A-B-C-D
B-C-D-E

INSERT INTO TableB ( ClientID, Countycode, entrydate, [Group], [Section],
Title )
SELECT TableA.ClientID, TableA.Countycode, TableA.entrydate, TableA.Group,
TableA.Section, TableA.Title
FROM TableA LEFT JOIN TableB ON (TableA.Title = TableB.Title) AND
(TableA.Section = TableB.Section) AND (TableA.Group = TableB.Group) AND
(TableA.entrydate = TableB.entrydate) AND (TableA.Countycode =
TableB.Countycode) AND (TableA.ClientID = TableB.ClientID)
WHERE (((TableB.ClientID) Is Null) AND ((TableB.Countycode) Is Null) AND
((TableB.entrydate) Is Null) AND ((TableB.Group) Is Null) AND
((TableB.Section) Is Null) AND ((TableB.Title) Is Null));

--
Build a little, test a little.


"TommyP" wrote:

Hello,

I have been searching the existing posts for an answer to my issue. Many
have similar suggestions, but none of them quite fit my problem, so I
apologize if you feel this is redundant. Here we go:

The ultimate goal is to append new records (and new records only) to an
existing table. When the query runs to append records which do not already
exist in the table, I need the query to compare the entire record in the
table to the entire record in the query (as opposed to one field where an
unmatched query would be useful). My reason for this is that it is the
entire combination of fields in each record which make the records unique.

For example, the records in the existing Table a
A-B-C-D
B-C-D-E
(With each letter being a field).

The Query will produce record results of:
A-B-C-D
B-C-D-E
C-D-E-F

I only want C-D-E-F to append to the table. I want the query to "see" that
the combination of C-D-E-F does not exist in the table, and to append it.

I am a fairly new user to Access, so simplified explanations would be
greatly appreciated. Thank you so much.

.

Thank you Karl, a component of this has worked.Â* It works in a Select Query
(where I did a quick run of it), and it returns those records not in the
table.Â* Perfect.
Â*
This is where I am running into an issue:Â* When I change it to an Append
Query, I have two sets of the same fields.Â* (Where the join lines exist).Â* I
have the fields from the Query, which actually contain the dataÂ*.. and then
all of the fields from the Table, with the criteria "IS NULL".Â* I need to
keep the criteria in the Table fields... however, I am getting an error
message of:Â* "DUPLICATE OUTPUT DESTINATION" .. obviously because we have two
of every field nowÂ*pulled down.Â* Any thoughts on how to remedy this?

Again, thank you for your time and thoughts.






KARL DEWEY wrote:
I am guessing but the following would allow you to append --
d-e-f-a
f-s-f-a
but not --
A-B-C-D
B-C-D-E

INSERT INTO TableB ( ClientID, Countycode, entrydate, [Group], [Section],
Title )
SELECT TableA.ClientID, TableA.Countycode, TableA.entrydate, TableA.Group,
TableA.Section, TableA.Title
FROM TableA LEFT JOIN TableB ON (TableA.Title = TableB.Title) AND
(TableA.Section = TableB.Section) AND (TableA.Group = TableB.Group) AND
(TableA.entrydate = TableB.entrydate) AND (TableA.Countycode =
TableB.Countycode) AND (TableA.ClientID = TableB.ClientID)
WHERE (((TableB.ClientID) Is Null) AND ((TableB.Countycode) Is Null) AND
((TableB.entrydate) Is Null) AND ((TableB.Group) Is Null) AND
((TableB.Section) Is Null) AND ((TableB.Title) Is Null));

Hello,

[quoted text clipped - 26 lines]

.

I have figured out the issue in regards to my last post.Â*Â*I simply deleted
the "Append To" section of the field for all of the Table Fields, which were
pulled down into the QueryÂ*criteria.Â*


TommyP wrote:
Thank you Karl, a component of this has worked.Â* It works in a Select Query
(where I did a quick run of it), and it returns those records not in the
table.Â* Perfect.
Â*
This is where I am running into an issue:Â* When I change it to an Append
Query, I have two sets of the same fields.Â* (Where the join lines exist).Â* I
have the fields from the Query, which actually contain the dataÂ*.. and then
all of the fields from the Table, with the criteria "IS NULL".Â* I need to
keep the criteria in the Table fields... however, I am getting an error
message of:Â* "DUPLICATE OUTPUT DESTINATION" .. obviously because we have two
of every field nowÂ*pulled down.Â* Any thoughts on how to remedy this?

Again, thank you for your time and thoughts.

I am guessing but the following would allow you to append --
d-e-f-a
[quoted text clipped - 20 lines]

.

Open in design view and ckick the checkbox of the fields from the table that
is being appended to.

--
Build a little, test a little.


"TommyP" wrote:

Thank you Karl, a component of this has worked. It works in a Select Query
(where I did a quick run of it), and it returns those records not in the
table. Perfect.

This is where I am running into an issue: When I change it to an Append
Query, I have two sets of the same fields. (Where the join lines exist). I
have the fields from the Query, which actually contain the data .. and then
all of the fields from the Table, with the criteria "IS NULL". I need to
keep the criteria in the Table fields... however, I am getting an error
message of: "DUPLICATE OUTPUT DESTINATION" .. obviously because we have two
of every field now pulled down. Any thoughts on how to remedy this?

Again, thank you for your time and thoughts.






KARL DEWEY wrote:
I am guessing but the following would allow you to append --
d-e-f-a
f-s-f-a
but not --
A-B-C-D
B-C-D-E

INSERT INTO TableB ( ClientID, Countycode, entrydate, [Group], [Section],
Title )
SELECT TableA.ClientID, TableA.Countycode, TableA.entrydate, TableA.Group,
TableA.Section, TableA.Title
FROM TableA LEFT JOIN TableB ON (TableA.Title = TableB.Title) AND
(TableA.Section = TableB.Section) AND (TableA.Group = TableB.Group) AND
(TableA.entrydate = TableB.entrydate) AND (TableA.Countycode =
TableB.Countycode) AND (TableA.ClientID = TableB.ClientID)
WHERE (((TableB.ClientID) Is Null) AND ((TableB.Countycode) Is Null) AND
((TableB.entrydate) Is Null) AND ((TableB.Group) Is Null) AND
((TableB.Section) Is Null) AND ((TableB.Title) Is Null));

Hello,

[quoted text clipped - 26 lines]

.

.