Creating an MRP Database but stuck on updating figures...

[Mrsbex]

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

[ranman256]

date math is done with DateAdd or DateDiff:
DateAdd("d", 10, [POP_DUE_DATE])

(10 days)

[ranman256]

date math is done with DateAdd or DateDiff:
DateAdd("d", 10, [POP_DUE_DATE])<=[Due Date]

(10 days)

[Mrsbex]

Thank you, but that doesn't help me with my problem

[Minty]

I think you would be better to post up sample data.
Starting data and the result you are looking for, include enough examples to cover all possible outcomes.
You can use a couple of spreadsheets for speed.

[Mrsbex]

Thanks @Minty but I wouldn't even know how to start with sample data as it's linked to our Sage accounts. I'm destined to get nowhere with this stupid thing. Time to throw in the towel I think

[Minty]

Well you have some data you are currently working with. We would need a chunk of that , and a manually created version of what you would like out if it.
Strip out the confidential stuff, leave us with enough to work with.

It's only meaningless numbers without the context, so you won't be giving anything away.

[MattBaldry]

I actually use Sage and the tables in a lot of my database work. Send me a message with your details and I will let you know what to do. I have a few queries that already do exactly what you are after.

One thing with the Sage tables, you cannot do much with them direct. I generally import the data into local tables, IE POP_ITEM_LOCAL, and then manipulate the data from there. Sage is not very friendly when trying to manipulate the data direct.

~Matt