Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Location
    Lao PDR
    Posts
    15

    Unanswered: Cross-Tab query or VB?

    I'm trying to determine if it possible to do something with a cross-tab query, or if i will have to do it in VB.

    I have the following two tables:

    Projects table
    ProjectID
    StartDate
    EndDate

    Contributions table
    ContributionID
    ContributionDate
    ProjectID
    USDAmount

    I have used the following cross-tab query to get the total amount of contributions per project per year:

    TRANSFORM Sum(ProjectContributions.USDAmount) AS SumOfUSDAmount
    SELECT ProjectContributions.ProjectID
    FROM ProjectContributions LEFT JOIN Projects ON ProjectContributions.ProjectID = Projects.ProjectID
    GROUP BY ProjectContributions.ProjectID
    PIVOT Year([ContributionDate]);

    Now what I'd like to do is spread the value of all contributions for a project across the years of the project by

    multiplying the total project value by the percentage of the project's lifespan that falls in a given year.

    It seems to me that it might be possible to write a query to do this, but I can't figure out how to get it to show all

    the years of the project.

    Any suggestions?

    Thanks,
    Justin

  2. #2
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    Get the data showing in the query the way you like before it gets pivoted. If you want to show more than one value, like a total contribution and a count of contributions for each cell, then you may find it easier to program it to store into a table.

Posting Permissions

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