Add New Field and Null and Calculations.

[Kjonnnn]

I'm going to try to simplify this.

I have a access database that keeps track of member contributions (currency) to different categories, i.e., gift1, gift2 gift3.

There are occasions when I need to add a new category to this database, i.e., adding a "gift4" field.

When I add the new field (gift 4), even if the default value is set to zero, the field the blank/null.

This becomes an issue when I do a calculation (query or report) to total fields gift1 to gift4.

If a member has contributed to "gift4" and I enter it, the calculation for fields gift1 thru gift4 is fine. BUT, if a member has not contributed to "gift 4," the calculation for their total (gift1, gift2 and gift3) is off.

What I have been have to do is go to the Table view, and put zeros in the new category field for all the previous records. (Thousands)

Is there a way to:

1. Not have the new empty field interfere with the calculation?

2. Replace the blank/null fields with 0.00 so it will calculate properly.

3. Other suggestions?

 

[dhookom]

My suggestion is to either move your table to Excel (the home of spreadsheets) or normalize your structure in Access (a relational database).

Member contributions do not belong in the member table. They belong in a related table where each contribution creates a new record. Any other table structure IMHO is just wrong. If you don't understand normalization, do a search on google.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Spenney]

Everything Duane says is good advice and it would be worth 'normalizing' yuor structure.

Having said that, if you wish to update your new field (just this once mind!) you could run an update query to set the value to 0 where it is currently <Null>.

Something like...

UPDATE tblMemberConts SET tblMemberConts.gift4 = 0
WHERE ((tblMemberConts.gift4) Is Null);

 

[Kjonnnn]

I stated I was trying to simplify the database explanation for purposes of the question.

The contributions are not in them members table. They are in a separate related table. I've been using the database as a whole for many years with no issue other than this.

Thanks Spenney, I think thats what I'm looking for. I'll try it.

 

[lespaul]

Simplifying is one thing, what you described is not normalized. If you are going to say:

keeps track of member contributions (currency) to different categories, i.e., gift1, gift2 gift3.

Most database professionals are going to assume that you have a table with the fields gift1, gift2, gift3.

Additionally, as someone who has

been using the database as a whole for many years

we would assume that you have had exposure to an UPDATE query, since that's one of the basics of database development.

Leslie

 

[Kjonnnn]

I really thank those for their helph.

But I was trying NOT write out a lengthy discription of the database which I thought would have nothing to do with my the issue.

I'm not a database developer, nor have I pretended to be.

Thanks for letting me what I SHOULD have know.

Sure makes you not want to ask a question.

 

[JerryKlmns]

Kjonnnn

3. Other suggestions?
How about NZ function?

 

[Kjonnnn]

Thanks Jerry I'll check into that one too. Much appreciated.