Calculate Sum of a field in a table using access query

Posted on 2009-02-12
Last Modified: 2012-05-06
I would like to add a field to my existing query that would perform a sum calculation off of a field in the table that is shown in the query.  THis field is a cumulative amount that is not in any table.  For example; I know the Machine Num and would like to calculate the cumulated Operating hours.  Table has MachNum and OpHrs.  I am passing the MachNum from another form so I know that coming in.
Question by:avalonwgi
    LVL 26

    Accepted Solution

    something like this may help you...

    SELECT MachineNum, SUM(OpHrs)
    FROM yourTable
    WHERE MachineNum = @MachineNum
    GROUP BY MachineNum
    LVL 75

    Expert Comment

    by:DatabaseMX (Joe Anderson - Access MVP)
    try this:

    SELECT Table1.FIELD1, Sum(Table1.FIELD2) AS OpHrs
    FROM Table1
    WHERE (((Table1.FIELD1)=[Forms].[YourFormName].[txtMachNum]))
    GROUP BY Table1.FIELD1;

    Where FIELD1 is MachNum , FIELD2 = OpHrs

    LVL 75

    Expert Comment

    by:DatabaseMX (Joe Anderson - Access MVP)
    I'm using  txtMachNum as the Name of the control on your form passing the MachNum


    Author Closing Comment

    It looks like it is working.  I still have more to do to fully test but thanks for your help.
    LVL 75

    Expert Comment

    by:DatabaseMX (Joe Anderson - Access MVP)
    How will this work in an Access database:



    Also, the Accepted solution does not take into account

    "I am passing the MachNum from another form so I know that coming in."


    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    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…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

    755 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

    22 Experts available now in Live!

    Get 1:1 Help Now