Summing Fields

[MJuric]

I'm sure this is a very simple operation but I just can't seem
to figure it out or find anything in here about it.
I have a table consisting of nothing more than a name and
several fields of numbers. I want to add some of these fields, I.E.
Field 2-8, adn fields 9-12. After these are added I would like to take
the sum and multiple the sum by a certain number. For some reason no
matter what I do I can't seem to get the things to add up.

Any help would be appreciated.

~Matt

 

[MJuric]

As I suspected this is pretty easy and what I've tried is supposed to
work I.E. in a query TotalAmount:[Field1]+[Field2]..... And it does if
I start a new data base from scratch and manually enter the numbers.
However If I use the database I have, which is imported from Excel
everything is blank. By all appearnaces I have done the same thing in
both tables, queries and forms. One works one doesn't.

Any Ideas?

~Matt

 

[May]

Try to use sum([Field1]+[Field2])

May
MCP in Access and SQL Server

 

[MJuric]

Try to use sum([Field1]+[Field2])

I have without luck. I think what the problem is is that the
default field value is 0. The imported info contains nothing "null" in
the data were a number does not exist. In teh one I created from
scratch the 0's were automatically inserted. Taking the table and
adding the 0's to the imported data gives me correct results.
Now teh problem is how do I get zero's into all of the blanks
or can I change something to allow it to calculate.

~Matt

 

[May]

create a query to update all the records first
Update tablename
set [fieldname]=0 where [fieldname] is null

May
MCP in Access and SQL Server

-----Original Message-----

Try to use sum([Field1]+[Field2])

I have without luck. I think what the problem is is that the
default field value is 0. The imported info contains nothing "null" in
the data were a number does not exist. In teh one I created from
scratch the 0's were automatically inserted. Taking the table and
adding the 0's to the imported data gives me correct results.
Now teh problem is how do I get zero's into all of the blanks
or can I change something to allow it to calculate.

~Matt

May
MCP in Access and SQL Server would
like to take

.

 

[SteveS]

Or you try:

SUM(NZ([Field1])+NZ([Field2]))

The function: NZ() (null to zero) will change a null to a
zero "on the fly".


Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

-----Original Message-----

Try to use sum([Field1]+[Field2])

I have without luck. I think what the problem is is that the
default field value is 0. The imported info contains nothing "null" in
the data were a number does not exist. In teh one I created from
scratch the 0's were automatically inserted. Taking the table and
adding the 0's to the imported data gives me correct results.
Now teh problem is how do I get zero's into all of the blanks
or can I change something to allow it to calculate.

~Matt

May
MCP in Access and SQL Server would
like to take

.

 

[MJuric]

create a query to update all the records first
Update tablename
set [fieldname]=0 where [fieldname] is null

I'll try that. Thanks!

~Matt

May
MCP in Access and SQL Server

-----Original Message-----

Try to use sum([Field1]+[Field2])

I have without luck. I think what the problem is is that the
default field value is 0. The imported info contains nothing "null" in
the data were a number does not exist. In teh one I created from
scratch the 0's were automatically inserted. Taking the table and
adding the 0's to the imported data gives me correct results.
Now teh problem is how do I get zero's into all of the blanks
or can I change something to allow it to calculate.

~Matt

May
MCP in Access and SQL Server
-----Original Message-----
I'm sure this is a very simple operation but I
just can't seem
to figure it out or find anything in here about it.
I have a table consisting of nothing more than a
name and
several fields of numbers. I want to add some of these
fields, I.E.
Field 2-8, adn fields 9-12. After these are added I would
like to take
the sum and multiple the sum by a certain number. For
some reason no
matter what I do I can't seem to get the things to add up.

Any help would be appreciated.

~Matt
.

.

 

[John Vinson]

I'm sure this is a very simple operation but I just can't seem
to figure it out or find anything in here about it.
I have a table consisting of nothing more than a name and
several fields of numbers. I want to add some of these fields, I.E.
Field 2-8, adn fields 9-12. After these are added I would like to take
the sum and multiple the sum by a certain number. For some reason no
matter what I do I can't seem to get the things to add up.

It sounds like you're using Access as if it were a spreadsheet. IT'S
NOT, and treating it like one will make your life a lot more difficult
than it needs to be!

What you can do in a Query (*not* in a Table) is put a "calculated
field" into the Query. You can do so by opening the New Query window,
selecting your table, and putting (for example)

TwoToEight: [Field2] + [Field3] + ... + [Field8]

in a vacant Field cell. If any of these fields are blank, the result
will also be blank; to get around this (and treat blank fields as
zeros) use

NZ([Field2]) + NZ([Field3]) + <etc.>

You can then sum across rows by turning the Query into a Totals query,
by clicking on the Greek Sigma icon (looks like a sideways W). Select
only the fields that you want to sum and change the default Group By
to Sum. If you want subtotals for each value of some other table
field, leave that field in and set its aggregate function to Group By.