Hi,

i am using ms-access database and would like to make the auto totol using its field properties: "Validation Rule"

i have entered the formula as =(a)+(b)

Where field C is the sum of A and B.

It shows a error as " cannot use multiple columns in a cloumn level check constraint"

Pls help.

Thanks

Hello. You didn't specify if you want to do it by a VB application or directly in Access' Design View. I guess you meant the second, as you spoke about ValidationRule property. But anyway it makes no sense to build a table like that.

A global rule for all computing matters is "Don't do what computer can do itself". In databases theory this rule was extended and developed, resulting in the First, Second and Third Normal Form rules. Without entering the deep of each normal form, an application of the First is: never put in a field a value that can be retrieved or computed by another field. In fact, we don't really need to store the sum, we don't really need a field to store it.

Usually we consider tables the places where we find and watch our data, but in fact it is not so: every time a db have to show some data, it should do it by a query.
Queries, which are sometimes named "Views", don't really show data, but a copy of it. This prevent user for accidental changes on records information. Yes, there are some query types which can execute actions on records, but they need to be explicitly built and launched, so this assumes who does it knows exactly what he's doing.
When you open a query - a view - you can compute the sum you need every time. In this way your sum is always updated, and your data is protected by accidental editing as well.  The same concept applies to an Age field: once I filled it, it wouldn't be updated anymore the very next day. A BirthDate field is enough, and I can compute the exact age whenever I need to view it.

So don't try to apply any auto-filling criteria to any field: the only one Access recognizes is the Autonumber field, but no more. Access doesn't implement any way to relate by an aggregation function two fields in the same table, and tables have no events to raise in which you can execute a sum or similar.
And this is correct. You better study for another system.

Hope it helps

____________________________
Real Programmer can count up to 1024 on his fingers

Hi yronium,

Thanks for giving some important information.  So using queries i can do the same.

Thanks.

Hi,
Just to add to yronium's very sound advice about database normalization.
Yes, you can use a query to recover and manipulate data in any way that you wish. But if you are using MS Access to display the contents of your database in an Access Form then you can easily do on the Form what you were trying to do within the Table design. As long as you have two bound controls on the Form (bound to fields "A" and "B" in your underlying table) then you can put an unbound control on the Form to display the Total, just set its Control Source Property to
=Sum([A]+[B])
Don't confuse the ValidationRule Property with this Total calculation - the ValidationRule is exactly what it says - a rule that Access will check entered data against to make sure that it is valid - For example to make sure that you only get Positive numbers you would set it to ">0"


____________________________
multi-tasking - the ability to hang more than one app. at the same time.

Thanks a lot, GeoffS.
While I was too taken claiming "A table is not designed to it!", I completely forgot to tell he could do it in a form or in a report, by an unbound control.
You're absolutely right, and I apologize with is269 for my lack.

____________________________
Real Programmer can count up to 1024 on his fingers