relationship building on inventory not working

[ryan]

Hello,

I'm new to the forum, and to access 2007 as well. Ill try to throw as much
info
as to what I'm trying to do as well as what my problem is. Bare with me on the
spelling and punctuation errors, the message is being sent through phone.

A little background information: I'm a civil service firefighter for the air
force and my additional duty in the station is one of the most important. I'm
the owner of the SCBA Program. I'm responsible for all the life safety
equipment you see firefighter wear, 97 face masks, over 60 air packs and 100
breathing air bottles. Together the equipment surpasses the 50k$ range.

What I'm trying to do, at the moment, is build a relationship between my
inventory list of 97 face masks to my personnel roster of 56 people and
inventory movement. I've got my personnel table complete with the fire
department employees, each with a unique ID. I've got my inventory table
created with 97 masks each with a unique ID and serial numbers with sizes
listed. I also have an inventory movement table created, to track what masks
were issued and to who on what date.

I've managed to build a relation from the personnel table to the inventory
movement table from the personnel unique ID to the issued to area of the
movement table which uses the same ID as the personnel. My problem is building
a relationship from the inventory to the inventory movement because it violates
referential integrety.

Here's my setup in text version, if it may help.:

Personnel Table-
ID-Primary Key (Unique ID identifying each employee)
Company (Fire Dept. Name)
Last Name (Last name of employee)
First Name (First name of employee)
E-mail (E-mail address of employee)
Rank (Employees rank)
Shift (The shift that the employee works on)

Inventory Movement-
Serial Number-Primary Key (The serial number of the face mask)
Item (General description of the item moved)
Size (Size of the face mask)
Date Issued (Date of Issue)
Movement Type (Was the item issued or returned)

Inventory-
ID-Primary Key (Unique ID for each face mask)
Serial Number (Serial Number for each face mask)
Mask Number (tracking number for each face mask)
Size (Size of face mask)
Out-of-service (Yes or No selection for mask being out of service)

....confusing maybe. A lot of information too. Any suggestion or help on
trying to tie this together is greatly appreciated.