Solved

# Forecast Query

Posted on 2004-08-01
396 Views
Last Modified: 2008-02-26
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.

0
Question by:romaniup
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
• 2
4 Comments

LVL 1

Expert Comment

ID: 11690683
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

LVL 4

Accepted Solution

davidW earned 500 total points
ID: 11690814
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

Author Comment

ID: 11693787
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

LVL 4

Expert Comment

ID: 11699525
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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
###### Suggested Courses
Course of the Month9 days, 8 hours left to enroll

#### 740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.