problem with sum

[RCMetrics]

I have a table with an Employee ID field and several other fields that has secods values as records.

The emplyee field properties is set to "text"

all the other ones are "numbers"

I import from a csv file the emplyee ID and values that goes in their respective colums in the table.

but all fields other than Employee ID may be >0, 0 or null (empty)

When I do a query do sum all these "number" fields, unless I have a value of 0 or >0 in each column, the sum wont work.

I want the query to sum an empty field as 0 (or to ignore it)

Apperently, the fact that some of these records are null or empty... it effects my overall Query that sum all of them.

Now I can't have the criteria set to >0 because that whole record line will be ignore provided that one of the record was not >0

How can I get this to work?

Here's an exemple:

Employee ID = 555
Field1 = 5
Field2 = leave empty
Field3 = 10

Technically, I want the answer to be 15 for employee 555
but result I get is "null" (empty) unless the "leave empty" above would be a 0

I cannot do replace all, or a query that changes those empty fields... I have over 2 Millions of records in this table.

 

[CT Witter]

Try looking at the MS KB article: 209839 - ACC2000: How to Calculate Row-Level Statistics http://support.microsoft.com/default.aspx?scid=kb;en-us;209839

I believe this is what you want.

HTH,
CT

 

[GLFMHoward]

If it doesn't make a difference whether the field is empty or 0 ...

... update all your current records. Even with 2,000,000 records you should be able to use an Update query. Make a copy of your table first and test it.

... and, for going forward, if you're importing data directly into the table, set the field defaults to 0 so if there isn't a value it will autopopulate the 0 for you.

Hope this helps!

H