jneal0331
asked on
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!!
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!!
ASKER
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.
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.
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
Regards,
Patrick
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
Regards,
Patrick
ASKER
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:
http://www.education.ky.gov/users/jneal/error/1.jpg
http://www.education.ky.gov/users/jneal/error/1.jpg
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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!
(°v°)