Software

Ask something
Discussion Locked

MS ACCESS - ADDING Values In TABLE

0 helpful
13 replies

by jkeav634

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
Access 2000. I have created a table that lists employee names and scores that they have received on 5 tests. Each row contains an employee name, and each of the five colums contains a test score(numeric). I would like to have the fifth row calculatethe total of the five test scores(add all scores together). I tried doing this with a querey and had problems. Any help is appreciated..

13 replies

by jkeav634

I have tried adding the crosstab querey using the wizard and have had no luck. I get the employees name in there, but the wizard only allows me to add one column for the test score (i need five).... Any help is appreciated

by sgt_shultz

Make sure the test score fields in your table are set to 'number' (not text).
then create a plain old query with the name, the 5 test scores and then put this formula in another query column:
TotalScore: ([Table1]![test1]+[Table1]![test2]+[Table1]![test3])
where Table1 is your table of names and scores and test1 test2 etc are the columns with the test scores. run the query and you'll see a column named TotalScore with the added-up scores...hope this helps...

by jkeav634

Thanks, for getting back to me. I have tried what you have suggested and the total score field is showing "0" even though the correct table and field have been entered into the formula " Expr1: "TotalScore"=([Table1]![response1]+[Table1]![response2]+[Table1]![response3]) ". Any ideas on why this would not work?? Thanks

by kees.valkenswaard

If you want to use the method of answer 2 there should be a : [colon] and not and = [equal sign].
You will have to retype the formula or remove the expr1 and the double quotes.
In addition do not use a plain query [after testing] but an update query.
Good luck

by jboardman

I assume you're using a form to enter the data? If so, use an unbound field to add the other fields together and write the answer to the table. If you're using the table directly, then you'll have to run a query to get the totals. The calculation you wrote in your comments is fine for either one, except you MUST put a colon (:) instead of an equals (=) to get it to work. Also, you don't have to put the table title in if each field has a distinct name from any other field available in the query (ie no other joined table duplicates the field names).

by richtee

You are getting close. Based on Answers 2,3,and 4.
Either use Expr1: OR Total Score:

Do not use both.

Also, if the query is based on one table, you might also find that you do not need to have the table designated in the formula you put in the query field. You could use the following:

Total Score: [Score1]+[Score2]+[Score3]+[Score4]+[Score4]

Good Luck.

This discussion is closed to new replies.