Link to home
Start Free TrialLog in
Avatar of romaniup
romaniup

asked on

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.

Avatar of bobsterboy
bobsterboy

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.

ASKER CERTIFIED SOLUTION
Avatar of davidW
davidW

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of romaniup

ASKER

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.
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