?
Solved

How do you assign forign keys in Access 2007

Posted on 2009-05-11
2
Medium Priority
?
190 Views
Last Modified: 2012-05-06
having trouble assigning forign keys
0
Comment
Question by:1030071002
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 12

Accepted Solution

by:
geowrian earned 75 total points
ID: 24361066
In a nutshell, you have a primary key from table x and the foreign key in table y. Make sure the types are compatible (i.e. an autonumber in table x with a long integer in table y). That's basically it. Depending on the indexing options you select on the foreign key, the type of relationship will change (i.e. indexing: yes with duplicates allowed will create a many-to-one relationship).

To enforce referential integrity (as is often what people are looking for with access databases from my experience):
1) Right-click off a table and choose "Relationships".
2) Add tables x and y to the view, if they are not already there.
3) Drag the primary key in table x to the foreign key in table y
4) Check enforce referential integrity and the cascading options you need.
0
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 75 total points
ID: 24363591
Access can handle most of the heavy lifting, if you setup your tables as geowrian describes. For example, most of the time in Access a foreign key relationship is handled through either (a) a combo or listbox or (b) a subform. If you use a combo/listbox, then you simply set the ControlSource of that control to the field in your table that would house the foreign key value, and you'd set the RowSource of the control to show the records from the foreign table.

If you use a Subform, you'd set the Master/Child links to reflect the relationship between those two tables, with your Master being the "one" side of the 1-to-Many relationship, and the Child (i.e. subform) being the "Many" side of that relationship.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question