New to access said:
Does anyone know how I would go about writing a query that would allow me
add the values of different fields in a table together. For example:
Field one = 3
field two= 10
field three= 22
field four = 75
Fields 1+2+3+4= ?
I am sure it is simple to write but for some reason I am drawing a blank
and
I am very new to MS Access. Thank you so much.
If you're adding separate fields from the same record, they probably should
each be in a separate
record. Let's say the fields are scores on a test. Right now you have
this:
Student Table:
StudentID
StudentName
Score1
Score2
Score3
Score4
Score5
and to get the total of scores you use
[Score1]+[Score2]+[Score3]+[Score4]+[Score5]
What if you want to create more tests? You'd have to change the structure of
the table each time
you added a test. Suppose a student missed two of the tests, you'd still
have to store 5 scores
wasting space. Here's a different approach: Create TWO tables, Student
Table and Scores Table
Student Table:
---------------------------
StudentID <- Make this a Primary Key
StudentName
Scores Table:
---------------------------
StudentID <- Make this a non-unique index
Score
Now create a one-to-many relationship on StudentID from Student Table to
Scores Table.
Each student can have as many scores as necessary with no table changes. To
get the sum of scores
create a query with the two tables and the relationship drawn between the
index fields in each table.
change the query type to Summary (the Sigma icon on the toolbar). Drag all
fields from the Student
Table to the grid and drag all fields EXCEPT StudentID from the Scores Table
to the grid. Now
in the Scores column, change group By to Sum. You're all done.
Tom Lake.
