Can't add a sum field and a count field... 2

[JonWolgamuth]

I have a database where users enter transactions. Some transactions require that the users enter a quantity (which I "sum&quot, and other transactions are by definition 1, so I just "count" those). There are certain transactions where the quantity is optional.

For some reason when I do the addition, I don't get the items that are counted, just the summed items. Items that are only counted show up as blank. However, when a particular item has a sum and a count, it works.

This pre-query result...

Week# Transaction Quantity Count
19 Appointment 241
19 Confirmation 54 7
19 Expiry Update 66
19 Invoice 241 1
19 New Business 1
19 POS Database 2
19 Renewal 920 1
19 Returned Mail 8 1
19 Service Agent 120 18
19 State Reject 9

added together using <Total: [Quantity]+[Count]> becomes this...

Week# Transaction Total
19 Appointment
19 Confirmation 61
19 Expiry Update
19 Invoice 242
19 New Business
19 POS Database
19 Renewal 921
19 Returned Mail 9
19 Service Agent 138
19 State Reject

What gives?

Jon

 

[grnzbra]

If you are counting only 1's, wouldn't you get the same result if you summed them?

 

[grnzbra]

Ignore my previous; I misunderstood what you were asking.

 

[petermeachem]

Your count is wrong ( trust me to point out the obvious ) and contains Null. Something + Null = Null. You need to look at your count code. Peter Meachem
peter@accuflight.com

Support Joanna's Bikeathon

http://www.accuflight.com/blue_peter_bikeathon_2002.htm

 

[JonWolgamuth]

Not sure I understand. I don't have a &quot;count code&quot;, I'm merely using grouping to determine how many times the transaction shows.

Jon

 

[grnzbra]

OK. Let's try this. Count is a function name and it may be confusing things if you are using it for a fieldname. Try NumbCnt: = Count(etc. If you have problems with nulls, you could try using the following function =NZ(fieldname,0). It will replace the nulls with zeros.

 

[JonWolgamuth]

grnzbra, thanks for sticking with me! That was exactly the problem!

Have a great day!

Jon