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.
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
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.
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..