Append Query list of records not appended

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

Guest

I am trying to append a set of records to another table, but not all records
are being updated. Is there a way to get a list of which records were not
transfered and then what is the problem with each record that was not
appended?

Thanks for any help.
Sophia
 
You are mixing terms --
- append a set of records to another table - append
- not all records are being updated - updated
- which records were not transfered - transfered
Append is to add records and leave the orignal intact.
Update is to revise data withing an existing record.
Transfer is to move a record to another table so that it no longer exist in
the orignal table.

Use an Unmatched query to find those that were not appended.

NOTE - If you have a primary key (a field or combination of fields) it will
be unique and any record that has the same data as exist in the target table
will not be appended.
 
You're right, I did use alot of incorrect terms. What I wanted to do was to
append records from one table to another table.

I made a copy of a table, structure only, and then imported records from an
Excel file to the new table. This worked fine. Then I tried to import the
records from the same Excel file to the original table, but only 900 records
were imported. I can't understand why the other 400 records were not
imported. I have looked at the ones that were not imported and I don't see
the problem. I just started to try to import one of the records, changing
fields, but still it will not import. How can I find out why a specific
record was not imported?
Sophia
 
I am trying to append a set of records to another table, but not all records
are being updated. Is there a way to get a list of which records were not
transfered and then what is the problem with each record that was not
appended?

Thanks for any help.
Sophia
Click to expand...

Unless you suppressed the error message display, you should have
gotten a message when the query ran, on the order of "400 records were
not appended due to <some problem>". This message isn't specific to
individual records and may be hard to figure out, but it at least
gives you a starting point!

Did you get such an error? If so, what was the problem stated?

John W. Vinson[MVP]
 
The error message that I get when I try to import the Excel file into the
original table (even though the entire Excel file imported everything into a
copy of the original table when I copied the "structure only")
Error
The contents of fields in 0 records were deleted and 411 records were lost
due to key violations....If records were lost, either the records you pasted
contain primary key values that already exist in the destination table or
they violate referential integrity rules for a relationship table defined
tables.

I know that there are 38 duplicate primary keys, but I don't see where the
other error description is valid.
Sophia
 
Use this query to check to see exactly how many of the primary keys are
duplicated.

SELECT YourTable.PK, Count(YourTable.PK) AS CountOfPK
FROM YourTable
GROUP BY YourTable.PK
HAVING (((Count(YourTable.PK))>1));
 
Karl: I don't understand which table names to put in the query. Should I be
substituting both of the table names or just one? I know from my own query
that when I compared the two tables with a query, I found that there were
38 records that had a duplicate PK. The other 403 are a mystery. I typed
one of the records that did not import, into the orginal table to see if
there was a problem with the field data, but there wasn't.
Sophia
 
I found the problem, but not the solution. It is not importing records into
the original table when there is no data in a specific field. But, that
field should be blank in some cases. When I look at the table design, the
only difference between that field and other fields were data was imported is
the Indexed is set to "No", whereas others say "yes". The field in question
looks up data from a table via a combo box. But, I don't need to enter a
value in that field if I type in a record. What can I do to make that field
accept the other records?
Sophia
 
Make sure
Required says No
Allow zero lenght Yes
Indexed No If it is yes you have to check the index for Ignore Nulls.
You do this by clicking in the field name and then menu VIEW - Indexes.
Click in the field name and look in the window of the index window.
 
Yes, I have Required "No", Allow Zero Length "Yes" and Indexed "No".
I removed the relationship between the field (that was blank and that was
not importing) and the Lookup table. It's still odd that I can type in a
record and leave that field blank, with no problem, but I can't import a
blank. I guess I don't really need that relationship, but I still want it to
lookup the values from another table, if applicable. So, now it fully
imports.
Thanks everyone for your help and guidance. Although if someone has any
further comments, I would appreciate them.

Sophia
 
Back
Top