We help IT Professionals succeed at work.

Access Query to produce grouped listing with totals

In Access, I have a table Called Data1 with around 120,000 records and eight columns as per the attached. I’m looking to produce a query that will Group the data by:
Company – Company appears grouped
Category - Subgrouped with Category
Shift – Subgrouped by Shift.
HR_Extras and HR_Totals should be totaled as per the below.
Structure should appear along the lines of
Have attached an access 2007 DB with the data as listed included and a word file displaying the requested format of the report.
Description.docx
Data1.accdb
Comment
Watch Question

CERTIFIED EXPERT
Commented:
As far as I know, the only way to produce a report organized the way you have it is to use a pivot table, either as a pivot on data in Excel, or as a pivot view on a query in Access. See attached for your database with a pivot view query implemented. It's not identical to your report only because by default the categories are sorted alphabetically. You could sort reverse alphabetically, but if you want a custom order, like you have in your report, you'd have to add a separate column in your data or a separate table to hold the sort order for your categories.
Data1-GroupedListing.accdb
CERTIFIED EXPERT

Commented:
Thank you for accepting my solution. I noticed you rated my answer low quality though. What other information could I have provided to make my answer better?

Explore More ContentExplore courses, solutions, and other research materials related to this topic.