"can't append all the records" but no sign of common causes

Philocthetes

Has a great deal to learn
Local time
Today, 04:33
Joined
Dec 19, 2017
Messages
28
This might be another case where I'm doing something that seems logical to me but is database-brainless.

I've got a 'central' table, Titles, with an AutoNumber PK, Title_ID, which is an FK on several other tables, e.g. Print_QC. When I try a simple append of Title_ID from Titles to Print_QC, it doesn't "add 277 record(s)...due to key violations."

The destination field is neither AutoNumber nor a PK. I thought it might be the relationship between the FK and PK Title_ID fields, so I deleted the relationship. No change in behavior.:banghead:
 
Sounds like you have already added those records so you can't do so again

OR the destination table PK field is NOT autonumber and therefore isn't being populated
 
That message says you are trying to add 277 records, not one record. Is there possibly a JOIN clause here? Because if you THINK you are adding 1 record and are actually adding 277 records, there is what we call a Cartesian JOIN that causes multiplication of records.
 
Perhaps you could show the sql of the append query, and the design of table(s) involved.
 
What, exactly, does this describe?
...a simple append of Title_ID from Titles to Print_QC...
What is "a simple append?" What is the exact data to be appended? What is the exact structure of the target table, including indexes that might specify individual fields as being required to be unique?
Without that info, we are just guessing.
hth
Mark
 
That message says you are trying to add 277 records, not one record. Is there possibly a JOIN clause here? Because if you THINK you are adding 1 record and are actually adding 277 records, there is what we call a Cartesian JOIN that causes multiplication of records.

Just one problem Doc - 277 is a prime number so Cartesian join can't be the answer here if no records are appended
 
I think we have to wait for the OP to give more info. As MarkK said --without more info we're just guessing.
 
Thanks to all for helping me learn how to ask better questions.

I've attached a copy of the database and also opened a OneDrive folder, uflorida-my.sharepoint.com/:f:/g/personal/gwswicord_ufl_edu/EpUl2Nja57NCudKky_wi6FIBwVTkDBDa-i74Se_2GVi6xw?e=3itY0B, in case anyone might find that an easier way to snag the file and/or pass examples to me.

All of my PKs are AutoNumber. The destination table has zero records.

There is a join behind the update query, Titles_and_Holdings_Outer (although I'm still weak on the Inner/Outer difference).

The data that I'm trying to append is the PK from tblTitles, Title_ID, to the FK field in tblPrint_QC. I currently have the destination field indexed, duplicates allowed (didn't change it b/c source is no dupes).
 

Attachments

Could you describe what to do, to see what problem you've?
 
Your problem is the myriad of links added to the relationships window.

For example, you have 6 different tables joining to the TableID field in the Titles table with similar 'knots' and circular joins elsewhere.

It's a wonder if anything involving two or more tables is updateable

You need to strip that all right back removing all but the minimum number of links needed between tables

EDIT:
For info, your last post is unclear as it talks about Titles_and_Holdings_Outer being used as an update query although the next sentence reverts to discussing an append query

FWIW - Although I don't believe this is what you want, I can convert the Titles_and_Holdings_Outer query into an append query and append the same single record over & over again into Print_QC

Code:
INSERT INTO Print_QC ( Title_ID, Holding_ID )
SELECT Titles.Title_ID, Holdings.Holding_ID
FROM Titles INNER JOIN Holdings ON Titles.Current_Catalog_ID = Holdings.Current_Catalog_ID;
 
Last edited:
Ridders said:
Just one problem Doc - 277 is a prime number so Cartesian join can't be the answer here if no records are appended

Colin, doesn't matter that 277 is or isn't prime if the table triggering the unexpected join happens to have 277 records in it. Or 278 and only the first one makes it but the next 277 don't.
 
Colin, doesn't matter that 277 is or isn't prime if the table triggering the unexpected join happens to have 277 records in it. Or 278 and only the first one makes it but the next 277 don't.

The quote you used did state explicitly .... IF no records are appended
OK - agreed it could be 277 in one and one in the other ....
 
The join query is used by the append query. Re all the relationships, I have no doubt that they are my major design problem currently.

I built the relationships because when I was normalizing (or so I thought), I was trying to ensure that records in various tables could connect back to the core table Titles. The db is for tracking digitization work on individual master's theses. In library catalog terms, a top-level object is a title (system number, bibliographic ID). Each title can have multiple holdings (branch and item specific info). Titles are entangled with author/estate contacts, requests to library branches, workflow checkpoints…lots of moving parts.
Partly in response to another thread here about data validation, I’ve cleared out all of the relationships to the Users table because I can use a locked, default value control in each user’s front end. The Notes table has ten relationships because I’m messing around with ideas for enabling individual notes, tagged by user and date, for the other tables. I want a system where I can check an individual’s notes and where users can’t accidentally overwrite older notes.
So what obvious-to-pros thing am I missing about that latter notes-for-all table notion?
 

Attachments

I've only just seen your unmatched query & append query in the unassigned objects

I've made a new append query qryAppendTitle2PrintQC that does the same job as both of these & it now works as it should:

Code:
INSERT INTO Print_QC ( Title_ID )
SELECT Titles.Title_ID
FROM Titles LEFT JOIN Print_QC ON Titles.Title_ID = Print_QC.Title_ID
WHERE (((Print_QC.Title_ID) Is Null));

Went back to the original db & it didn't work ... so removing some of the links was necessary as I suspected.
I still think there are too many joins but not knowing your setup I'm reluctant to remove any myself
There are now several 'detached tables' with no links - remove these from the window?

As for the Notes table, its hard to give advice when the table is empty. Perhaps remove all FK fields from that table & instead add NotesID as an FK field in some of the other tables currently linked to it.

Together with other existing joins you might only need to link to Titles table as that is already linked to almost everything else
So you'd still have links but simplified
 

Attachments

Users who are viewing this thread

Back
Top Bottom