Access Query to produce grouped listing with totals

Posted on 2012-08-15
Medium Priority
Last Modified: 2012-08-16
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.
Question by:Alistair_Mair
  • 2
LVL 12

Accepted Solution

telyni19 earned 1000 total points
ID: 38298409
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.
LVL 12

Expert Comment

ID: 38300988
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?

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question