Calculations in query

M

mac

Hello
I have an apped queryI have several fieds in my query and a total field
(which total s all the fields. When I run the append query all of the fields
append correctly except the total. Below is the total field

Tot_RB:
Sum(nz([RB_SELF],0)+nz([RB_Mgdcare],0)+nz([RB_nonmgd],0)+nz([RB_Medicare],0)+NZ([RB_MAFFS],0)+nz([RB_Medchoice],0)+nz([RB_Res],0)+NZ([RB_SCO],0)+NZ([RB_nonma],0)+NZ([RB_VA],0)+NZ([RB_ADH],0)+NZ([Rb_Other],0)

The total should be 4,799,335. The total I am getting is
137,287,517,245,003,000. Please what am I doing wrong. Any help wil be
greatly appreciated.
 
J

John Spencer

If you just want to total the row then get rid of SUM.

What you are doing here is adding all the values in the row together and
then summing all the rows.



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
M

mac

Here it is. I figured it out and it is taking each field and adding it to
the end for exanple rb_self is 2654910 RB nonmgd is 34935 total would be
265491034935 plus all the other fields. I am at a total loss. Thank you for
your help.

INSERT INTO Revenue ( VPN, RB_Self, RB_MgdCare, RB_NonMgd, RB_MEDICARE,
RB_MAFFS, RB_MedChoice, RB_RES, RB_SCO, RB_NONMA, RB_VA, RB_OtherPayers,
RB_ADH, TOT_RB, ANC_Self, ANC_MGDCARE, ANC_NonMgd, ANC_MEDICARE, ANC_MAFFS,
ANC_mEDCHOICE, ANC_SCO, ANC_NONMA, ANC_VA, ANC_OtherPayers, Tot_Ancillary,
[REV NON_NURSING], REV_Misc, TOT_REV, Acct_3000, Var )
SELECT [Gather All Revenue Accts_Crosstab].VPN, NZ([Gather All Revenue
Accts_Crosstab]![3003_1]) AS RB_Self, NZ([Gather All Revenue
Accts_Crosstab]![3003_2]) AS RB_MgdCare, NZ([Gather All Revenue
Accts_Crosstab]![3003_3]) AS RB_NonMgd, NZ([Gather All Revenue
Accts_Crosstab]![3003_4]) AS RB_Medicare, NZ([Gather All Revenue
Accts_Crosstab]![3003_6])+NZ([Gather All Revenue
Accts_Crosstab]![3022_6])+NZ([Gather All Revenue Accts_Crosstab]![3003_7]) AS
RB_MAFFS, NZ([Gather All Revenue Accts_Crosstab]![3003_5]) AS RB_MedChoice,
NZ([Gather All Revenue Accts_Crosstab]![3026_3]) AS RB_res, NZ([Gather All
Revenue Accts_Crosstab]![3003_8]) AS RB_SCO, NZ([Gather All Revenue
Accts_Crosstab]![3022_7]) AS RB_NoNMA, NZ([Gather All Revenue
Accts_Crosstab]![3023_2]) AS RB_VA, NZ([Gather All Revenue
Accts_Crosstab]![3003_9]) AS rb_Other, NZ([Gather All Revenue
Accts_Crosstab]![3025_3])+NZ([Gather All Revenue
Accts_Crosstab]![3026_1])+NZ([Gather All Revenue
Accts_Crosstab]![3025_4])+NZ([Gather All Revenue
Accts_Crosstab]![3025_5])+NZ([Gather All Revenue Accts_Crosstab]![3026_2]) AS
RB_ADH,
Sum(nz([Rb_SELF],0)+nz([RB_Mgdcare],0)+nz([RB_nonmgd],0)+nz([RB_Medicare],0)+NZ([RB_MAFFS],0)+nz([RB_Medchoice],0)+nz([RB_Res],0)+NZ([RB_SCO],0)+NZ([RB_nonma],0)+NZ([RB_VA],0)+NZ([RB_ADH],0)+NZ([Rb_Other],0))
AS Tot_RB, NZ([Gather All Revenue Accts_Crosstab]![3005_1]) AS ANC_SELF,
NZ([Gather All Revenue Accts_Crosstab]![3005_2]) AS ANC_MGDCARE, NZ([Gather
All Revenue Accts_Crosstab]![3005_3]) AS ANC_NONMGD, NZ([Gather All Revenue
Accts_Crosstab]![3005_4]) AS ANC_MEDICARE, NZ([Gather All Revenue
AcCts_Crosstab]![3005_6])+NZ([Gather All Revenue
Accts_Crosstab]![3032_6])+NZ([Gather All Revenue Accts_Crosstab]![3005_7]) AS
ANC_MAFFS, NZ([Gather All Revenue Accts_Crosstab]![3005_5]) AS ANC_MEDCHOICE,
NZ([Gather All Revenue Accts_Crosstab]![3005_8]) AS ANC_SCO, NZ([Gather All
Revenue Accts_Crosstab]![3032_7]) AS ANC_NONMa, NZ([Gather All Revenue
Accts_Crosstab]![3033_2]) AS ANC_VA, NZ([Gather All Revenue
Accts_Crosstab]![3005_9]) AS ANC_Other,
NZ([ANC_Self])+NZ([ANC_Mgdcare])+NZ([ANC_nonmgd])+nz([ANC_Medicare])+NZ([ANC_maffs])+NZ([Anc_medchoice])+NZ([ANC_SCO])+NZ([ANC_nonma])+NZ([anc_VA])+NZ([ANC_Other])
AS TOT_ANC, NZ([Gather All Revenue Accts_Crosstab]![3026_0]) AS
Rev_NonNursing, NZ([Gather All Revenue Accts_Crosstab]![3130_0]) AS Rev_MISC,
NZ([Tot_RB])+NZ([Tot_ANC])+([Rev_MISC]) AS TOT_REV, [Gather All Revenue
Accts_Crosstab]![3000_0] AS Acct_3000, NZ([TOT_REV])-NZ([Acct_3000]) AS Var
FROM [Gather All Revenue Accts_Crosstab]
GROUP BY [Gather All Revenue Accts_Crosstab].VPN, NZ([Gather All Revenue
Accts_Crosstab]![3003_1]), NZ([Gather All Revenue Accts_Crosstab]![3003_2]),
NZ([Gather All Revenue Accts_Crosstab]![3003_3]), NZ([Gather All Revenue
Accts_Crosstab]![3003_4]), NZ([Gather All Revenue
Accts_Crosstab]![3003_6])+NZ([Gather All Revenue
Accts_Crosstab]![3022_6])+NZ([Gather All Revenue Accts_Crosstab]![3003_7]),
NZ([Gather All Revenue Accts_Crosstab]![3003_5]), NZ([Gather All Revenue
Accts_Crosstab]![3026_3]), NZ([Gather All Revenue Accts_Crosstab]![3003_8]),
NZ([Gather All Revenue Accts_Crosstab]![3022_7]), NZ([Gather All Revenue
Accts_Crosstab]![3023_2]), NZ([Gather All Revenue Accts_Crosstab]![3003_9]),
NZ([Gather All Revenue Accts_Crosstab]![3025_3])+NZ([Gather All Revenue
Accts_Crosstab]![3026_1])+NZ([Gather All Revenue
Accts_Crosstab]![3025_4])+NZ([Gather All Revenue
Accts_Crosstab]![3025_5])+NZ([Gather All Revenue Accts_Crosstab]![3026_2]),
NZ([Gather All Revenue Accts_Crosstab]![3005_1]), NZ([Gather All Revenue
Accts_Crosstab]![3005_2]), NZ([Gather All Revenue Accts_Crosstab]![3005_3]),
NZ([Gather All Revenue Accts_Crosstab]![3005_4]), NZ([Gather All Revenue
AcCts_Crosstab]![3005_6])+NZ([Gather All Revenue
Accts_Crosstab]![3032_6])+NZ([Gather All Revenue Accts_Crosstab]![3005_7]),
NZ([Gather All Revenue Accts_Crosstab]![3005_5]), NZ([Gather All Revenue
Accts_Crosstab]![3005_8]), NZ([Gather All Revenue Accts_Crosstab]![3032_7]),
NZ([Gather All Revenue Accts_Crosstab]![3033_2]), NZ([Gather All Revenue
Accts_Crosstab]![3005_9]), NZ([Gather All Revenue Accts_Crosstab]![3026_0]),
NZ([Gather All Revenue Accts_Crosstab]![3130_0]), [Gather All Revenue
Accts_Crosstab]![3000_0];

--
thank you mac


KARL DEWEY said:
Post the complete SQL statement.
--
KARL DEWEY
Build a little - Test a little


mac said:
Hello
I have an apped queryI have several fieds in my query and a total field
(which total s all the fields. When I run the append query all of the fields
append correctly except the total. Below is the total field

Tot_RB:
Sum(nz([RB_SELF],0)+nz([RB_Mgdcare],0)+nz([RB_nonmgd],0)+nz([RB_Medicare],0)+NZ([RB_MAFFS],0)+nz([RB_Medchoice],0)+nz([RB_Res],0)+NZ([RB_SCO],0)+NZ([RB_nonma],0)+NZ([RB_VA],0)+NZ([RB_ADH],0)+NZ([Rb_Other],0)

The total should be 4,799,335. The total I am getting is
137,287,517,245,003,000. Please what am I doing wrong. Any help wil be
greatly appreciated.
Click to expand...
Click to expand...
 
J

John Spencer

Sometimes in a query, NZ will force a string

Try forcing a number type on each use of NZ where you have numbers
involved. You can use CCur if the values are all currency

CCur(NZ([SomeField],0))

CDbl forces the number to type Double

CLng to long integer (only good if you have NO decimal parts to your number)



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Here it is. I figured it out and it is taking each field and adding it to
the end for exanple rb_self is 2654910 RB nonmgd is 34935 total would be
265491034935 plus all the other fields. I am at a total loss. Thank you for
your help.

INSERT INTO Revenue ( VPN, RB_Self, RB_MgdCare, RB_NonMgd, RB_MEDICARE,
RB_MAFFS, RB_MedChoice, RB_RES, RB_SCO, RB_NONMA, RB_VA, RB_OtherPayers,
RB_ADH, TOT_RB, ANC_Self, ANC_MGDCARE, ANC_NonMgd, ANC_MEDICARE, ANC_MAFFS,
ANC_mEDCHOICE, ANC_SCO, ANC_NONMA, ANC_VA, ANC_OtherPayers, Tot_Ancillary,
[REV NON_NURSING], REV_Misc, TOT_REV, Acct_3000, Var )
SELECT [Gather All Revenue Accts_Crosstab].VPN, NZ([Gather All Revenue
Accts_Crosstab]![3003_1]) AS RB_Self, NZ([Gather All Revenue
Accts_Crosstab]![3003_2]) AS RB_MgdCare, NZ([Gather All Revenue
Accts_Crosstab]![3003_3]) AS RB_NonMgd, NZ([Gather All Revenue
Accts_Crosstab]![3003_4]) AS RB_Medicare, NZ([Gather All Revenue
Accts_Crosstab]![3003_6])+NZ([Gather All Revenue
Accts_Crosstab]![3022_6])+NZ([Gather All Revenue Accts_Crosstab]![3003_7]) AS
RB_MAFFS, NZ([Gather All Revenue Accts_Crosstab]![3003_5]) AS RB_MedChoice,
NZ([Gather All Revenue Accts_Crosstab]![3026_3]) AS RB_res, NZ([Gather All
Revenue Accts_Crosstab]![3003_8]) AS RB_SCO, NZ([Gather All Revenue
Accts_Crosstab]![3022_7]) AS RB_NoNMA, NZ([Gather All Revenue
Accts_Crosstab]![3023_2]) AS RB_VA, NZ([Gather All Revenue
Accts_Crosstab]![3003_9]) AS rb_Other, NZ([Gather All Revenue
Accts_Crosstab]![3025_3])+NZ([Gather All Revenue
Accts_Crosstab]![3026_1])+NZ([Gather All Revenue
Accts_Crosstab]![3025_4])+NZ([Gather All Revenue
Accts_Crosstab]![3025_5])+NZ([Gather All Revenue Accts_Crosstab]![3026_2]) AS
RB_ADH,
Sum(nz([Rb_SELF],0)+nz([RB_Mgdcare],0)+nz([RB_nonmgd],0)+nz([RB_Medicare],0)+NZ([RB_MAFFS],0)+nz([RB_Medchoice],0)+nz([RB_Res],0)+NZ([RB_SCO],0)+NZ([RB_nonma],0)+NZ([RB_VA],0)+NZ([RB_ADH],0)+NZ([Rb_Other],0))
AS Tot_RB, NZ([Gather All Revenue Accts_Crosstab]![3005_1]) AS ANC_SELF,
NZ([Gather All Revenue Accts_Crosstab]![3005_2]) AS ANC_MGDCARE, NZ([Gather
All Revenue Accts_Crosstab]![3005_3]) AS ANC_NONMGD, NZ([Gather All Revenue
Accts_Crosstab]![3005_4]) AS ANC_MEDICARE, NZ([Gather All Revenue
AcCts_Crosstab]![3005_6])+NZ([Gather All Revenue
Accts_Crosstab]![3032_6])+NZ([Gather All Revenue Accts_Crosstab]![3005_7]) AS
ANC_MAFFS, NZ([Gather All Revenue Accts_Crosstab]![3005_5]) AS ANC_MEDCHOICE,
NZ([Gather All Revenue Accts_Crosstab]![3005_8]) AS ANC_SCO, NZ([Gather All
Revenue Accts_Crosstab]![3032_7]) AS ANC_NONMa, NZ([Gather All Revenue
Accts_Crosstab]![3033_2]) AS ANC_VA, NZ([Gather All Revenue
Accts_Crosstab]![3005_9]) AS ANC_Other,
NZ([ANC_Self])+NZ([ANC_Mgdcare])+NZ([ANC_nonmgd])+nz([ANC_Medicare])+NZ([ANC_maffs])+NZ([Anc_medchoice])+NZ([ANC_SCO])+NZ([ANC_nonma])+NZ([anc_VA])+NZ([ANC_Other])
AS TOT_ANC, NZ([Gather All Revenue Accts_Crosstab]![3026_0]) AS
Rev_NonNursing, NZ([Gather All Revenue Accts_Crosstab]![3130_0]) AS Rev_MISC,
NZ([Tot_RB])+NZ([Tot_ANC])+([Rev_MISC]) AS TOT_REV, [Gather All Revenue
Accts_Crosstab]![3000_0] AS Acct_3000, NZ([TOT_REV])-NZ([Acct_3000]) AS Var
FROM [Gather All Revenue Accts_Crosstab]
GROUP BY [Gather All Revenue Accts_Crosstab].VPN, NZ([Gather All Revenue
Accts_Crosstab]![3003_1]), NZ([Gather All Revenue Accts_Crosstab]![3003_2]),
NZ([Gather All Revenue Accts_Crosstab]![3003_3]), NZ([Gather All Revenue
Accts_Crosstab]![3003_4]), NZ([Gather All Revenue
Accts_Crosstab]![3003_6])+NZ([Gather All Revenue
Accts_Crosstab]![3022_6])+NZ([Gather All Revenue Accts_Crosstab]![3003_7]),
NZ([Gather All Revenue Accts_Crosstab]![3003_5]), NZ([Gather All Revenue
Accts_Crosstab]![3026_3]), NZ([Gather All Revenue Accts_Crosstab]![3003_8]),
NZ([Gather All Revenue Accts_Crosstab]![3022_7]), NZ([Gather All Revenue
Accts_Crosstab]![3023_2]), NZ([Gather All Revenue Accts_Crosstab]![3003_9]),
NZ([Gather All Revenue Accts_Crosstab]![3025_3])+NZ([Gather All Revenue
Accts_Crosstab]![3026_1])+NZ([Gather All Revenue
Accts_Crosstab]![3025_4])+NZ([Gather All Revenue
Accts_Crosstab]![3025_5])+NZ([Gather All Revenue Accts_Crosstab]![3026_2]),
NZ([Gather All Revenue Accts_Crosstab]![3005_1]), NZ([Gather All Revenue
Accts_Crosstab]![3005_2]), NZ([Gather All Revenue Accts_Crosstab]![3005_3]),
NZ([Gather All Revenue Accts_Crosstab]![3005_4]), NZ([Gather All Revenue
AcCts_Crosstab]![3005_6])+NZ([Gather All Revenue
Accts_Crosstab]![3032_6])+NZ([Gather All Revenue Accts_Crosstab]![3005_7]),
NZ([Gather All Revenue Accts_Crosstab]![3005_5]), NZ([Gather All Revenue
Accts_Crosstab]![3005_8]), NZ([Gather All Revenue Accts_Crosstab]![3032_7]),
NZ([Gather All Revenue Accts_Crosstab]![3033_2]), NZ([Gather All Revenue
Accts_Crosstab]![3005_9]), NZ([Gather All Revenue Accts_Crosstab]![3026_0]),
NZ([Gather All Revenue Accts_Crosstab]![3130_0]), [Gather All Revenue
Accts_Crosstab]![3000_0];
Click to expand...
 
J

John W. Vinson

Here it is. I figured it out and it is taking each field and adding it to
the end for exanple rb_self is 2654910 RB nonmgd is 34935 total would be
265491034935 plus all the other fields. I am at a total loss. Thank you for
your help.
Click to expand...

As John Spencer said, you're not only adding across the record, you're also
summing all those totals. If you want to store this value (see below!!) just
drop the Sum, and use

(nz([Rb_SELF],0)+nz([RB_Mgdcare],0)+nz([RB_nonmgd],0)+nz([RB_Medicare],0)+NZ([RB_MAFFS],0)+nz([RB_Medchoice],0)+nz([RB_Res],0)+NZ([RB_SCO],0)+NZ([RB_nonma],0)+NZ([RB_VA],0)+NZ([RB_ADH],0)+NZ([Rb_Other],0))
AS Tot_RB,

HOWEVER...

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

John W. Vinson [MVP]
 
M

mac

IGood morning,

I tried it without the sum and this is what I got
2.65491003493602E+19

I really appreciate the time you are taking to help me. Thank you all.
 
M

mac

It takes a while to grasp things, but I finally got what you were telling me.
I changed the sum to CCur and it works beautifully. Again thank you for the
help.
--
thank you mac


mac said:
IGood morning,

I tried it without the sum and this is what I got
2.65491003493602E+19

I really appreciate the time you are taking to help me. Thank you all.
--
thank you mac


mac said:
Hello
I have an apped queryI have several fieds in my query and a total field
(which total s all the fields. When I run the append query all of the fields
append correctly except the total. Below is the total field

Tot_RB:
Sum(nz([RB_SELF],0)+nz([RB_Mgdcare],0)+nz([RB_nonmgd],0)+nz([RB_Medicare],0)+NZ([RB_MAFFS],0)+nz([RB_Medchoice],0)+nz([RB_Res],0)+NZ([RB_SCO],0)+NZ([RB_nonma],0)+NZ([RB_VA],0)+NZ([RB_ADH],0)+NZ([Rb_Other],0)

The total should be 4,799,335. The total I am getting is
137,287,517,245,003,000. Please what am I doing wrong. Any help wil be
greatly appreciated.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

CALCULATION IN QUERY 4
Running total in query 2
adding dsum results in a query 3
Need zeros for null values in crosstab query 6
Missing Row Field in Crosstab Query 1
Missing Row Field in Crosstab Query 0
Reduce # of date columns in crosstab query 4
Invalid use of null in query expression 3

Top