Creating relationships and Forms w/ subforms

  • Thread starter Thread starter hdharrison
  • Start date Start date
H

hdharrison

I am creating a form that has a main form and a subform built-in. My
delimma is that I wanted to have, lets say, departments as the main form and
the positions of employees as the subform. I want to be able to see what
positions are in the department that i choose to see. Like this:

Marketing (main form)

(Subform below)
Name1 Vice President of Marketing
Name2 Assistant Vice Pres.
Name3 Sales Rep
Name4 Sales Rep
..
..
..
Name5 Adminstrative Assistant

where Name? is the actual name of a person.


As for relationships, I am a little lost for this situation. I tried to set
this up so that when I run the Form Wizard, I want to be able to check "Form
with subform(s) option. I have not been able to invoke that wizard dialog
box yet as I am not sure if I set up the relationships correctly.

Any suggestions
 
Assuming you have a Departments Table and an Employee table with a foreign
key in the employee table to the Department ID,
open the main form and click on the properties for the subform, link
child/master fields to DepartmentID. DepartmentID would have to be on both
the form and subform. Now when you look at Marketing, you should see those
employees in the subform with the foreign key to Marketing....

HTH
Damon
 
Ok, i understand all of that but the foreign key. How does one establish
this foreign key? I think that this is my "missing piece of the puzzle" and
once I understand it, that will be most helpful information for future
references.

Dustin
 
Assuming that an Employee belongs to only ONE Department, ForeignKey simply
means a Field in the tblEmployee that store a valid DepartmentID that the
Employee belongs to. We say "Foreign" because the value comes from (is
linked to) Field value in *another* Table (tblDepartment in your case).

The term "ForeignKey" is related more to the meaning / usage of the Field
rather than what you set in Access. Related concepts / Access features are
One-to-Many relationship and Referential Integrity which you can set in
Access. See Access Help on the above terms.
 
-----Original Message-----
Ok, i understand all of that but the foreign key. How does one establish
this foreign key? I think that this is my "missing piece of the puzzle" and
once I understand it, that will be most helpful information for future
references.
Click to expand...

The Department table has a field, say DeptID, that is the
Primary Key (indexed, no duplicates).

The Employee table has a field, say EmployeeID, that is
the Primary Key (indexed, no duplicates). The Employee
table has another field named DeptID that is the foreign
key. Its datatype is the same as the datatype of the
field DeptID in the Department table.

Using the field DeptID as the link there will be a one-to-
many relationship between Department and Employees. Each
department can have many employees. Although I worked in
a company that practiced matrix management, one employee
could belong to more than one department.

Roxie Aho
roxiea at usinternet.com
 
oh, I see now what the "Foreign Key" is. I was working on my dummy database
to try to create it and I was surprised by its ease and I began to
understand it now. Thanks for all of you that helped me.

Dustin
 
Back
Top