Results 1 to 14 of 14
  1. #1
    doobybug is offline Advanced Beginner
    Windows 98/ME Access 2003
    Join Date
    Jan 2009
    Posts
    39

    Referential Integrity problem?

    Hi I have these 3 tables that I included a picture of and I think I have a problem with the referential integrity...do you think I can subdivide the tables more? I am thinking that I have this problem as when I try to import data to tblCaseInfo it is not appending.

    Click image for larger version. 

Name:	Capture.PNG 
Views:	24 
Size:	13.9 KB 
ID:	28774

  2. #2
    doobybug is offline Advanced Beginner
    Windows 98/ME Access 2003
    Join Date
    Jan 2009
    Posts
    39
    Bdw 1 notary can have multiple indices and 1 notary can have multiple cases

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    at the moment you do not have referential integrity between tblNotaries and tblNotaryIndex. Add an autonumber primary key to tblNotaryIndex. removing the existing multiple primary key. Then you can set referential integrity. If the reason you had a multi field index was to prevent duplicates, you can still set that as a separate, non primary index - click on the indexes option on the ribbon.

  4. #4
    HiTechCoach's Avatar
    HiTechCoach is offline MS MVP - Access Expert
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2010
    Location
    Oklahoma, USA
    Posts
    702
    Quote Originally Posted by doobybug View Post
    Hi I have these 3 tables that I included a picture of and I think I have a problem with the referential integrity...do you think I can subdivide the tables more? I am thinking that I have this problem as when I try to import data to tblCaseInfo it is not appending.

    Click image for larger version. 

Name:	Capture.PNG 
Views:	24 
Size:	13.9 KB 
ID:	28774

    When you append data to tblCaseInfo, do you set a value in the field NotaryRefNo? If not and yu let it default to Zero, then you would violate RI.

    If you need to leave the NotaryRefNo not set then you need to:


    First, make sure the field tblCaseInfo.NotaryRefNo is set to not required.

    Next, do one of the following:

    1) make sure the field tblCaseInfo.NotaryRefNo in the table's design does not have a default value

    or

    2) Append Null into the field tblCaseInfo.NotaryRefNo

  5. #5
    doobybug is offline Advanced Beginner
    Windows 98/ME Access 2003
    Join Date
    Jan 2009
    Posts
    39
    Quote Originally Posted by Ajax View Post
    at the moment you do not have referential integrity between tblNotaries and tblNotaryIndex. Add an autonumber primary key to tblNotaryIndex. removing the existing multiple primary key. Then you can set referential integrity. If the reason you had a multi field index was to prevent duplicates, you can still set that as a separate, non primary index - click on the indexes option on the ribbon.

    I added the primary key in tblNotaryIndex but still for some reason I cannot enforce referential integrity. NotaryRefNo in tblNotaries is indexed Yes no duplicates and in tblNotaryIndex is indexed Yes Duplicates Ok. Do you think that this is the reason I cannot enforce ri?

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Do you think that this is the reason I cannot enforce ri?
    no - NotaryRefNo in tblNotaries needs to be indexed Yes no duplicates and joined to NotaryRefNo in tblNotaryIndex which needs to be indexed Yes Duplicates Ok.

    It may be that tblNotaryIndex has orphan records where NotaryRefNo in tblNotaryIndex does not have a matching NotaryRefNo in tblNotaries. Either way, you should be getting a message which is telling you why you cannot enforce referential integrity.

  7. #7
    doobybug is offline Advanced Beginner
    Windows 98/ME Access 2003
    Join Date
    Jan 2009
    Posts
    39
    Not really...I think all my records match...take a look. I also included the error message givenClick image for larger version. 

Name:	Capture.PNG 
Views:	15 
Size:	11.5 KB 
ID:	28783Click image for larger version. 

Name:	Capture1.PNG 
Views:	15 
Size:	11.0 KB 
ID:	28784Click image for larger version. 

Name:	Capture2.PNG 
Views:	15 
Size:	34.4 KB 
ID:	28785

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    can you send a screen shot of your indexes in tblNotaryIndex as well

  9. #9
    doobybug is offline Advanced Beginner
    Windows 98/ME Access 2003
    Join Date
    Jan 2009
    Posts
    39
    Click image for larger version. 

Name:	Capture.PNG 
Views:	15 
Size:	18.2 KB 
ID:	28791 Here it is...thank you so much

  10. #10
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    I presume the parent field has the same number of characters specified? Otherwise, it might be to be to do with the other relationship in the table - shelfID.

  11. #11
    doobybug is offline Advanced Beginner
    Windows 98/ME Access 2003
    Join Date
    Jan 2009
    Posts
    39
    Quote Originally Posted by Ajax View Post
    I presume the parent field has the same number of characters specified? Otherwise, it might be to be to do with the other relationship in the table - shelfID.
    Yes they are the same size ie: 4 and have both an input mask \R999;;

    I removed the relationship of shelfID but same problem

  12. #12
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    I don't see why this is an issue - can you upload a copy of the db (compacted and zipped), having removed any sensitive data

  13. #13
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Ref Post #6
    Quote Originally Posted by doobybug View Post
    I added the primary key in tblNotaryIndex but still for some reason I cannot enforce referential integrity. NotaryRefNo in tblNotaries is indexed Yes no duplicates and in tblNotaryIndex is indexed Yes Duplicates Ok. Do you think that this is the reason I cannot enforce ri?
    If tblNotaryIndex.NotaryRefNo now is an autonumber type and tblNotaries.NotaryRefNo is still a TEXT type, you will not be able to set RI.
    Linking fields MUST be of the same type.

  14. #14
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by doobybug View Post
    Bdw 1 notary can have multiple indices ...
    Looking at the image in Post #1, tblNotaryIndex should have a foreign key from tblNotaries, a 1-to-many relationship, not a 1-to-1 relationship.



    Quote Originally Posted by doobybug View Post
    ... and 1 notary can have multiple cases
    I think there should be a junction table between tblCaseInfo and tblNotaries..... If I understand correctly.....

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Problem with Relationship Referential Integrity
    By Radtastic10 in forum Access
    Replies: 3
    Last Post: 03-22-2016, 11:03 AM
  2. Referential integrity
    By Lou_Reed in forum Access
    Replies: 14
    Last Post: 12-17-2015, 03:08 PM
  3. Referential Integrity
    By YunqHero in forum Forms
    Replies: 4
    Last Post: 12-17-2012, 05:05 PM
  4. Referential Integrity
    By Desstro in forum Database Design
    Replies: 4
    Last Post: 06-13-2010, 01:29 PM
  5. Referential Integrity
    By jbarrum in forum Access
    Replies: 5
    Last Post: 01-14-2010, 09:04 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums