Referential Integrity

[Paul H]

I am trying to patch together some data that was entered into of copy of my database. I know I can link to it, but I want to consolidate all this into my home database so I can do a clean split later. I am more that halfway there, but I ran into something that puzzles me. There are three tables with autonumber primary keys. First I deleted all the relationships in the destination tables and the data, then did a Compact and Repair to reset the numbers. So far so good. I copied and pasted my data successfully. All the ID numbers remained the same. Again these tables are all related by these ID numbers. In the other database these tables are linked and have Referential Integrity enforced, including cascading deletes. Here are the three tables



tblTransactions (Primary Key - Transaction_ID, Foriegn Key - Submission_ID)
tblSubmissions (Primary Key - Submission_ID, Foriegn Key - Batch_ID)
tblBatchID (Primary Key - Batch_ID)

I think I've laid this out correctly.

First I relinked tblTransactions to tblSubmissions successfully using Referential Integrity. My last task is to link tblBatchID to tblSubmissions and Access tells me it can't create this relationship and enforce Referential Integrity.

I ran a query to see if there were any records in tblBatchID that had no related records tblSubmissions and there were none.

I'm stuck. Any ideas?

Paul

[jzwp11]

Are there any records in the submission table where the Batch_ID field is null or where the Batch_ID field contains a value that is not in the batch table? I assume that the batch_ID primary key field and the corresponding foreign key field are of the same datatype, correct?

[June7]

Reread original post, removed comment.

[Paul H]

I'm going to mark this solved to take it off the table. This is a mute issue because we are going to purge our tables before we go live. There's very little gain here.

Thanks anyway though.