Solved

Forecast Query

Posted on 2004-08-01
4
392 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
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

by:
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

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

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
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…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

895 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

11 Experts available now in Live!

Get 1:1 Help Now