Issue summing query fields when no data exists

[accessmatt]

Hello,

How can I sum multiple fields in a query. What's happening is that if there's 0's in those fields it simple excludes that whole row from my count (even if there's data in other fields of the calculation).

[June7]

Don't understand. 0 value should not exclude records.

Post sql statement for analysis.

[accessmatt]

Hello June7, it looks like those fields are actualyl Null, there are no 0s. however I used Nz in the calculation hoping to effect that. However what's happening it's simple summing only rows where all of these fields are already populated with data.

SELECT [Mobile Data Input].Application, CLng(Nz([Mobile Data Input]![2010]+[Mobile Data Input]![2011]+[Mobile Data Input]![2012]+[Mobile Data Input]![2013]+[Mobile Data Input]![2014],0)) AS [Lifetime Downloads]FROM [Mobile Data Input]
WHERE ((([Mobile Data Input].Application) Not In ("Application","Total")));

[June7]

Having a field for each year indicates a non-normalized data structure. Is that the structure of the raw data? A design that requires routine modification is problematic at best.

Why using the CLng function?

Arithmetic with null results in null. If you want to still sum the non-null fields need to use Nz() on each field.

Nz([2010])+Nz([2011])+Nz([2012])+Nz([2013])+Nz([2014])

[accessmatt]

June7, BINGO, you nailed it again. The data I'm dealing with is terrible and I'm really using Access to normalize it through queries, etc.