If, then, Insert date, else.

[Guest]

Currently I have this query to keep track of inventory. I would like to
create an accurate restock routine however.

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

So...after this portion of the query has run, I would like it to ask one
more question: If T.Inventory is <= T.MinQuantity, then insert the date
into T.LeadStart, else do nothing. However i don't want a value to be
overwritten if there is one already there.

Any help writing this code is welcomed!

 

[Jeff L]

UPDATE ToolData AS T
SET T.LeadStart = Date()
WHERE T.Inventory <= T.MinQuantity
And T.LeadStart Is Null

You may need more criteria to identify which record to update. The way
mine is written, it will update all records meeting the criteria.

Hope that helps!

 

[John Spencer]

PERHAPS something like the following.

UPDATE ToolData AS T INNER JOIN ToolCribSignOut AS S ON
T.ToolCribDesignation=S.ToolCribDesignation
SET T.Inventory = T.Inventory-S.SignOutQuantity
, T.LeadStart = NZ(T.LeadStart,
IIF(T.Inventory-S.SignOutQuantity<=T.Minquantity,Date,T.LeadStart))
WHERE S.[TIMESTAMP] = (SELECT MAX([TIMESTAMP]) FROM ToolCribSignOut);

Although, you might want to break that up into two queries.

As always. back up first.

 

[Guest]

You guys make it look so easy!!
Perhaps you can help me with one more. Its similar, but the reverse:

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);

Now I need to add a bit of a more complicated function to it:

if the T.Inventory is now greater than the T.MinQuantity then determine the
time difference in days between LeadStart and Now. This value needs to
multiply 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; (multiply here by date difference
in days)
.....which i need to run only for the current record. After all this i need
the T.LeadStart to be cleared of any value.
If the inventory is still not larger than MinQuantity then i don't want the
second update portion to run.

I may be in over my head here!

In brief: run Update1, next if Inventory > MinQuantity then take difference
between LeadStart and Now, multiply the result of Update 2 by this value
(that was calculated in days) and clear LeadStart, else only run Update 1

Again, any help or suggestions are welcomed.