# Forecast Query

Currently I am working on a Forecasting Project. The problem is as follows:

I have a table, which has 13 fields/columns (one customer name field and 12 every month's field. All together there are 13 fields / columns in that table). I forecast each month's order from each customer for 12 months based on the following formula: Average order of last three months*12/52*4.

If some month (for example towards end of March) customer calls and tells me that in the next two months (example: April and May) the order size would be larger or smaller than that what I have predicted I would go to a form called Order Adjustment Form and update order size for those two months. Now after I have updated, my forecast for the rest of the eleven months (until March in this case because I am using a rolling calendar) is suppose to be changed based on the formula described above.  I have not found a way to run query that will update the values from June until March and not effect April and May values.

I have written some update queries but not sure how to apply those. I would really appreciate if someone could help me out.

###### Who is Participating?

Commented:
probably not what you want BUT:-

as described you lose any entry 13 months old

have you thought of a structure along the lines of
CustomerID
Date ( formatted yyyy-mm)
Order
Forecast ( yes/no ) meaning yes = your forecast
no = an update +/-

i think this structure would easily allow you to extract the relevent records to 1 query and a crosstab query would allow you to display them as you do at prsent

please feel free to tell me to go away
0

Commented:
Okay,

By your formula, if March, April and May orders were \$2000 each, June would be \$1846

You update April and May to 2500 and 2200 respectively and you want to update June-
March, not April and May.

So, your update query would be something like this.

Update tblForecast

SET Jun=((Mar + Apr + May)/3) * 12 / 52 * 4,
Jul = ((Apr + May + Jun)/3) * 12 / 52 * 4

and so on.

If you wanted this in VBA, you could save a LOT of typing and code this to be automatically updated based on whatever month you just updated.  If you always know you're going to update two months at a time, you can code it to skip the last two months.

Your easiest way would be to build this query in VBA and execute the docmd.runSQL
If you need help with that code, let me know.

0

Author Commented:
Hello davidW,
I am thinking to do it in your way but problem is I have got (another column, which I forgot to mention about...sorry). This column is product type column. And I have 6 product types for each customer. So if I arrange my table this way I will have almost 12000 rows of data. Your suggestion seems very feasible but can you suggest me with what can I do with this extra column called Product Type.  Thank you so much for your suggestion.
0

Commented:
just thinking out aloud

1 table for products ( ProductID, Product ) with 6 entries

add a field 'ProductID' to main table

'now the tricky part

subform1 shows main table linked to main form via
CustomerID and is based on  a query where 'Forecast' = true and 'ProductID' = 1

subform2 shows main table linked to main form via
CustomerID and is based on  a query where 'Forecast' = true and 'ProductID' = 2

etc.

when you are entering alterations you switch to a set of queries where 'Forecast' = false ( me![subformname].form.recordsource = "queryname'

i am still assuming that if you forecast 500 units and the customer phones in 650 you can enter +150

if you want to enter 650 what follows gets a bit tricky but no doubt possible

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.