zero sums

[Guest]

I'm confused about the Is Null feature in queries.
I have a query that sums 25 values, val1, val2,val3.... From that i print
out a bar chart in a report that shows the magnitude of these values. I
would like to ignore zero summed values so it fits nicer on the chart. How
would i enter in the code either in SQL or in the criteria of the query?

 

[Tom Ellison]

Dear SoLah:

After the GROUP BY clause, and before the ORDER BY clause (if any) put in:

HAVING SUM(val1) <> 0 AND . . .

Does this do it?

Tom Ellison

 

[Jeff Boyce]

If you are saying that you don't want to include rows that have nulls in any
of the fields you are summing, you would include all the fields you are
summing in the query, uncheck the "Show" box, and put something like the
following in the criterion beneath each:
Is Not Null

However, if you have a table with fields named something like [Val1] and
[Val2] and [Val3] and ..., you probably have a spreadsheet, not a relational
database. Access provides a very powerful (and very easy) Totals query, but
it only works on well-normalized data. That is, one field with values can
be summed quite easily in an Access Totals query, but you will have to work
quite hard to "sum" a series of fields as your description implies.

Regards

Jeff Boyce
Microsoft Office/Access MVP