How can I subtract numbers from 2 feilds in same table and then add them together for all users?

Posted on 2006-04-22
Last Modified: 2008-02-01
I have a simple access database that is going to track weight loss. I only have two tables. The first table collects beginning data, such as name, starting weight, goal weight. The second table will collect the date, type of activity, and weight for that date. The database is complete, but iI'm having trouble with the calculations. First I want to find the total weight each user wants to lose (BeginWeight - GoalWeight) from the same table. Next, I want to combine this data for all 10 users to get the number of pounds the staff want to lose - so later I can graph the amount of weight lost per week. So, I need

1) how to subtract 2 fields from the same table to get total weight needed to lose
2) How to combine these results

Thanks in advance for any help!!
Question by:jneal0331
    LVL 58

    Expert Comment


    To perform calculations in Access, you need queries. For example, create a query based on your table, select the fields you want to see, and add this new field in a blank column:

        GoalLoss: BeginWeight - GoalWeight

    Simple as that.

    To get the total over you table, change this to a total query (menu "view / totals"), and select Sum as operator for this new field.

    There you go. Try that and tell me how it goes.
    Good luck!

    Author Comment

    I have a query with three fields. The first filed is EmployeeName from table tblEmployee; the second table I
    have field BeginWeight from tblEmployee, and the final coloumn has field WeightGoal from same table. In the
    forth column I didn't put any fields, but in the criteria area I put

    GoalLoss: BeginWeight - GoalWeight

    I get the following message

    You can't set criteria before you add a field or expression to the field row. Either add a field from the field
    list to the column and enter an expression, or delete the criteria.
    LVL 92

    Expert Comment

    by:Patrick Matthews
    Hi jneal0331,

    Don't put that in the Criteria; it goes on the first line.  Criteria are used to selectively filter a result.

    Your final SQL should look like this (switch the the SQL view):

    SELECT EmployeeName, BeginWeight, WeightGoal, [BeginWeight] - [WeightGoal] AS GoalLoss
    FROM tblEmployee



    Author Comment

    ok, Now the second part of the question. While I had this query open (1st part) I renamed it and clicked on "(menu "view / totals"), and select Sum as operator for this new field". I received the following error:

    LVL 58

    Accepted Solution

    As you can see in the message, you specify Sum() twice, using two valid methods. Use only one of them:

    Field:          GoalLoss: [BeginWeight] - [GoalWeight]
    Group By:   Sum


    Field:          GoalLoss: Sum([BeginWeight] - [GoalWeight])
    Group By:   Expression    <---- this says you have a complete expression above

    The QBE grid will normally transform one syntax into the other, depending on what is considered the most "user friendly"...

    Since I'm reading your query, your current setup will not produce a meaningful result. You must remove the fields EmployeeName, BeginWeitght and GoalWeight from the grouping, or there would be nothing to sum -- there still would be one row per employee... If you had a field "Department", you could group by that field to get the sum of excessive pounds for a department. If you have only the single "sum" column, it will compute the sum over your entire table.


    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    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…
    Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
    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.

    732 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

    23 Experts available now in Live!

    Get 1:1 Help Now