Trouble With Tables

JRT2006

Hello everybody,

I'm new to the whole access thing and I've decided to start off using the templates and building from there. I'm in the process of creating an inventory database using the Desktop Inventory template provided by Microsoft. This database will help me inventory all my equipment and parts. I am in charge of an SCBA program for a fire department in the Air Force. Under my wings is over 100k dollars worth of parts and equipment, you can understand why I want to have this database working.

But, to the main question for this post. How can I issue a respirator from a respirator table and allow it to reduce the stock in my inventory table?

I've got a table built for my inventory (parts & equipment). 256 items are listed on this inventory table, each with a part number for ordering and current stock quantity. 3 of my items are respirator facepieces; PN# 10094029 (15 in stock), PN# 10094028 (66 in stock), and PN# 10094030 (15 in stock).
I've also got a Respirator table with 96 respirators (15 small, 15 large, and 66 medium) each with a unique serial number (different from the part number for ordering on the inventory table)

I need to be able to open my respirator table, find medium respirator SN# xxxxx1 and issue it to a fire fighter. Once issued, I need the quantity for PN# 10094028 in my inventory table to drop from 66 to 65. If I issue a small respirator, SN# xxxxx2, from the respirator table to a firefighter, I need the quantity for PN# 10094030 in the inventory table to drop from 15 to 14 and vice versa for returning a respirator, have the quantity increase.

Any ideas on how I can get this working...it is the last thing I need to add onto this template to have it completed (I hope).

If the explanation is terrible, let me know and I will try to draw it out some more

Thanks