Help Please!! Putting together a Challenging Query

[Guest]

I have an update query run every time I close a form to update an inventory
count:

UPDATE ToolData AS T INNER JOIN ToolCribSignOut AS S ON
T.ToolCribDesignation=S.ToolCribDesignation SET T.Inventory =
T.Inventory+S.ReturnQuantity
WHERE S.[TIMESTAMP] = (SELECT MAX([TIMESTAMP]) FROM ToolCribSignOut);

I need to add a query right afterwards that asks:

If T.inventory > T.MinQuantity then calculate the difference between
LeadStart and Now() in days and multiply this result by the result of:
UPDATE ToolData AS T SET T.MinQuantity =
(DSum("SignOutQuantity","ToolCribSignOut","[ToolCribDesignation] = '" &
T.ToolCribDesignation & "' AND [DateModified] >= DateSerial(Year(Date()),
Month(Date()) -3, Day(Date()))")/90)*1.2
and assign a null value back to LeadStart


I would love some help. I think i have most of the code, but just don't know
how to fit it together.
Cheers.

 

[pietlinden]

I have an update query run every time I close a form to update an inventory
count:

InventoryQueryGuy... hm... why are you not using a QUERY and
calculating what's in/out on the fly? When I built an "equipment loan"
database, I stored the issue date and the return date.

So the number on hand of a given type is:

Quantity of Type X - (Number of loans for X where Returned =
False/Null). No weird updating required.

 

[Guest]

Hey! Yeah trust me, I had already designed a database that worked based on a
calculation because I know that i stays accurate, will not currupt and is
often faster to do. It was however, Rejected!, so i had to redesign the whole
thing for the purpose of being able to recall on a PDA total inventories for
another database i have designed on it.