help needed with function

[zookeeperbobbie]

Hi all, I am creating a form in access from a table.
The form has the following information:


Deduction type 1 Deduction amount 1
Deduction type 2 Deduction amount 2
Deduction Type 3 Deduction amount 3

It also is set up to be one record- per purchase order.
The deduction types are combo boxes with drop down lists which are working fine.

I have a another field called total deductions.
I entered the following formula:=Sum([Deduction 1 Amount]+[Deduction 2 amount]+[Deduction 3 Amount])

Its working perfect except its adding every single value togeather for every record. Its not adding just the 3 deduction amounts per record.
Can someone please give me some advice? I;m kinda new to accessbut a quick learner... Thanks!!!

 

[VicRauch]

Remove the Sum() from your formula. Sum() would be used in Excel, but works differently in Access.

 

[zookeeperbobbie]

Thanks!! Its working for the first record, But record 2 and so are have nothing is the total deduction fields... Any ideas what i;m doing wrong?

Thanks!!!

 

[zookeeperbobbie]

Never mind- i fiqured it out- Now here's another problem if you can help-
The total deduction amount is not being added into my table...

Thanks so much for your help!!!!

 

[VicRauch]

I will assume you have some deduction amounts that are blank. Sorry I did not include this in the first reply. You need to use the Nz() function. This function returns a zero "0" if you are dealing with a math problem, or a zero length string if dealing with string data. (Look it up in help to read all about it!)
So your formula would now look like this:

=Nz([Deduction 1 Amount])+Nz([Deduction 2 amount])+Nz([Deduction 3 Amount])

The blank field is a Null, and any time you add a Null, your answer will be a Null, therefore blank.

 

[zookeeperbobbie]

Perfect!!!! Thank you- Now any idea why its not adding that value to my table?


Thanks!!!

 

[VicRauch]

Generally it is not a good idea to keep totals in your tables. If they are something that can be calculated again, which in this case they can, then you would best be calculating them each time the total is needed.
But, if you must have the total in your table, we need to know what are you doing on your form to try to get this total into your table.

 

[zookeeperbobbie]

Hello again-
Well to sum it up i'm slowly trying to create a master database- which will track all of my vendors's problems and deductions.
This is still in its very early stages.
Part of this is creating the form which i;m going to use to enter the information for each of our purchase orders and any problems that occur. I was doing this via a spreadsheet in exell by each vendor and it was becomming a nightmare plus it was impossible to run reports..
Anyways
I want to be able to have all of this information stored as a reference and Eventually i want to be able to run reports by each vendor that will detail how many deductions they had during a certin time frame and what type of deductions they were.... Its hard to explain... and i;m sure i will eventually want to add even more.. Right now i have close to 40 spreadsheets with various information that i have to use to create pivot charts and lookup information.. I think by converting everything into access it will save me more time, and allow me to link more features and spreadsheets by vendor...

 

[VicRauch]

Sounds like a very ambitious project. There are some basics that it would be good for you to know before going too far into Access. Please read the very helpful information in this thread.