Access Query to produce grouped listing with totals

Posted on 2012-08-15
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
    LVL 12

    Accepted Solution

    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

    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

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    IF SQL Statement Access SQL 6 38
    Oracle Pivot 2 20
    Calulated Field in MSAccess 11 17
    query question 4 20
    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    734 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

    Need Help in Real-Time?

    Connect with top rated Experts

    26 Experts available now in Live!

    Get 1:1 Help Now