My database (Access 2003) has a table which contains employee-wise, 
month-wise activity-wise (knowledge management, Administrative tasks, Idle, 
etc) data.
Using a cross-tab query I am able to analyse what is the time spent by each 
employee, each month amongst different activities.
Since the number of activities is large and some of them are partly 
overlapping I require to combine some of the columns in the cross-tab query. 
However, when I tried to create a calculated field in a separate select 
query based on the cross - tab query to combine the activities "Leave" and 
"Leave (part of the day)", the calculated field returned a value only when 
there were values for both the fields in the cross - tab query and not when 
either one of them was blank.
How do I ensure that calculated field sums the two columns irrespective of 
whether one of them is blank?

It may be that the Nz function will take care of the problem:
NewField: Nz([Field1],0) + Nz([Field2],0)
See help for more information about Nz.  The problem may come when one of 
the fields is Null, which can be thought of as Unknown.  Unknown plus a 
number is still Unknown.  Nz converts the null to 0.

It usually helps when posting a problem such as this to show exactly what 
you have tried (in this case, the expression that did not produce the 
intended result).


"alphaorionis57" <alphaorionis57@discussions.microsoft.com> wrote in message 
news:26C0E4C9-9FBE-4E4D-B68D-0DED5D7E43FC@microsoft.com...
> My database (Access 2003) has a table which contains employee-wise,
> month-wise activity-wise (knowledge management, Administrative tasks, 
> Idle,
> etc) data.
> Using a cross-tab query I am able to analyse what is the time spent by 
> each
> employee, each month amongst different activities.
> Since the number of activities is large and some of them are partly
> overlapping I require to combine some of the columns in the cross-tab 
> query.
> However, when I tried to create a calculated field in a separate select
> query based on the cross - tab query to combine the activities "Leave" and
> "Leave (part of the day)", the calculated field returned a value only when
> there were values for both the fields in the cross - tab query and not 
> when
> either one of them was blank.
> How do I ensure that calculated field sums the two columns irrespective of
> whether one of them is blank?