Unable to do addition of fields in Microsoft access 2010

Question

hi,

i am learning how to use ms access . i have the office 2010 version of ms access . i am unable to do addition of fields in  Expression builder (query design)

it does not add the values but puts them side by side  . the picture below explain my problem

Answer 1

What are the data types of the fields involved?

Answer 2

I suspect that the columns (fields) are all of text data type, rather than a number data type.  Consequently the values, as string expressions, are being concatenated.

However, I'm concerned that the structure of the underlying table might be incorrect.  Each column appears to represent a subject of study or similar.  If so data is being 'encoded as column headings'.  A fundamental principle of the database relational model is the Information Principle (Codd's Rule #1). This requires that all data be stored as values at column positions in rows in tables, and in no other way.

If these are students' scores in subjects for instance, what you have is a many-to-many relationship type between students and subjects.  A many-to-many relationship type is modelled by resolving the relationship type into two or more one-to-many relationship types by means of an additional table.  So you'd have, in broad outline, tables along the following lines:

Students

....StudentID  (PK)

....FirstName

....LastName

....etc

Subjects

....SubjectID  (PK)

....Subject

And to model the relationship type between them:

StudentSubjects

....StudentID  (FK)

....SubjectID  (FK)

....Score

Score is an attribute of the relationship type.

To sum a student's scores for all subjects taken would now be a case of summation over each subset of rows, rather than addition over a set of columns, which is how a relational database is designed to work.

Answer 3

First the question of datatype seems to be the issue here. In your screen shot, the scores are shown left justified which indicates they are text, not numbers. And that's why adding them doesn't work.

As Ken has said, your database is not designed properly. Relational database tables should be tall and thin not wide. 

So Ken's StudentSubjects table would be a proper design. You can then total the grades for each student with a group By query:

SELECT StudentID, Sum(Score) As Grade

FROM StudentScores

Group BY StudentID;

Would give you a total score for each student.