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!!
jneal0331Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

harfangCommented:
Hello,

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°)
0
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.
0
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

Regards,

Patrick
0
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:

http://www.education.ky.gov/users/jneal/error/1.jpg

0
harfangCommented:
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

or

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.

Cheers!
(°v°)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.