CALCULATION IN QUERY

  • Thread starter Thread starter MAC
  • Start date Start date
M

MAC

HELLO,
I am trying to sum several fields to get a total
This is the calculation but my total are wrong, Can you
tell me what I am doing wrong. Any help will be wonderful.



TOT_REV: NZ([RB_SELF])+NZ([RB_MGDCARE])+NZ([RB_NONMGD])+NZ
([RB_Medicare])+NZ([RB_MAFFS])+NZ([RB_MEDCHOICE])+NZ
([RB_MAMGD])+NZ([RB_SCO])+NZ([RB_NONMA])+NZ([RB_VA])+NZ
([RB_ADH])+NZ([RB_OTHER])+NZ([ANC_SELF])+NZ([ANC_MGDCARE])
+NZ([ANC_NONMGD])+NZ([ANC_MEDICARE])+NZ([ANC_OTHER])+NZ
([ANC_MAFFS])+NZ([ANC_MEDCHOICE])+NZ([ANC_SCO])+NZ
([ANC_NONMA])+nz([ANC_MAMGD])+NZ([ANC_va])+NZ([RB_MISC])
 
Try being explicit about the value to use if the field is null:

TOT_REV: NZ([RB_SELF],0) + NZ([RB_MGDCARE],0) + ...
 
I TRIED, DIDN'T WORK. IT SEEMS TO TAKE THE FIRST VALUE
([rb_sELF])OUT OF THE TOTAL, ONLY ON SOME OF THE ROWS
WHICH DOES NOT MAKE SENSE TO ME. ANY IDEAS?
-----Original Message-----
Try being explicit about the value to use if the field is null:

TOT_REV: NZ([RB_SELF],0) + NZ([RB_MGDCARE],0) + ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

HELLO,
I am trying to sum several fields to get a total
This is the calculation but my total are wrong, Can you
tell me what I am doing wrong. Any help will be wonderful.



TOT_REV: NZ([RB_SELF])+NZ([RB_MGDCARE])+NZ([RB_NONMGD]) +NZ
([RB_Medicare])+NZ([RB_MAFFS])+NZ([RB_MEDCHOICE])+NZ
([RB_MAMGD])+NZ([RB_SCO])+NZ([RB_NONMA])+NZ([RB_VA])+NZ
([RB_ADH])+NZ([RB_OTHER])+NZ([ANC_SELF])+NZ ([ANC_MGDCARE])
+NZ([ANC_NONMGD])+NZ([ANC_MEDICARE])+NZ([ANC_OTHER])+NZ
([ANC_MAFFS])+NZ([ANC_MEDCHOICE])+NZ([ANC_SCO])+NZ
([ANC_NONMA])+nz([ANC_MAMGD])+NZ([ANC_va])+NZ([RB_MISC])
Click to expand...


.
Click to expand...
 
SORRY IT MULTILY BY 100
-----Original Message-----
Try being explicit about the value to use if the field is null:

TOT_REV: NZ([RB_SELF],0) + NZ([RB_MGDCARE],0) + ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

HELLO,
I am trying to sum several fields to get a total
This is the calculation but my total are wrong, Can you
tell me what I am doing wrong. Any help will be wonderful.



TOT_REV: NZ([RB_SELF])+NZ([RB_MGDCARE])+NZ([RB_NONMGD]) +NZ
([RB_Medicare])+NZ([RB_MAFFS])+NZ([RB_MEDCHOICE])+NZ
([RB_MAMGD])+NZ([RB_SCO])+NZ([RB_NONMA])+NZ([RB_VA])+NZ
([RB_ADH])+NZ([RB_OTHER])+NZ([ANC_SELF])+NZ ([ANC_MGDCARE])
+NZ([ANC_NONMGD])+NZ([ANC_MEDICARE])+NZ([ANC_OTHER])+NZ
([ANC_MAFFS])+NZ([ANC_MEDCHOICE])+NZ([ANC_SCO])+NZ
([ANC_NONMA])+nz([ANC_MAMGD])+NZ([ANC_va])+NZ([RB_MISC])
Click to expand...


.
Click to expand...
 
Break the field down until you start aggregating totals successfully, and
then identify where it is not working.

You might also see if your keyboard has a Caps Lock key. Messages in all
upper case are interpreted as shouting in this environment.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

MAC said:
I TRIED, DIDN'T WORK. IT SEEMS TO TAKE THE FIRST VALUE
([rb_sELF])OUT OF THE TOTAL, ONLY ON SOME OF THE ROWS
WHICH DOES NOT MAKE SENSE TO ME. ANY IDEAS?
-----Original Message-----
Try being explicit about the value to use if the field is null:

TOT_REV: NZ([RB_SELF],0) + NZ([RB_MGDCARE],0) + ...
Click to expand...
Click to expand...
 
Back
Top