Results 1 to 2 of 2

Thread: Forecast

  1. #1
    Join Date
    Nov 2002
    Posts
    2

    Unanswered: Forecast

    I am writing a forecast to from three different tables. I need to show the starting month plus 11 months out, beginning in which ever month is current, i.e.

    OCT NOV DEC JAN FEB ETC

    When I Run the Query by YearMonth what I get is

    200210 200211 200212 200310 200301 200202 ETC

    Any Fixes.

    Number 2: I need to show a Forecast for Each table for records that do not exist, i.e.,

    CustomerA OCT NOV DEC JAN FEB MAR
    BOOKINGS $10000
    FORECAST $15000 $25000

    Any idea as to trick Access into showing records for customers that do not exist. FC must be customer (among other thing) specific.

    Thanks, C-

  2. #2
    Join Date
    Nov 2002
    Posts
    13

    Re: Forecast

    1- If I understand you correctly, you want one column in the query for each of the 11 months including this one? Do a calculated field for each column using Now() to obtain the current date, the DateAdd function to increase the month and Format to display it with just the first 3 letters of the month. So for the next month, do Format(DateAdd("m",1,Now()),"mmm") and two months ahead would be Format(DateAdd("m",2,Now()),"mmm")

    2- I don't know if I fully understand the query you're working with, but it sounds like another calculated field would do work. An if statement that basically says if the field is null then display this string else display the value of the field- Iif(Isnull([customer].[id]),"Fake Customer", [customer].[name])
    Hope this helps!


    Originally posted by selfed
    I am writing a forecast to from three different tables. I need to show the starting month plus 11 months out, beginning in which ever month is current, i.e.

    OCT NOV DEC JAN FEB ETC

    When I Run the Query by YearMonth what I get is

    200210 200211 200212 200310 200301 200202 ETC

    Any Fixes.

    Number 2: I need to show a Forecast for Each table for records that do not exist, i.e.,

    CustomerA OCT NOV DEC JAN FEB MAR
    BOOKINGS $10000
    FORECAST $15000 $25000

    Any idea as to trick Access into showing records for customers that do not exist. FC must be customer (among other thing) specific.

    Thanks, C-

Posting Permissions

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