Results 1 to 5 of 5
  1. #1
    baum12 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Apr 2011
    Posts
    10

    Table Subtotals for Groups

    Hi all, I have a project in Excel that I would like to move to access. It tracks money spent each quarter but it has subtotal for each division. Is there a way in Access to have my table look just like it does in Excel with the subtotals after each group? I know how to do the total for the 300 in the example below but not the total under each group - at least not in a table. Thank you for any advice - I appreciate it! Lori


    Group A: 100 100 100 = 300
    Group A: 100 100 100 = 300


    Group A: 200 200 200
    Group B: 100 100 100 = 300
    Group B: 100 100 100 = 300
    Group B: 200 200 200

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Is there a way in Access to have my table look just like it does in Excel with the subtotals after each group?
    First time I've ever seen a request to do that - usually someone sets up Access tables like spreadsheets first, then asks why they can't figure out how to get info in or out! You don't want to do that. Generally, we don't store calculations in records either - you calculate when needed but in forms, queries and reports, and we don't create tables that look like sheets. Relational databases are tall (related data is in rows, not columns) not wide like sheets. You probably should start with understanding database normalization first.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Join Date
    Apr 2017
    Posts
    1,673
    In addition to Micron's post, in Access you have to differ between data storage (tables), data manipulation (forms), and reporting (reports). Each area has his own rules and best practices.

    Tables must be designed in a way what guarantees most speed and flexibility for data manipulation and reporting (for all current and possible future forms/reports). As a rule, users must not have any direct access to tables (the best way to ensure this is to hide all tables from users!);
    Forms are linked to tables, or to queries based on tables, and they are mainly used to insert new data and to update data. This may be done manually, or through VBA code invoked by form or control events. And they also can have controls which call reports;
    Reports are just reports - they read data from tables or queries, and display/print those data in any design your data and your skills are matching with.

    So about your question - you need a report with a couple data groupings. In group headers/footers, you display group name and group total, and in report body you display detail info.

    E.g. you have a table
    tblCosts: CostID, DivisionID, GroupID, Year, Quarter, Cost

    You create a report grouped by Year and Quarter, DivisionID, and GroupID - all costs for every quarter grouped as Quarter total costs, Division total cost in quarter, and Group totalcost in quarter, with individual costs listed for every group. You also can call the same report using filter to limit the output to single year;
    Or you create a report grouped only by DivisionID, and GroupID, and call it using filter condition to limit the output to single quarter - only data for single quarter are displayed;
    etc.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Should spend a solid week studying an introductory tutorial book on Access.
    Forget what you know about building spreadsheets.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Question: why do you want to move your project to Access? What do you want to achieve? Each program has it's own purposes and maybe the things you want to achieve are easier in Excel.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Adding Subtotals to a Table or Query
    By wojosh6 in forum Access
    Replies: 4
    Last Post: 12-10-2015, 07:53 AM
  2. Replies: 1
    Last Post: 08-31-2015, 10:38 PM
  3. Replies: 15
    Last Post: 11-18-2013, 10:49 PM
  4. Hide Subtotals in Pivot Table
    By Victor70 in forum Forms
    Replies: 2
    Last Post: 04-11-2012, 06:10 AM
  5. Replies: 3
    Last Post: 11-03-2010, 09:53 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums