Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sales Forecasting (2002 - XP)

    Hello Hans, Charlotte, Wendell, and all. I'm back, first the history. I had almost 7 years worth of sales figures in Excel spreadsheets that I imported into Access Tables. There is one table for each month of each of the 7 years. Each table contains the same fields.

    I am trying to build a sales forecaster. Something that will show me what I might expect my sales to be based on last years sales for the same day of week. So why seven years of numbers, you might ask? 1. Just so all the info is there if I ever want it and 2. So that I can eventually build something from the averages of the day of week sales.

    My current question: What would be the logical next step in creating this forecaster? How would I create a query that would essentially ask, "What were the sales one year ago on Thursday the fourth (assuming that this year Thurs. is on the fifth)?"

    Thanks,

    Bret

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sales Forecasting (2002 - XP)

    You're skating around a design that is generally used in Data Warehousing and is somewhat different from regular databases. However, a table for each month is a horrible design. To get at the data most efficiently, all of it would be in a single table, with the date associated with each sales figures record as part of the record. Seven years worth of months requires a ridiculous union query otherwise, and it serves no purpose I can think of in this kind of database (or any other for that matter). Data warehouses generally use a somewhat denormalized design for their tables but it is common to ask questions like "show me all the data for this condition for a year ago", for instance. It is done by having a special table that holds all the dates covered with fields that contain the date itself as a date, plus the month, the day, and the year as integers, the number of the month in each year and overall, the number of the year from the start of the table, the number of the quarter in the year and overall, etc. The you use the date value to link to date fields in the sales records table to the date field in the dates table. fter that, you can filter the recordset based on the dates table and automatically get the appropriate records from the other table.

    If that's what you want to do, post back and we can help, but I urge you to combine those pesky tables into one. <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sales Forecasting (2002 - XP)

    Okay, I'll combine them. And once I am bald <img src=/S/clown.gif border=0 alt=clown width=15 height=15> I'll check back with you.

    Thanks Charlotte.

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Sales Forecasting (2002 - XP)

    I agree with Charlotte, in that the UNION query would be horrific.

    A problem with UNION queries is that you only have a maximum of 49 UNIONs in Access97, what these maximums are in A2002 I don't know.

  5. #5
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sales Forecasting (2002 - XP)

    Okay, I'm Bald. I have combined all of 2002 into one table with the following fields, ID, Temp, Soft Serve, Food, Drinks, Cakes, Coupons, Customers, Date (long format). I will work on the rest of the tables over time to get them combined into one year.

    I don't know if it really matters where I start so if it doesn't I would like to have a query that would convert 2002 dates into 2003 so that I know what my sales should be. As the example I gave before I would like to be able to have a form that says, June 3, 2002 was the first Wednesday in June therefore, June 4, 2003 should have sales of X (X=June 3rd 2002 sales).

    Thanks for the helps folks.

    Bret

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sales Forecasting (2002 - XP)

    You don't do it that way, and as Pat says, don't use "Date" as a field name. You don't change any of the old data, you use that to project what the target period should be. Don't start designing forms in detail until you can query out the basic data you need from your tables. The basic query you want is one that carries out the request "show me the sales for June of last year" (or last month, or the past 5 Junes, etc). That means you need to query the table to give you a total for the month of June for 2002. Then you can use that information to display that value on a form for 2003. Actually, you would probably want to also project a growth factor to estimate current year sales anyhow. But to start with you would use something roughly like

    "SELECT Year([SalesDate]) As SalesYear, Month([SalesDate]) As SalesMonth, SUM([Soft Serve] + [Food] + [Drinks] + [Cakes]) As SalesAmt FROM Sales GROUP BY Year([SalesDate]), Month([SalesDate]) HAVING Year([SalesDate]) = Year(Date()) -1 AND Month([SalesDate]) = 6;"

    Once you can query the value you want out, then you can move on to getting fancy.
    Charlotte

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Sales Forecasting (2002 - XP)

    I would have thought you would need a WHERE clause that tests for Year-1.
    eg. If the date selected was selDate and tblDate is the data field in this table (don't call the date field in the table Date, it's an access reserved word) try the following as the criteria:
    tblDate = DateSerial(Year(selDate)-1, Month(selDate), Day(selDate))

    Or am I missing something?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •