Append Query Results in Error

[skydivetom]

Experts:



I have run into an issue when running an APPEND query. Allow me to provide some background first.

- I have a MASTER table (unpopulated) that contains approximately 200 fields.
- I also have various organizational tables from which I need to (sequentially) append records into the MASTER TABLE.
- For the first four organizational files/tables, the APPEND query worked flawlessly and APPENDED nearly 100K records within a few seconds.
- I will say, however, NOT every single field (out of the 200) is populated by the first 4 tables. That's perfectly fine given that some organizations have more data than others.

Here's my current issue:
a. I have now transitioned into a 5th organizational table. That one contains ~15K records.
b. When running the APPEND query, however, I'm getting an error (see attached JPG).
c. My first step was to double-check that I am NOT attempting to append, e.g., a string into a numeric field (or any other data type violation).
d. Based on c., I'm relatively confident that all data types line up and the append query inserts text into TEXT and numbers into NUMERIC fields, etc.

Now, assuming that c. is a correct assumption (and I will double-check again), is there anything else that I should look into? Also, please let me point out that the MASTER table does NOT have a UNIQUE index. Finally, it is my understanding that all fields (by default) are set to "Required = No"; "Duplicates = Allowed". Is that a correct assumption?

So, again, I'd welcome any ideas that might allow me to identify as to what's causing the issue. Btw, for proprietary reasons, I do NOT feel comfortable posting some sample records due to business ethics. I understand that not seeing a sample record may hamper any specific error identification.

Anyhow, anything that might re-direct me to look into issues outside "data types" would be greatly appreciated.

Thank you,
Tom

[Micron]

My first step was to double-check that I am NOT attempting to append, e.g., a string into a numeric field (or any other data type violation).

That is exactly what the message says you are attempting to do. There are 4 reasons why you get this message along with the count of records that failed for each case. You have 2 in the first case. If you should have no null fields, it should be easy to find the fields that are the problem. If not, I guess you have n fields to check, where n is the count of all fields with a null value. But you must know what the data type is at the source as well.

[davegri]

The append did append all records (if you click YES). It just failed to include the contents of 2 fields. Maybe you can inspect the Master table and the 5th table to identify the failed fields.

[CJ_London]

Numeric to numeric is not sufficient. There are different sizes within numeric- byte, integer, long, double for example. Trying to append a long to an integer field will fail if the long value is outside the range for integers

[NoellaG]

Have you checked the date fields? Maybe some are in a date format the master table can't identify as date

[skydivetom]

Good morning, experts!

Thank you for the feedback... based on your info, I was able to successfully resolve the underlying differences in data types. Again, thank you for the pointers... as they say, "the devil is/was in the detail".

Cheers!!

Tom

[Micron]

A posted solution is always nice idea. It might help a future reader.
And thanks for the rep point!