Adding fields together

[WLC]

Ok. So this should really be simple but for some reason, I am not getting the result I want.

I have the following fields:

Incomplete: Nz([Incomp],0) - my original field name was "Incomp". I renamed it to Incomplete and defaulted it to zero.

Complete: Nz([com],0) - my original field name was "com". I renamed it to Complete and defaulted it to zero.

I am trying to create a new field that will be a sum of the two above fields. This is what is entered in my field: Possible Total: [Incomplete]+[Complete].

For example sake, lets say I have a zero in Incomplete and a 4 in Complete. One would think my result would be 4 (i.e. 0+4=4). But no, my result is 04 (like it concatenated them together).

Another example, Incomplete=2 and Complete=29. My result should be 31. My actual result is 229.

Thoughts?

 

[MSAccessRookie]

The first thing that comes to mind is to check the Types of the original data Fields. Your results would seem to indicate that at least one of the Field Types is String. As a rule, Nz([Incomp],0) and Nz([com],0) would both return numbers unless the Field Type is String. If Either of them is a String, then the results could look like what you are experiencing.

 

[WLC]

Let me back up a little bit then. And I'm quite sure I've gone about this some backwards way so feel free to offer suggestions.

First off, my overall goal is a cross-tab query result that I can put into a report.

My starting table is a giant log of daily information. This table contains (among other fields):
Desk (number - to which I am linking to a "desk" table to get the actual desk name
Activity (text)
Date (date/time)
Correct (yes/no)

I had to create a make table query first, then a cross-tab query, then a select query. If this can all be done as one query where I don't have to write to a table, that would be great.

So here is my first query - a make table. notice I am hand-keying a desk and a date range:

SELECT Desk.Desk, [Non Log Table].Activity, [Non Log Table].Date, [Non Log Table].Correct, 1 AS Quantity, IIf([non log table].[Correct]=0,"Incomp","Comp") AS test INTO tbl01Test
FROM Desk INNER JOIN [Non Log Table] ON Desk.ID = [Non Log Table].Desk
WHERE (((Desk.Desk)=[Enter Desk]) AND (([Non Log Table].Date) Between [Start Date] And [End Date]))
ORDER BY Desk.Desk, [Non Log Table].Activity, [Non Log Table].Date;

Then my cross-tab query:

TRANSFORM Sum(tbl01Test.Quantity) AS SumOfQuantity
SELECT tbl01Test.Activity
FROM tbl01Test
GROUP BY tbl01Test.Activity
PIVOT tbl01Test.test;

Then finally, my select query (which turns out my errant results):

SELECT qry02Test.Activity, Nz([Incomp],0) AS Incomplete, Nz([comp],0) AS Complete, [Incomplete]+[Complete] AS [Possible Total], [Complete]/[Possible Total] AS [Percent Correct]
FROM qry02Test;

Hopefully this will make sense. And thank you for taking the time to look at this.