Hi Everyone,
This is my first post so I apologise that it's a long-winded and probably confusing one...
I really need some help with this as I've hit a brick wall, and feel somewhat out of my depth now. I have been tasked with building an MRP database, based on tables linked to Sage 50c.
I have got all the necessary Sage tables linked in the database, and managed to created queries to get the basic information together, like what we have on order, what is needed by when etc.
My next task, which is what I am struggling with, is this:-
I need it to look at the component order (SOP) due date, and the purchase order (POP) due date, and if the POP due date + 10 days is earlier than the SOP due date, then add the POP Qty on order to the Qty in stock, place that figure in the qty in stock and then remove what it added to the stock from the Qty on order. I have an expression which adds together the two figures if the dates are right...
IIf(([POP_DUE_DATE] +10)<=[Due Date],[QTY_ORDER]+[QTY_IN_STOCK],[QTY_IN_STOCK])
What I don't know how to do is subtract the qty on order figure from the POP_ITEMS table once it has been used, which it needs to do in date order, so the QTY_ORDER is not used twice from the same purchase order.
I tried using an IIF statement in an update query to update the QTY_ORDER value but I get an error message whenever I try to run the update query, "IIf(([POP_DUE_DATE]+10)<=[Due Date],[QTY_ORDER]=0,[QTY_ORDER]) is not a valid name. Make sure it is a valid parameter or alias name, that it does not include invalid characters or punctuation, and that it is not too long."
It needs to be fluid, and calculated from scratch each time the Sage tables are refreshed as we get orders coming in which could have an earlier due date than existing orders, and would use the incoming stock first.
Anything you could suggest to help would be greatly appreciated. I'm sure you'll need further info and clarification which I'm happy to provide. I just need to get a handle on this.
Thanks!
Beckey