Prev: The not "Max" records
Next: Calculated field
From: TommyP on 12 May 2010 12:01
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 are:
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.

From: KARL DEWEY on 12 May 2010 13:09
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 are:
> 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.
>
> .
>
From: TommyP on 12 May 2010 14:12
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]
>>
>> .

From: TommyP on 12 May 2010 15:33
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]
>>>
>>> .

From: KARL DEWEY on 12 May 2010 15:44
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]
> >>
> >> .
>
> .
>
 | 
Pages: 1
Prev: The not "Max" records
Next: Calculated field