i am new to access, when i import i have records deleted due to key violation, how can i get these deleted records to an error table so i know that the right record was deleted?
i am new to access, when i import i have records deleted due to key violation, how can i get these deleted records to an error table so i know that the right record was deleted?
Are you importing into an existing table? If so, try importing into a different table name. If you need to define the fields, don't define any as autonumber or primary key.
actually just begining a table...cannot go on until i get a report on what it is deleting due to duplicates, ie...sometimes CSR's enter a payment as $0, realize their mistake then reenter with actual payment.
Sometimes access will create a "import error" table, have you looked?
I have, I have seen that in the past but not always and not on this one
you suggested that you were just starting a table. However, to get a violation as you described, aren't you importing values into a table where at least you have defined a field as a primary key?
If you are still stuck, try importing into a less restrictive table until you get it sorted out.
Yes like I stated earlier my problem is that CSR's enter data twice, they forget to enter a $ paid then reenter the data with a $ amount. I did forget to state that the information on all of this is sent to me via excel which I import into this table, the primary key is date paid and work order date, even if they pay twice in one day I only give credit to the collection rep once.
Since I doubt that you can eliminate the entry errors, importing to a Temp table would prevent destruction of data. Then you could run a query (or several queries) to present the duplicate records, delete the errors and update your permanent table. Perhaps you could develop a screening query that eliminates most, if not all, of the duplicates.
I agree with hertfordkc you need to work with the raw data, then you decide whch records to keep/omit/process...
Seem to have a couple of choices
- you could try to get cleaner data from Excel before moving to Access. or
- you could import all the data into an Access table with no primary keys, and no unique indexes on fields.
Then work out some queries (logic) to determine what is "wrong" with records from a business view --duplicate payments, $0 payment etc whatever you decide makes a record "wrong".
Reconcile all of the wrong records, then build records to go to your "good" table.
When access creates import errors it's usually because of wrong data types, text in numeric fields etc. It doesn't automatically know that a payment of $0 is incorrect.