Calculating unrelated tables

[reverentcreature]

Hi all I have the following tables in my database where I am experiencing problems figuring out what I need to do

TBLJOBPARTS
JobPartJobID
JobPartAction
JobPartPartID (foreign)
JobPartQuantity

TBLPARTS
PartID (Primary)
PartMake
PartModel

TBLSTOCK
StockPartID (foreign)
StockEngineerID
StockQuantity

When a user adds the parts used/fixed/serviced in the subform (a query on tbljobparts) he/she pulls in a part from a parts table. I need the JobPartQuantity when entered deduct somehow from the StockQuantity.

My problem is there is no direct relationship between TBLSTOCK and TBLJOBPARTS. They are only related via TBLPARTS. Also, for the stock to be deducted from the correct van(engineer), any query would need to filter for both PARTID and ENGINEERID.

Any help would be much appreciated.

 

[jdraw]

You have described something you have created. We have no idea what that is. It would help everyone if you would describe your business need/problem/opportunity in plain English so we can all understand your situation.
We only know what you tell us and you have not told us WHAT you are trying to do.

Better to remove the jargon/terminology until we understand the basic issue,

 

[reverentcreature]

Hi, sorry I was trying to keep my question short and to the point. The database is for a small security firm. The parts table stores the cameras, door entry system, etc and the stock table will hold the quantity of these parts for each engineer who has their own van.

When a job is completed, the Job Parts table is filled out by bringing in the parts that have been fitted or repaired. If parts have been fitted I need the amount in JobPartQuantity to be taken off of StockQuantity. But these fields are not directly related. I hope that makes sense?