Access 3022 error Unable to add records to table RRS feed

  • Question

  • I am working with donation records where there are variables that are "Date",  "Asset",  "Amount", "DepositCode".  Works fine when there is only a single deposit on a given day into a given Asset (bank Account).  It will handle single deposits to one or multiple bank accounts on a given day.  Has worked for years.  Now I am trying to allow more than one deposit to any bank account on the same day. I had to add the "Deposit Code" to the input (tGifts) table as an integer and the queries that use it.  I create a "Today's Deposits" query that groups by the the date+Asset+ DepositCode combinations that exist, i.e. money on the same day for two banks with two deposits each would give me 4 deposit records.  When I try to enter these 4 summary records into the tDeposits table (used for reconciliation with bank statements) I get the 3022 error. 

    By definition, they are unique.  And I don't try to index them.  The error message says the record(s) would create duplicate values in the Index, Primary Key, or relationship. I don't have any exclusion data in the tDeposits table, and there are no relationships between new deposit records and old deposit records I'm trying to copy into the tDeposits table.  The simple combination of the date and deposit number makes each of those transactions unique.  Is there something I can do to convince the compiler of that?

    Thursday, February 28, 2019 10:09 PM

All replies

  • If the Deposit Code is a unique Primary Key in the table you are copying FROM and If you are attempting to copy it as well as the other fields, it will create a 'duplicate values' error. Try using an APPEND query to add the records into the new table from the old table NOT INCLUDING the Deposit Code field. If the new table already has a unique Deposit Code Primary Key, then the records will append properly and ACCESS will automatically assign a new unique Deposit Code Primary Key.

    Thursday, February 28, 2019 10:30 PM