Relationships in Access

[Guest]

Im attempting to create a relationship between employee's and assets in their
offices. When I create a relationship btwn my Assets and Employees tables Im
not sure which fields Im supposed to connect. I need to "Enforce Referential
Integrity," "Cascade Update Related Fields," and "Cascade Delete Related
Records." I relationed Employee IDs and Asset IDs with a One-To-One
relationship. When I go into the form where I want to input/delete assets
from employees, it doesnt seem to like the relationship. When I assign an
asset to an employee on the subform window, it does fine. But once I add
another asset to the same employee, Access adds it to the NEXT employee.
Please explain this to me.

 

[Wayne Morgan]

You will need a "foreign key" in one of the tables. This would be the value
of the ID field from the other table. You would link the tables on these
field.

Example:
A one-to-many relationship (each employee has multiple assets):

You would add a field to the Assets table for the EmployeeID that uses or
has that asset. You will need to create a record in the Assets table for
each asset that each employee has.

A many-to-many relationship (each employee has multiple assets and each
asset may be assigned to multiple employees):

This would come into play if you had an asset such as a desk and you didn't
track each desk separately by a serial number. Each employee probably has a
desk. So you would have multiple desks assigned to multiple employees. To
set this up, you need a third table known as a linking table. The linking
table will need two fields in it, the EmployeeID and the AssetID. Each of
the other two tables would be linked to this third table on their respective
ID fields. You would set both of these fields as the Primary Key for the
linking table. If needed, you could add other fields to the linking table,
such as Quantity, to indicate if an employee had more than one of a
particular asset.

 

[Guest]

Each employee can control more than one asset. You need a one-to-many
relationship between employee and assets.

Make the Employee ID a key field.

 

[Guest]

Im not quite sure I understand what a foreign key is. The fields I have in
the Employees table are "ID(which is autonumbered, and Prime Key)," "Employee
Number," "Last Name," "First Name," "Room No," "Email Address," "Extension,"
"Home Number," "Work Number," and "Extension."

The fields I have in the Assets table are "ID(which is autonumbered, and
Prime Key)," "Asset Category," "Status ID," "Make," "Model," "Tag No," and
"Description."

The Assets table is completely emtpy, because I havent assigned anything to
any employee. The Employees table has 75 entries and is completely filled for
the most part. Which fields do I link in relationships, so that when I add an
asset to an employee on the Form it will let me add personal items multiple
times to a single employee?

 

[Wayne Morgan]

A Foreign Key field is a field that has the primary key from another table.
In this case, to set up a one-to-many relationship you need to add a field,
perhaps called EmployeeID, to the Assets table. You would then link the two
tables on the Employees.ID and Assets.EmployeeID fields.

To set up a many-to-many relationship, you would create a third table,
perhaps called EmployeeAssetLink, and it would need at least two fields,
EmployeeID and AssetID. Set both of these to be the primary key of this
table. You would then link the following fields, Employees.ID to
EmployeeAssetLink.EmployeeID and Assets.ID to EmployeeAssetLink.AssetID.

 

[Guest]

THat helped about greatly, thank you. You saved me a lot of time.