• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4962
  • Last Modified:

MS Access: How to make Parent / Child table relationships

I have been trying to make the following parent child table relationship but can't seem to make it work.

The following table: "tblTools" is the parent, table "tblInspections" is the first child table "tblDimensionVerification" is the second child.

Can someone please help me out with this I keep getting relationship type errors and no index found errors when I try to put foreign key in I don't understand how to make this relationship.


Measuring-Instruments-4-29-11Rev.mdb
0
James Coats
Asked:
James Coats
  • 4
  • 2
1 Solution
 
Rey Obrero (Capricorn1)Commented:
did you create a Primary Key (PK) in tblTools. This PK will be the Foreign Key (FK) in the child tables
0
 
Rey Obrero (Capricorn1)Commented:
in table tblInspections, you don't need the DimensionPK field
in table tblDimensionVerification, you don't need the Tool_Pk field

you create the relation between tblInspections and tblDimensionVerification using the  InspectionPK field

0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
Why all the one-to-one relationships?




See the attached:
 Measuring-Instruments-4-29-11Rev.mdb
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
James CoatsComputer Info. Sys. StudentAuthor Commented:
TheHiTechCoach,

Each tool can have many inspection results in the table tblDimensionVerification and many inspections in table tblInspections so I see your point they should be many to one. I don't see any changes in the file.

Capricorn1,

I made changes to the table relationships is this what you mean?? I have attempted to add a record to table tblDimensionVerification but I get an error stating that I can't put in a new record because I have to have a related tool in table tblTools.

see the file.


Measuring-Instruments-4-29-11Rev.mdb
0
 
Rey Obrero (Capricorn1)Commented:
0
 
Rey Obrero (Capricorn1)Commented:
<I have attempted to add a record to table tblDimensionVerification but I get an error stating that I can't put in a new record because I have to have a related tool in table tblTools.>

this is caused by the relationship that you created with referential integrity.
* you can not create a child record without a parent record.

see Referential Integrity from this link for more info

Defining relationships between tables in a Microsoft Access database
http://support.microsoft.com/?kbid=304466
0
 
James CoatsComputer Info. Sys. StudentAuthor Commented:
Thank you very much!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now