Summing olumns in Access

[Laura1 via AccessMonster.com]

Data in most of the data fields however some are null

Make table query:

Quarterly Revenue: [Tbl_Referrals_W_Expiration_Date]![10/31/2006]+
[Tbl_Referrals_W_Expiration_Date]![11/30/2006]+
[Tbl_Referrals_W_Expiration_Date]![12/31/2006]

The results are not consistent. Access is not summing all the values for all
the entries in each column, even if the fields are all populated for all
three timeframes. Sometimes I get the total other times I don't. Doesn't
seem to be tied to whether or not values are in all fields.

Table is small only contains 2010 records.

What am I doing wrong?

 

[Marshall Barton]

Data in most of the data fields however some are null

Make table query:

Quarterly Revenue: [Tbl_Referrals_W_Expiration_Date]![10/31/2006]+
[Tbl_Referrals_W_Expiration_Date]![11/30/2006]+
[Tbl_Referrals_W_Expiration_Date]![12/31/2006]

The results are not consistent. Access is not summing all the values for all
the entries in each column, even if the fields are all populated for all
three timeframes. Sometimes I get the total other times I don't. Doesn't
seem to be tied to whether or not values are in all fields.

What a mess. You really should not have data dependent
fields in a table. Instead, you should have a table with
three fields, one for the date and one for the amount and a
foreign key to link to a referral table.

Ignoring all that, the addition will not produce a result is
any of your fields contains a Null value. This is easy to
deal with by using the Nz function:

Quarterly Revenue: Nz([10/31/2006],0) + Nz([11/30/2006],0) +
Nz([12/31/2006],0)

If that doesn'e completely resolve your issue, then inspect
the field value for anomolous values. Also check the fields
to make sure they are a numeric or Currency type. If the
fields are type Text, all kinds of things can/will go wrong.

 

[Laura1 via AccessMonster.com]

This was great help!! Thank you!

Data in most of the data fields however some are null

[quoted text clipped - 8 lines]

three timeframes. Sometimes I get the total other times I don't. Doesn't
seem to be tied to whether or not values are in all fields.

What a mess. You really should not have data dependent
fields in a table. Instead, you should have a table with
three fields, one for the date and one for the amount and a
foreign key to link to a referral table.

Ignoring all that, the addition will not produce a result is
any of your fields contains a Null value. This is easy to
deal with by using the Nz function:

Quarterly Revenue: Nz([10/31/2006],0) + Nz([11/30/2006],0) +
Nz([12/31/2006],0)

If that doesn'e completely resolve your issue, then inspect
the field value for anomolous values. Also check the fields
to make sure they are a numeric or Currency type. If the
fields are type Text, all kinds of things can/will go wrong.