Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2009
    Posts
    8

    Unanswered: Acces 07 - Crosstab Query - Revenue Forecasting - Overflow (Bad design or what???)

    If there's a better way to do this, I'm not "attached" to this design at all so if you know better, please, have at it

    I've created a table with a list of projects that stores a "Beginning Date", "# Weeks" and a "Fee". I would like to generate a report that will list each project and disburse the "Fee" by month, starting from "Beginning Date" through "# Weeks" (whatever month that maybe, but the report should only display up to 9 months out from Date() ). Basically like:

    Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct..... (9 months out)
    Project 1
    Project 2 - [Values filled in based on project, month, & #weeks]
    Project 7
    Project 26
    ...

    Since this is a situation where any month/year in the future could be the beginning month, I've created a query that will determine the "Fee" for "1stMonth", "2ndMonth" "3rdMonth"... for up to 9 months. My query field for each month is:

    Code:
    1stBillMonth: IIf([FeePerMonth]>=([Fee]),([Fee]),[FeePerMonth])
    Code:
    2ndBillMonth: IIf([Fee]-([FeePerMonth])<[FeePerMonth],([Fee]-[1stBillMonth]),[FeePerMonth])
    Code:
    9thBillMonth: IIf([Fee]-([FeePerMonth]*8)<[FeePerMonth],([Fee]-[1stBillMonth]-[2ndBillMonth]-[3rdBillMonth]-[4thBillMonth]-[5thBillMonth]-[6thBillMonth]-[7thBillMonth]-[8thBillMonth]),[FeePerMonth])
    My next step was to create a Crosstab Query that can list the projects (Row Heading) and assign the "1stMonth" "2ndMonth" etc... based on the "Beginning Date" from a list of months filtered to start from today's month. (Column Heading).

    My attempts at opening the query to view the results have led to either "Overflow" or "Query to complex"

    Any help is greatly appreciated!

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    "Sometimes" an overflow error is due to the data such as a date entered incorrectly (ie. 12/08 versus 12/22/08). You may want to check for bad values in the table. Note also that it's very easy to miss a bad date value when you're looking through thousands of records.

    Otherwise try and break things down starting with a basic query with basic formulas and then build up from there adding in more complex formulas. Then you can find out what specific part of the formula is causing it to be too complex (but check your data first - especially date field data.)
    Last edited by pkstormy; 02-04-09 at 21:11.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Jan 2009
    Posts
    8
    My dates appear to match. I take all months of the year and convert them to integers for comparison.

    For example, in order to display all possible values for the month of Jan., I use the code (keep in mind that the Beginning month is actually December and the first month to bill is Jan, thus "12" is first and [Month] is an integer) in a Select Query:

    Code:
    Jan: IIf([Month]=12,[1stBillMonth],IIf([Month]=11,[2ndBillMonth],IIf([Month]=10,[3rdBillMonth],IIf([Month]=9,[4thBillMonth],IIf([Month]=8,[5thBillMonth],IIf([Month]=7,[6thBillMonth],IIf([Month]=6,[7thBillMonth],IIf([Month]=5,[8thBillMonth],IIf([Month]=4,[9thBillMonth])))))))))
    This code is repeated with the [Month]=# incremented each time, for each month.

    I was looking at the templates that came with Access, would a Pivot Table be a better approach?

  4. #4
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    I've come across this when a record has a null field and access does not know what to do with it. Check your date field for nulls.

Posting Permissions

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