Solved

Forecast Query

Posted on 2004-08-01
4
391 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
Comment
Question by:romaniup
  • 2
4 Comments
 
LVL 1

Expert Comment

by:bobsterboy
Comment Utility
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

by:
davidW earned 500 total points
Comment Utility
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

by:romaniup
Comment Utility
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

by:davidW
Comment Utility
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
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 …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

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

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

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now