Append Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

When I run an append query, do I *HAVE* to trick access into adding the new
data to the existing data table by creating a relationship between the two
tables? To get it to add all the new data correctly, I have had to create a
'phony' relationship between a variable with a value (from the new data
table) and a variable with no value (in the old data table) selecting the
option to "Include all records from the new data table and only thos records
from the old data table where the join fields are equal".

Is there not a way to SIMPLY append new data to a table with old data
without creating this relationship?

Note: The new data table does not have the exact field names / all of the
fields that the old data table does. That is the main reason I am migrating
them into a standard table.

Thanks in advance for your help! I can really use it!
 
If your fields are properly set in the table and you don't have any
relationships between tables set that preclude adding data, there should be
no problem appending data. In fact, I do it all of the time.

However, if you are getting errors around duplicate values and so it doesn't
append all records then something is wrong either with the data coming in
(duplicates that violate the set primary key) or you've got the key set wrong
in the table.

Are you using a composite key per chance?
--
Bob Larson
Access World Forums Super Moderator
____________________________________
Access 2000, 2003, 2007, SQL Server 2000, Crystal Reports 10/XI, VB6
WinXP, Vista
 
Yeah, I do not have anyrealtionships between either of the tables. I also do
not have any primary key set in either table. Is that ok in running an
append query? I must be missing something really simple.

I have fields A, B, C, D, E, F, G, H, & I in the existing table. The table
has about 110,000 records.
I have a new table with new data with only fields B, C, D, E & F. This
table has only 6,000 records.
I simply want to 'tack on' these new 6,000 records at the end of the
existing table. I realize that when I append it, the A, G, H, & I fields;
will show up blank for those new 6,000 records...and that is ok!

How do I do what I explained above? I have no primary keys set and no
relationships between the tables.

Please help?!?!?
 
Yeah, I do not have anyrealtionships between either of the tables. I also do
not have any primary key set in either table. Is that ok in running an
append query? I must be missing something really simple.

I have fields A, B, C, D, E, F, G, H, & I in the existing table. The table
has about 110,000 records.
I have a new table with new data with only fields B, C, D, E & F. This
table has only 6,000 records.
I simply want to 'tack on' these new 6,000 records at the end of the
existing table. I realize that when I append it, the A, G, H, & I fields;
will show up blank for those new 6,000 records...and that is ok!

How do I do what I explained above? I have no primary keys set and no
relationships between the tables.
Click to expand...

Let's call these OldTable and NewTable. Simply create a new query based on
NewTable; select all five fields into the query grid. Don't join the old table
to the query, it comes in the next step.

Change it into an Append query using the Query menu option, or the query type
tool in the toolbar. You'll be asked what table you want to append to - select
OldTable (or whatever its real name is).

Run the query by clicking the ! icon.

This may fail if there is a unique Index (such as a primary key) on one of
these five fields, and adding records would create a duplicate; it will also
fail if any of A, G, H and I are required fields.

If you run it and get an error message, read the message and consider what
it's telling you - if it makes no sense, post it here verbatim and someone
should be able to help interpret it.

John W. Vinson [MVP]
 
Back
Top