Summing one field value to another

[George]

Good morning,

I have 2 field in a table - CourseMarks and ExamMarks. I am making a query to sum these two values. My code entered through the expression builder is Total: [CourseMarks]+[ExamMarks].

When the query is run there are no results if both fields have a score, but If there is a score in the CourseMarks field and none in the ExamMarks field the CourseMark is returned.

Is there an explanation for this problem?

All the field value data types are interger.



Thanks for any assistance.

George

[JoeM]

What is the data type for each field?
Can you post the SQL code of your query?

[George]

This is my SQL code. The data types are integer


SELECT [last Name] & " " & [First Name] AS Name, Programs.[Prog Name], Courses.[Course Name], Attendance.CourseMark, Attendance.ExamMark, [Class by Course].Location, Attendance.Atten, [Class by Course].Group, [CourseMark]+[ExamMark] AS Total
FROM Courses INNER JOIN (Programs INNER JOIN (Student INNER JOIN ([Class by Course] INNER JOIN Attendance ON [Class by Course].ClassbyCourseID = Attendance.[Class by course ID]) ON Student.[Student ID] = Attendance.StudentID) ON Programs.[Program Code] = [Class by Course].[Program Code]) ON Courses.[Course ID] = [Class by Course].[Course ID];

[JoeM]

I have 2 field in a table - CourseMarks and ExamMarks.

Kind of misleading here. You are not working with one table, but rather with many tables. Without seeing the structure of your data tables (and maybe some data), it can be difficult to know exactly where you may be having issues. However, here are a few details with may help:

1. Do not use reserved words like "Name" and "Group" as field names. It could cause confusion/issues/errors. For a list of reserved words, see here: http://support.microsoft.com/kb/286335

2. When you are performing INNER JOINS between multiple tables, it will ONLY return records where there are matches in every table. So if a certain value is missing from one of your tables, it will not be part of your final data set. I would recommend determining which table has the complete list of records you want to return, and then use this table in LEFT JOINS with your other tables in order to return all records. You can use the NZ function to return 0 or something else in place of all the NULL values you get in the non-matched values. See this for details on joins: http://office.microsoft.com/en-us/ac...010096320.aspx

If you still cannot figure it out, please post some small data samples from your data, and the expected results (note that screen prints work best for me, as I cannot download files from my current location).

[George]

yes the database has multiple linked tables; But, the focus is only on one called attendance.

[JoeM]

It doesn't matter. You have included them all in your query, so the rules I mentioned apply. If you do not structure your query properly, you will not get the results you want.

If you only really need the Attendence table, then use code like this, then get rid of all the other tables in your query and just use that one. Or at least start from there, and work your way up (adding one table at a time after you confirm you are getting what you expect).

Also note that if you have any NULL values in the two fields you are adding together, you will need to use the NZ function to convert any NULLS to zero (you cannot include a NULL value in addition).

[George]

Thanks. The Nz function did it.