How do i add 3 Number/ Currency fields together?

[Mart19881988]

It sounds real stupid but im having trouble doing this even tho i could a few months ago, maybe because im using Access 2007 now?

Anyway im trying to add these 3 fields in my table:
"Cash"
"Cheque"
"Card"
in this field: "Money In"
All of the fields are Number, i tried in currency but failed in that too and would prefer it as currency.
The validation rule is playing me up telling me to use parenthesis then rejecting it anyway so im at a loss for ideas now.

Anyone got any comments as to how i do this?

 

[namliam]

You shouldnt store calculated values, in particular one that is as simple as this one.

Do it in a query instead!

It is actually as simple as ...
"Cash" + "Cheque" + "Card"

 

[Mart19881988]

Thanx

Im just doin what the boss wants

Thanx a lot you cant find this anywere in the help or on other sites (even if it is so simple)
So thanks a lot, i owe you one mate

Why does it make a difference doing this in a table rather than a query?

Oh an i just tried it out with "Cash" as 8, "Cheque" as 4 and "Card" as 36
i got this message: "One or more values are prohibited by the validation rule ""Cash"+"Cheque"+"Card"="Money In"" set for 'Expenses.Money In'. Enter a value that the expression for this filed can accept."

 

[namliam]

Im just doin what the boss wants

Bosses are there to boss people around, let them do what they are good at (nothing) and let us developers develop....

Why does it make a difference doing this in a table rather than a query?

Let me ask you this question: What happens if one of the 3 fields are changed?

Right you have to change to total field as well... Now if you do in storing the value in the table... you get messy things. In a query, the totals are re-calculated everytime you run the query, thus not running into this problem.

Plus, it is one of the basic design rulez when designing any database. You 'normalize' this database, one of its main rulez: "Do not store calculated values"

 

[Mart19881988]

So i do what exactly then?
this access is confusing and i havent used it for a very long time, ill up your reputation as much as i can cos i realise im awkward lol

I could do wiv like a step by step guide or just easy to follow instructions

 

[namliam]

Step1
Create a query

Step 2
Add your table

Step 3
Add above formula (without the quotes) to your query.

Done.

Upping reputation is Optional, not a requirement.

 

[Mart19881988]

okay iv made a new query and added the fields, where do i write in the formula?
the design view is different from the table design view and it doesnt have a validation rule
is it criteria?

 

[namliam]

Nope not criteria...

In the fieldrow pick any old empty column add the formula there...
the syntax is: MyAlias: Formala

so in your case:
Money In: Cash+Cheque+Card

 

[Mart19881988]

I think id much rather not do this than keep inputting all this information, why is it so much more difficult than before?

iv done what you said but now i have another field at the end called "Money In"-which doesnt work and iv also got the "Expenses" field twice now only one is called "Expenses.Money In"

this is irritating, you want to do the whole thing for me?
i doubt i'll ever finish it

 

[namliam]

Your complete SQL would be something like:
Select Cash, Cheque,Card, Cash+Cheque+Card as [Money In]
From yourtable

I dont have A2007, so I cannot be 100%, but that is how it works in 2003 and previous versions.

 

[Mart19881988]

I entered Cash+Cheque+Card into the validation rule in the table in the "Money In" field but it didnt work.
I also put as "Money In" at the end after that but that didnt work either

im irritated that iv spent 4 hours doing this now an got nowhere!!!
why cant it be simple?

 

[namliam]

because it is access....

Try putting it as default value...

Again storing calculated information should not be done!

 

[Mart19881988]

How do i put it as default value?

maybe u should do this lol an let me import it?
would have saved me 4 hours and u the time it takes to keep replying and solving the problems

 

[Rabbie]

How do i put it as default value?

maybe u should do this lol an let me import it?
would have saved me 4 hours and u the time it takes to keep replying and solving the problems

Why don't you try switching your brain on and try to do your job.

Namliam has given you a lot of good advice so use it.

 

[Mart19881988]

How about you say something useful and engage your brain before you type
I don't suppose you have 2007 and so wont have these problems

and yes it was good advice but it didnt work, im grateful to namliam for the help, you're not helping on the other hand so go do something useful like jump off a bridge

 

[namliam]

How do i put it as default value?

maybe u should do this lol an let me import it?
would have saved me 4 hours and u the time it takes to keep replying and solving the problems

Gentlemen gentlemen come on, lets not start calling names or start throwing people of a bridge.

Unfortunatly I dont have A2007, so I cannot give you 1:1 100% proof what you must do or must not do. I am sorry, we seem to be stuck.

 

[Rabbie]

How about you say something useful and engage your brain before you type
I don't suppose you have 2007 and so wont have these problems

and yes it was good advice but it didnt work, im grateful to namliam for the help, you're not helping on the other hand so go do something useful like jump off a bridge

All you need to do is to use the formula you were given in a update query and use that query to update the total field in your table.

As Namliam says you shouldn't really be storing a calculated field in a table but if your boss insists

Access help is pretty good so if you don't understand about Update queries then look there first.

And Yes I will jump off a bridge but only if you are standing below it so I can land on you

 

[Mart19881988]

Yh ill be under the bridge
only to hit you wiv a stick tho when you land in the canal and hold your head under the water.

Thanx anyway namliam
i spent so long doing this i dont have to do the invoices
i might just use the old Access and then import it when i get the chance, thatl work right?

 

[namliam]

That should work yes

 

[Mart19881988]

Yh
it shouldnt come to this but i suppose it will work

i think its clear that Rabbie won the stupid match by scoring 10 own goals

thanx a lot, iv upped your reputation as you did help me anyway
good to see theres still some nice people round x