Urgent - Append query won't pass all selected records to table

[incagold]

Hi everyone,

Newbie back with another problem. This one is rather urgent. The problem is we have an Append Query that selects the proper number of records from the source table but does not append all of them to the destination table. We have no constraints of any kind on the destination table. What can we be doind wrong? Below is the sql for the query. Any help or advice you can provide would be greatly appreciated. Thanks in advance for your reply.

BEF

INSERT INTO PT ( MTD, BLOCK, MTDP, TDP, PART_NO, NHA, SER_NO, SITE_NO, DESCRIPTION, CROSS_REF, COMM_CD, QTY, BOE, F_COST, Q_SUBMIT_DT, INSTALL_DT, LEAD_TIME, LEAD_TIME_INT, WO_NO, PART_NOTES, VAR_REASON, TRN, TRN_C, BREF )
SELECT PT_DATA.MTD, PT_DATA.BLOCK, PT_DATA.MTDP, PT_DATA.TDP, PT_DATA.PART_NO, PT_DATA.NHA, PT_DATA.SER_NO_1, PT_DATA.SITE_NO_1, PT_DATA.DESCRIPTION, PT_DATA.CROSS_REF, PT_DATA.COMM_CD, PT_DATA.SER_NO_QTY_1, PT_DATA.BOE_1, PT_DATA.F_COST_1, PT_DATA.Q_SUBMIT_DT_1, PT_DATA.INSTALL_DT_1, PT_DATA.LEAD_TIME, PT_DATA.LEAD_TIME_INT, PT_DATA.WO_NO_1, PT_DATA.PART_NOTES, PT_DATA.VAR_REASON, PT_DATA.TRN_1, PT_DATA.TRN_C_1, PT_DATA.BREF_1
FROM PT_DATA
WHERE (((PT_DATA.SER_NO_1)='SN1'))
ORDER BY PT_DATA.MTD, PT_DATA.PART_NO;

 

[LNBruno]

Your query looks to be correct. How many rows are being returned by just the SELECT portion? How many rows are actually getting inserted? Are you getting any error messages?

My guess is that you may have missing or invalid values in the PT_DATA table. Is it possible that you're trying to INSERT a NULL from PT_DATA into a column in PT?

< M!ke >

 

[incagold]

Hi Mike,

Thanks for the quick reply. The select query returns 1196 records while the append only adds 768 to the new table. There are no error messages. There is null data being added in a number of fields. But both the source table and destination table have "not required" and "allow zero length" set to no for these fields.

Thanks again,
BEF

 

[formerTexan]

Sorry, I don't have any answers, only a few more things to check. Maybe they will provide a clue. This does sound like a data issue though.

If the SQL statement is being run from a VBA module, then make sure the warnings haven't been turned off (DoCmd.setwarnings false).

Just out of curiousity, try setting the table's text field property to allow Zero Length strings.

Try looking at the unappending records to see if there are any common characteristics, or any distinguishing characteristics from those that were appended (ie: do all the non-appended records contain Nulls, are there string length issues, etc.).

Cheers,
Bill

 

[scriverb]

When you execute the Append query are you being prompted with warnings detailing the number of records being appended. And, when the actual append takes place are you getting a warning message saying a certain number of them are not being appended due to indexing duplication? If you are doing this within code and have set the warnings off then just comment them out so that we can see what exactly is being appended and what is not. I would look at indexes and determine if there are any that are set to Duplicates NO. This will keep records from appending to a table.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[JonFer]

Relationships with enforce integrity set will prevent you from inserting into a child table without a corresponding record in the parent table (the one-side of a one-to-many relationship).

As formerTexan indicated, you may have a zero-length string in PT_DATA (if it was loaded from another DB or had old data in it). On the text fields, add a criteria of "" (on separate rows if doing multiple fields at once) to see if that returns any rows.

To determine which field is the problem, run the Datasheet view of the query (after checking that the fields are in the same order as in the table - you can rearrange the display columns of the table if necessary), copy a row you know isn't being appended, and then use Paste Append. You should get an error message indicating which field is the problem.

 

[incagold]

Morning formertexan and scriverb,

Found the problem thanks to the suggestions of all who replied. There was one field with the property allow zero length string set to "no". Corrected this and it solved the issue. I thought we had checked all the fields, but aparently we missed this one. Thanks to all for your help and rapid response. Your suggestions led us to the cause and subsequent correction.

BEF