Help with Relationships

[jdwm2310]

Hi,

I really need someone to help with this one. I am trying to link two tables inorder to click on the cascade delete selection. However I am having a tough time doing so.
I have 2 tables, Employee:Tbl and SERP Plan:Tbl
I don't even know how to join them I tried reading the Relationship topic provided by the help button and each time I read it I get more and more confuse. I really need some help. If you need further information please let me know. I would appreciate anything....

 

[tlbroadbent]

The first question to ask is, "What is the relationship between the tables?" Can you identify a common column or columns, such as EmployeeID? One would assume that there is a primary key on the Employee table and that column is a foreign key on the SERP Plan table. Is that correct?

Once you've identified the common column or foreign key relationship, you can open the Relationships screen. You should have a toolbar button that will open it, or it can be opened from the menu. I can't recall if the memnu item is under Tools (as it is in Access 20000) or elsewhere on the Access 97 menu.

Once the relationship screen is open add the two tables to the screen. If the key column name is the same on both tablkes, Access shuold add a line showing the relationship between the tables. If not, click on the primary key column and drag it to the foreign key column in the other table. You can then click on the line between the tables to set the properties of the relationship. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[jdwm2310]

From Employees:tbl the primary key is Social Security Number and I am linking to Employee Data from the SERP Plan:Tbl. (It's a one-to many relationship). The problem is that when I go to click on the Enforce Referential Integrity and Cascade Delete Related Records, a Error box opens up stating that Access can't create this relationship and enforce referential integrity...Any advise will be greatly appreciated.

 

[tlbroadbent]

I assume the foreign key on the SERP Plan table is also SSN. It appears that you have data on the SERP Plan table that violates the rule you (relationship) you are trying to establish. Create a query to find records on the SERP Plan table that don't have a match on the Employee table. Use the Find Unmatched Query Wizard to create the query. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[jdwm2310]

Guess what I got it, thanks for your help..Here is how I did: I created a EmpID in my Employees:tbl that is define as the primary key. I made into an autonumber.
Then in your Serp plan table I added a field called 'EmployeeID', data type = number, and a field named Serp planID, data type = autonumber (primary key).

In the relationships window. I dragged Employee.EmployeeID onto Serp plan.EmployeeID. Check the 'enforce referential integrity' and 'cacade delete'check boxes. I created a one to many relationship between the two tables.

And now it works!!!!!!! Once again thanks for your help ;-)