Aggregate Query - Need Help

[nerd__]

Hi guys,

Newbie here, but hoping to learn lots. I'm currently working on a project where I'm trying to aggregate some data in MS Access and need help with my query. This is my query as it looks now:

PARAMETERS [Forms]![Dialog - Hours by Resource]!cmbProject.[Value] Text ( 255 );
TRANSFORM Sum([ChangePoint Sample Data].Hours) AS SumOfHours
SELECT [ChangePoint Sample Data].Resource, [Resource List].Role, Sum([ChangePoint Sample Data].Hours) AS [Grand Total]
FROM [ChangePoint Sample Data] INNER JOIN [Resource List] ON [ChangePoint Sample Data].[Resource] = [Resource List].[Resource]
WHERE ((([ChangePoint Sample Data].Project)=[Forms]![Dialog - Hours by Resource]![cmbProject].[Value]))
GROUP BY [ChangePoint Sample Data].Resource, [Resource List].Role
ORDER BY [Resource List].Role
PIVOT [ChangePoint Sample Data].Month;

This is the output, the resource column actually has names but I have changed it for privacy reasons:

[Please see 'Hours by Resource Output' in my attached ZIP]

What I would like to have it look like is this:

[Please see 'Desired Output' in my attached ZIP]

How can I achieve my desired output? Either by modifying my query or using another tool in Access.


Any input is appreciated and excited to be part of the community!

[pbaldy]

I think the easiest way to get that look is to base a report on your existing query, and in the Sorting and Grouping area set up Role as a field to be grouped on. If you use the wizard, it will ask about grouping fields.

[nerd__]

Ok, so I was able to get the look but I can't get my summary details (eg. Total NumOfHours for Developers). Any thoughts?

[pbaldy]

You want to select the option to view group header/footers and put a formula like this in a textbox:

=Sum(FieldName)

[nerd__]

pbaldy, thank you! I finally got it to look the way I want
Am I able to mark this thread as solved or do the mods have to do it?

[pbaldy]

Happy to help! You can mark it solved in Thread Tools at the top of the thread.