Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    May 2002
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calculate the sum in a query (2000)

    Hi -I have a query for calculating the cost of materials by multiplying the quantity by the cost per ... Does any one know if it is possibly to calculate the sum of the cost field to get a total cost through a query or is my only option to do it on a form.

    If my only option is to do this sm on a form, how do I do it. Anyone. Please

  2. #2
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Calculate the sum in a query (2000)

    Yes it is possible. Do you want total cost by Material to total cost for all materials?

    For total cost by material, (assuming that the query returns materials and total cost) go to the query design mode, select View, Totals from the main toolbar. In the Group by section of the query, change group by to Sum and re-run your query.

    To get the total cost, have the query return the total cost only and then sum on that field.
    Regards,

    Gary
    (It's been a while!)

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calculate the sum in a query (2000)

    You can use a totals query for this.

    Create a new query based on the table or on an existing selection query containing the relevant fields.
    Make it into a totals query (select View/Totals or click the Totals button on the tool bar)
    Type the following in an empty field - and substitute the correct field names:
    Total: [Quantity]*[Cost per unit]
    The default value of the Total property of this field is Group by. Change that to Sum.

    That's it!

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Calculate the sum in a query (2000)

    The calculation of a sum can be done in several ways. If you want to do it in a query, you look at the query in design mode and choose menu option View/Totals. That lets you choose fields to group by, criteria to be used in a WHERE clause, and a number of different arithmetic expressions, including sum. Another option is to use the domain aggregate function DSum. Check out online help for examples. Finally, you can put a Sum expression on either a form or a report that will total a field in the detail record of the form or report. Controls that do that are typically put in either the header or footer of the form or report. One special situation should also be mentioned - there are situations where a running sum is desired. There have been several posts on the lounge about that kind of thing, so do a Search on that topic. (Be sure to specify "exact phrase" or you will get quite a bit of extraneous hits.) Hope this helps.
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •