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

    Question Unanswered: Cross-tab query ambition

    I'm not sure if this is possible or if I can explain it clearly.

    Basically, I have a db of Projects, each of which has a start and end date.
    Each project is classified in one or more sectors.
    Each project has one or more financial contributions, each or which can be either of type 1 or 2.

    What I am aiming for is a bar graph which shows has sectors on the vertical axis, and the year of the start date on the horizatonal axis, with the contributions as the values. Ideally I would like just one bar per sector per year representing the total of type 1 and type 2 contributions, but also to have each bar divided into type 1 and type 2.

    Is this possible, or am I going to have to be content with separate graph for type 1, type 2, and the total contributions?

    Any insight would be greatly appreciated. If you need further explanation, please just let me know.

    Thanks,
    Justin

  2. #2
    Join Date
    Jun 2004
    Posts
    96
    this is possible, but have you tried?
    try it and if you have problems, post it, I'm many in here will try to help..

    Cyherus

  3. #3
    Join Date
    Feb 2004
    Location
    Lao PDR
    Posts
    15

    Question

    I've tried to do it with a cross-tab query, but maybe this isn't the right approach.

    TRANSFORM Sum(ProjectContributions_ProjectDividedSums.Sector TotalShare) AS Total
    SELECT ProjectSectors.SectorID, Sum(ProjectContributions_ProjectDividedSums.Sector GrantShare) AS Type1, Sum(ProjectContributions_ProjectDividedSums.Sector LoanShare) AS Type2
    FROM (ProjectSectors LEFT JOIN ProjectContributions_ProjectDividedSums ON ProjectSectors.ProjectID = ProjectContributions_ProjectDividedSums.ProjectID) LEFT JOIN Projects ON ProjectContributions_ProjectDividedSums.ProjectID = Projects.ProjectID
    GROUP BY ProjectSectors.SectorID
    PIVOT Year([Projects]![StartDate]);

    I can only seem to divide one amount by it's year, whereas I want two separte amounts (types 1 & 2) for each year. I can only put the type 1 and 2 amounts as row headings, rather than as values.

    Any suggestions?

    Thanks,
    Justin

  4. #4
    Join Date
    Jun 2004
    Posts
    96
    which version are you using??

  5. #5
    Join Date
    Feb 2004
    Location
    Lao PDR
    Posts
    15
    Access 2000

  6. #6
    Join Date
    Jun 2004
    Posts
    96
    crosstab in access 2K is somehow quite limited, as it allows only 1 calculated field only
    but in xp, they provide pivot table/chart which gives you more options to play with such pivot/crosstab problems..

    Cyherus

Posting Permissions

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