Sum Function

[Kimmer1]

My boss created an Access Database to track Cabinets Painted per Day, what
the Daily Goal is and the difference between the two. He has asked me to go
in and give him a total number of cabinets painted to date and the average
amount of cabinets painted in a day. When I go in and try to do
=Sum([Cabinets/Cylinder Cab's Painted]) and =Sum([Daily Goal]) and
=Avg([Cabinets/Cylinder Cab's Painted]) all 3 of them give me an error.

I have not had any problems setting up his other reports just this one. On
the other reports though I was able to put in a header and footer for
employees that allowed me to isolate data by employee and then calculate my
totals, I have tried to do that here but that hasn't worked either.

Any suggestions?

 

[Evi]

When you say they give you an error, do you mean that they say Error in the
text box, or that they give you the wrong answer?

Are there any records where there is nothing in any of those fields which
you want to sum ie a blank space rather than merely a 0? If yes, then that
is probably where your errors are coming from.
You may need to add functions like eg
CabsPainted:Val(NZ([YourNumberField],0))
in the query on which your report is based and sum those calculated fields
instead of the ones in your table.

Evi

 

[Kimmer1]

I was trying to put them in a more appropriate header but I have had a
problem with that. I go to view and sorting and grouping and try to add a
group header to put these calculations in, unfortunately the choices I have
for creating the group header break everything up by date and make the report
very long.

Unfortunately at this point I think I am going to have to go in and
completely redo the foundation table for this data and build in some
different information, change the titles of these categories and start from
scratch. We have 2 other tables of information that I have worked with and
have had no problems with setting up a group header and footer and putting in
my calculations and giving him the report he has needed.......... this one
just seems like it is too simplistic and because of how things are titled and
how things are set up it would probably take me a lot less time to just
redesign it for him

Are you attempting to display the results in a Page Header or Footer or a
more appropriate Header or Footer where you can actually perform aggregate
calculations?

--
Duane Hookom
Microsoft Access MVP

All it is saying when I run the report is Error# it isn't giving me an error
number

My boss created an Access Database to track Cabinets Painted per Day, what
the Daily Goal is and the difference between the two.
You did not say if this was accomplished successfully.

all 3 of them give me an error.
You did not say what the error was for the three calculations.

--
KARL DEWEY
Build a little - Test a little


:

My boss created an Access Database to track Cabinets Painted per Day, what
the Daily Goal is and the difference between the two. He has asked me to go
in and give him a total number of cabinets painted to date and the average
amount of cabinets painted in a day. When I go in and try to do
=Sum([Cabinets/Cylinder Cab's Painted]) and =Sum([Daily Goal]) and
=Avg([Cabinets/Cylinder Cab's Painted]) all 3 of them give me an error.

I have not had any problems setting up his other reports just this one. On
the other reports though I was able to put in a header and footer for
employees that allowed me to isolate data by employee and then calculate my
totals, I have tried to do that here but that hasn't worked either.

Any suggestions?