Results 1 to 4 of 4
  1. #1
    pjemus is offline Novice
    Windows 10 Office 365
    Join Date
    Apr 2022
    Posts
    2

    How do I output the summed values of a field grouped by ID?

    I am trying to build a database for tracking progress over time.



    I have a Subjects table that has a unique ID for each subject I am trying to track.

    In this table I record the DateStart for the subject, the Deadline for the subject and the UnitsTotal required to complete the subject.

    I have arbitrary units of progress that I want to enter each day as UnitsProgress in a Reports table.

    I want to make a field in a query that calculates the PercentageProgress based on the summation of the daily UnitsProgress values entered into the Reports table for each SubjectID compared to the UnitsTotal value stored in the Subjects table.

    I have tried changing 'group by' to sum UnitsProgress, but it seems to be summating without regard for the SubjectID.

    The output I want:
    SubName UnitsProgressSum UnitsTotal PercentProgress
    SubjectX 70 100 70%
    SubjectY 50 100 50%

    What I am getting:
    SubName UnitsProgressSum UnitsTotal PercentProgress
    SubjectX 120 100 120%
    SubjectY 120 100 120%

    thanks

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    So group on subject?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Might be easier to build report and use Sorting & Grouping feature with aggregate calcs.

    Provide sample raw data and SQL of attempted query.

    If you want to provide db for analysis, follow instructions at bottom of my post.
    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.

  4. #4
    pjemus is offline Novice
    Windows 10 Office 365
    Join Date
    Apr 2022
    Posts
    2
    I decided to code it myself in the SQL view and it made more sense to me. I still don't really know how I did it, but it works fine now.

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

Similar Threads

  1. Replies: 9
    Last Post: 02-05-2018, 09:45 AM
  2. Replies: 1
    Last Post: 10-16-2016, 12:54 PM
  3. Replies: 3
    Last Post: 04-07-2015, 10:37 AM
  4. Min/Max of Transposed and Grouped Values
    By Lady_Jane in forum Queries
    Replies: 3
    Last Post: 06-20-2011, 08:55 AM
  5. Int in a summed field
    By tcheck in forum Access
    Replies: 4
    Last Post: 02-19-2011, 04:03 PM

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