Calculating the Sum of Mulitple Columns

[ARickert]

I don't understand, because the datbase/query I sent you has the expression in there... column sum FP, are you telling me you actually see numbers in that column???

[ARickert]

Hopefully you can see this one.

[ssanfu]

Yes, I used the query you posted and cut out the fields I didn't include in the table I created. My query returns the correct values - the same as the query in A2K3.

[ssanfu]

OK, got it!!! Remember when I was talking about fields being Empty or NULL??? It looks like, in A2K7 (), ANYTHING added to a NULL results in a NULL. So if one or more of the fields for a record (in this case [FP (Dep)], [FP (PTSD)] or [FP(Both)] ) is NULL, the result will be NULL.

Try running this query:

SELECT MonthlyPct.intMonth, MonthlyPct.intDay, MonthlyPct.intYear, MonthlyPct.FP_Dep, MonthlyPct.FP_PTSD, MonthlyPct.FP_Both, nz([FP_Dep],0)+nz([FP_PTSD],0)+nz([FP_Both],0) AS Expr1
FROM MonthlyPct
WHERE Not IsNull([Monthly %].[Suicide Risk]);

You could remove the WHERE clause if you want.

So the options are:

1) Default the 3 fields to zero when creating a new record, or
2) Wrap the field names with the NZ() function (Null-to-zero function).

[ARickert]

I'm not 100% sure what that was supposed to do, as it didn't fix the problem on my end. The "where is not null" is only in the risk assessment portion and should have nothing to do with the FP funciton. I tried to NZ(), and that didn't do anything either. IDK. Every logical thing that should work, just doesn't.

I don't want to default the negatives to 0, because I don't want to be able to see the -1's at a glance, so the items not selected are defaulted to put nothing.

[ssanfu]

I'm not 100% sure what that was supposed to do, as it didn't fix the problem on my end.

OK, let's start over.

Create a new table, with the following fields:

ID autonumber PK
Dep number long
PTSD number long
Both number long

(be sure to remove the default value of 0 for the number fields.)

Name the table "tblTest"

Now add records:

In the first row, enter -1, -1, -1
in the second row, enter 0, 0, -1
third row, enter -1 in the [Both] field
fourth row, enter -1 in the [PSTD] field
fifth row, enter -1 in the [Dep] field

Create a query:

SELECT TestNames.Dep, TestNames.PTSD, TestNames.Both, [Dep]+[PTSD]+[Both] AS Expr1 FROM TestNames;

I expect two of the rows to have a number in the [Expr1] column and the others to have nothing in them.

If you run this query:

SELECT TestNames.Dep, TestNames.PTSD, TestNames.Both, nz([Dep],0)+nz([PTSD],0)+nz([Both],0) AS Expr1
FROM TestNames;

I expect that all rows in the [Expr1] column to have a number.

---------------------------------------------------------------

The above was to test how A2K7 handles adding numbers when some values are NULL.

I would have the default value for the three fields set to zero in the table. It would help with the calculation and it doesn't matter if the fields default to zero in a new record because the user shouldn't be looking at or entering data directly into a table.


In tblTest, enter zeros for the missing numbers, so that all fields have either a zero or -1. Run the query to ensure that all fields/rows have either zero or -1

In the design view of the query, right click on a column and select properties. In the General Tab, in the Format row enter

0;-0;"";""

(see Help: Format Property - Number and Currency Data Types /Custom Formats)

Do this for all columns (not the ID column). Run the query. Now only the non zero numbers should be visible.

There is also the format property for controls on a form/report.


To recap:

• I would have a default value for number type fields
• If there is not a default value for numbers, then you should the Null to Zero function in queries and calculated controls

• Use custom formats to control how values are displayed in queries and controls on forms/reports.

[ARickert]

Ok, so that worked, when I added the 0's in. But once I input the 0;-0;"" to get the 0's out of the main table, it stopped calculating some of the new inputs. I've pretty much given up on what I want to do. If you would like to continue to try to figure it out, by all means I appriciate all your help, but if not, I'll just wash my hands of it.

[ssanfu]

Ok, so that worked, when I added the 0's in. But once I input the 0;-0;"" to get the 0's out of the main table, it stopped calculating some of the new inputs. I've pretty much given up on what I want to do. If you would like to continue to try to figure it out, by all means I appriciate all your help, but if not, I'll just wash my hands of it.

I read you post several times and I think I finally understand what you did. I couldn't understand why it didn't work for you.

The custom format (0;-0;"";"") doesn't go in the table. It goes in the query or in the controls on a form/report. The table is just a storage bin. No formatting, no look up fields, no nothing!! Just the raw data.

Take a look at the attached database. I modified your previous attached database.

Don't give up yet!!