Results 1 to 5 of 5
  1. #1
    dbmiller5 is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Jul 2016
    Posts
    10

    Question Simple Database Relationship Questions

    Hi guys,

    Firstly, I am a complete beginner with zero Access experience. I am attempting to create a simple one to many database for RMA's where one customer sends in multiple items. I have the form setup and the tables built but my relationship is not working and I am sure I have done it wrong. When I create a new record using my form and try to save it I am getting this error "The Microsoft Access database engine cannot find a record in the table <name> with key matching field(s) <name>. (Error 3101)"

    This should be very straightforward table design but I am not getting it so I thought I would ask for some help from the experts. Here are some screenshots of my table/relationship structure.

    Customer table (Company)
    Click image for larger version. 

Name:	Company.JPG 
Views:	20 
Size:	33.5 KB 
ID:	25170

    Table to hold RMA's (HH RMA)
    Click image for larger version. 

Name:	RMA.JPG 
Views:	20 
Size:	24.7 KB 
ID:	25171



    Relationship
    Click image for larger version. 

Name:	1.JPG 
Views:	20 
Size:	44.2 KB 
ID:	25172

    Thanks for looking and offering your guidance in this endeavor.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    From the sound of it, I'd guess there isn't a record in company when you try to add a record to the other table. Referential integrity requires the record there first. In other words, if you enter a CustomerID of 2 in the second table, that record must exist in company.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    NTC is offline VIP
    Windows 7 64bit Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    pb has given you the answer; to elaborate - once you made that relationship then you must have the same value on both sides of that line.....

    Microsoft intended the LookUp field type for this. You have your company table. In your HH RMA table the Customer ID field would be LookUp field type and the wizard will prompt you to point to the Company table and that will set up the relationship line. It makes the field a selector field too.

    As it is early in design - I would suggest you normalize your naming convention. It is confusing to have the 'Customer'ID linking to the 'Contact'ID of the 'Company' table - 3 different names for the same entity..... have CompanyID be the key field to the Company table and re-use that name where ever it is the foreign key field in other tables.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    More thoughts on lookup fields:

    http://access.mvps.org/access/lookupfields.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    dbmiller5 is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Jul 2016
    Posts
    10
    Thanks for the help guys, I got it all figured out with your help and it is working correctly now. +1 for each of you!!!

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

Similar Threads

  1. Table / relationship Questions
    By eolson30 in forum Access
    Replies: 29
    Last Post: 08-11-2014, 11:59 AM
  2. Establishing a simple relationship?
    By Harry2 in forum Access
    Replies: 7
    Last Post: 06-16-2014, 02:41 PM
  3. Replies: 21
    Last Post: 04-14-2014, 09:33 AM
  4. Relationship questions
    By ertjeffrey in forum Access
    Replies: 3
    Last Post: 01-24-2011, 04:50 PM
  5. Simple questions
    By canfish in forum Database Design
    Replies: 9
    Last Post: 07-23-2010, 02:57 PM

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