Need to use Query to forecast numbers

[kholmsley]

I have a query that is pulling data from two tables to give me my items by weeks of the year. For each week I have a projected usage by item. My query takes this date and puts in the quantity ordered instead of the projected usage when appropriate but I need it to take one more step.

I need my query to remove my projected usage quantity if I have had a subsequent order. So if in week 1 I had no orders, the query is putting in a projected usage #. This is good. But if I get an order in week 2, I need the # in week 1 changed to 0.

I haven’t been able to figure out what the iif statement should be to make this happen. It needs to be able to look back through the weeks and set the projected usage # to 0 for all weeks behind where an order is placed.

Item Projected Usage # Qty Ordered Week
1 10 10 1


2 10 10 1
1 10 20 2
2 10 15 2

In the above example, there are no orders for week one, so my query is putting the projected usage as the Qty Ordered. In week 2 there are orders, so now I need the Qty Ordered in week 1 to become 0 because they were just place holders until an actual order was placed.

Can you help?

[June7]

Run an UPDATE sql action.

Try (be sure to test with copy of table):

UPDATE tablename SET Projected=0 WHERE Week< DMax("Week","tablename","Item=" & [Item])

Advise not to use spaces or special characters/punctuation (underscore is exception) in names.

[kholmsley]

Thank you. I will try this on Monday and let you know the outcome.

[kholmsley]

Hi,
I'm not super experienced with sql so I'm having trouble making this work. Can you be more specific maybe? Or can I send you something more specific?
Thanks!
Kindra

[kholmsley]

Ok, so I got this to run, but it puts all the "projected" numbers to 0 except for week 9. So I guess it's seeing week 9 as the highest number. It needs to put all the projected to 0 for all weeks prior to the usage <> projected. How do I do this? It needs to be a query that can update this table as orders are processed throughout the year. Not a one time update.

[June7]

Original post says you want to set the QtyOrdered field (why did I pick on Projected?) - then try:

UPDATE tablename SET QuantityOrdered=0 WHERE Week< DMax("Week","tablename","Item=" & [Item])

If it is the Projected field you really want to zero if there are multiple weeks:

UPDATE tablename SET Projected=0 WHERE DCount("Week","tablename","Item=" & [Item]) > 1

[kholmsley]

Hi,
I've attached an example of one item in my query. The red cells are ones that should be zeroed out by the query because there are actual orders in future weeks. The yellow cells are actual orders, and the white cells are fine as they are until an order is placed.

The problem with the update query that you sent me is that it updates all the cells, and not just the ones where a future order has been placed. The "projectedusage" qty is simply a place holder until an order is placed. It allows us to calculate approximately how long our inventory will last and helps us gage when an stocking order needs to be placed.

I hope this helps you help me! I soooo appreciate any help!!

Thanks a million!!
Kindra

[June7]

That does complicate. If you actually save the ProjectedUsage value into ActualUsage field as a 'placeholder', how should we know that was not the actual order amount? There is no reason to have a value in Usage until there is an Actual Order placed. Then there would be no need for these UPDATE actions. In fact, I am not even sure these data should be in the same table.