Append query not working

[Bindy68]

Hi

Sorry I'm a bit of an Access dunce sometimes - (and don't do the complicated programming stuff ) - I'm trying to run an append query to add some records to another table.

Ref No - short text
Stock Code - short text
Quantity - long integer

Both tables only have three fields as above and the field types are the same. The table I'm trying to append to however has the first two fields as key fields and the table I'm appending from doesnt.

The information I am trying to append does not appear in the destination table already and the source data does not seem to contravene the rules set by the two key fields in the destination table?

However I'm getting key violations so they wont append?

I suspect it may be because I've got some pretty complicated relationships between tables and this may be behind it?

Any ideas for how i can run this append query - will be part of a macro eventually???

Thanks!

 

[June7]

If you are getting key violations then obviously the data is in violation. What do you mean by 'complicated' relationships?

Post the attempted SQL statement. If you want to provide db for analysis, follow instructions at bottom of my post.

 

[Bindy68]

The SQL statement is:

INSERT INTO [Movements-Detail] ( [Ref No], Stockcode, Quantity )
SELECT [Temp Write Back].[Ref No], [Temp Write Back].[Component Stockcode], [Temp Write Back].Required
FROM [Temp Write Back];

 

[Bindy68]

See attached for zipped db.

 

[June7]

Why do you need to copy records?

When I try to manually enter those data into Movements-Detail, get error message 'related record required in Movements-Main'.

Main has a [Ref No] 1 and Back has [Ref No] "WO1". Attempt to save "WO1" into Details violates referential integrity.

I would not set compound primary keys because they are not saved as compound foreign keys, just set compound index.

Advise not to use spaces nor punctuation/special characters in naming convention, nor reserved words as names.

 

[The_Doc_Man]

Your DB has a compound PK across the fields [Ref No] and [StockCode], so the immediate thought would be a key violation. However, you do not show unique keys on the individual fields so duplicating a [Ref No] (which your temp table table does) shouldn't kick it out. And the records already in the detail table don't appear to conflict with the records in the temp table. Therefore, I understand your confusion.

But there IS a sort of "gotcha" trap that you fell into. Field [Required] matches an Access reserved word and this situation has been known to trip Access up very quickly. Can you try to change that field name and see if something different happens?