• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 496
  • Last Modified:

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

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!!
  • 2
  • 2
1 Solution

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!
jneal0331Author Commented:
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.
Patrick MatthewsCommented:
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


jneal0331Author Commented:
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:


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

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now