"sum" syntax for four fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have four fields that are "number" type and have used the "sum" syntax to
add three of the fileds and place the "sum" in the fourth field. The syntax
is not working properly. Would someone provide the "syntax" for "sum" to add
three "fields" and place the "sum" in the fourth field (I would like the
fourth field to automatically "update" with the new sum for each record when
the three other fields are entered with data-numbers). Thank you. Roy A. Day
([email protected])
 
In your query builder, to get the sum of the other fields, you'd type use an
expression like:
ChooseAName: Nz([Field1],0)+Nz([Field2],0)+Nz([Field3],0)

HTH,
Brian
 
What would be the syntax for the "sum" in an "event" procedure after right
clicking the field in the design form?


Brian Bastl said:
In your query builder, to get the sum of the other fields, you'd type use an
expression like:
ChooseAName: Nz([Field1],0)+Nz([Field2],0)+Nz([Field3],0)

HTH,
Brian


ROY A. DAY said:
I have four fields that are "number" type and have used the "sum" syntax to
add three of the fileds and place the "sum" in the fourth field. The syntax
is not working properly. Would someone provide the "syntax" for "sum" to add
three "fields" and place the "sum" in the fourth field (I would like the
fourth field to automatically "update" with the new sum for each record when
the three other fields are entered with data-numbers). Thank you. Roy A. Day
([email protected])
Click to expand...
Click to expand...
 
I'll assume that you're not trying to write the value to the table, which in
most cases would be frowned upon. So the fourth "field" would be an unbound
text control. In its control source (with a default value of 0), just type:

=Nz([Field1],0)+Nz([Field2],0)+Nz([Field3],0)

If you wanted to do this with VBA, and for some valid reason you need to
store the value, then probably using the form's On Current procedure, you'd
use something like:

Me.4thControl = Nz(Me.1stControl,0) + Nz(Me.2ndControl,0) + ...

And then in the AfterUpdate of each of the first three controls, you'd need
to recalc the 4th control.

HTH,
Brian


ROY A. DAY said:
What would be the syntax for the "sum" in an "event" procedure after right
clicking the field in the design form?


Brian Bastl said:
In your query builder, to get the sum of the other fields, you'd type use an
expression like:
ChooseAName: Nz([Field1],0)+Nz([Field2],0)+Nz([Field3],0)

HTH,
Brian


ROY A. DAY said:
I have four fields that are "number" type and have used the "sum"
Click to expand...
syntax
to
add three of the fileds and place the "sum" in the fourth field. The syntax
is not working properly. Would someone provide the "syntax" for "sum"
Click to expand...
to
add
three "fields" and place the "sum" in the fourth field (I would like the
fourth field to automatically "update" with the new sum for each
Click to expand...
record
when
the three other fields are entered with data-numbers). Thank you. Roy
Click to expand...
A.
Day
([email protected])
Click to expand...
Click to expand...
Click to expand...
 
What would be the syntax to create a sum of three fields in one record in a
query table (NOTE: I prefer an SQL syntax, if you know SQL)? Subsequently,
what is the procedure to place the aforesaid query table "sum" as a field in
another table ? If I am not clear on the aforesaid statement, request further
information.

Brian Bastl said:
I'll assume that you're not trying to write the value to the table, which in
most cases would be frowned upon. So the fourth "field" would be an unbound
text control. In its control source (with a default value of 0), just type:

=Nz([Field1],0)+Nz([Field2],0)+Nz([Field3],0)

If you wanted to do this with VBA, and for some valid reason you need to
store the value, then probably using the form's On Current procedure, you'd
use something like:

Me.4thControl = Nz(Me.1stControl,0) + Nz(Me.2ndControl,0) + ...

And then in the AfterUpdate of each of the first three controls, you'd need
to recalc the 4th control.

HTH,
Brian


ROY A. DAY said:
What would be the syntax for the "sum" in an "event" procedure after right
clicking the field in the design form?


Brian Bastl said:
In your query builder, to get the sum of the other fields, you'd type use an
expression like:
ChooseAName: Nz([Field1],0)+Nz([Field2],0)+Nz([Field3],0)

HTH,
Brian


I have four fields that are "number" type and have used the "sum" syntax
to
add three of the fileds and place the "sum" in the fourth field. The
syntax
is not working properly. Would someone provide the "syntax" for "sum" to
add
three "fields" and place the "sum" in the fourth field (I would like the
fourth field to automatically "update" with the new sum for each record
when
the three other fields are entered with data-numbers). Thank you. Roy A.
Day
([email protected])
Click to expand...
Click to expand...
Click to expand...
 
What would be the syntax to create a sum of three fields in one record in a
query table (NOTE: I prefer an SQL syntax, if you know SQL)? Subsequently,
what is the procedure to place the aforesaid query table "sum" as a field in
another table ? If I am not clear on the aforesaid statement, request further
information.
Click to expand...

The NZ() function is a builtin Access function which converts Null to
Zero (or to an optional second argument, e.g. NZ([Foo], 13) will
return 13 if the field Foo is NULL).

SELECT A, B, C, NZ([A]) + NZ() + NZ([C])) AS SumOfABC;

is a SQL syntax. To update a table (which you really should NOT need
to do; storing derived data is generally a Bad Idea), you can use the
same expression in an Update query.

John W. Vinson[MVP]
 
Hi Roy,

I'm not sure how proficient you are with the query builder, so I'll assume
very little.

1. Create New Query in design view. A "Show Table" window will pop up.
Select the table which you'd like to query, and click OK.
2. At a minimum, select the table's primary key from the table, and drag and
drop it onto the first empty column. Pull down any other fields you'd like
to display.
3. In the Field line of the next blank column, you'd type something like:

Everything: Nz([Field1],0)+Nz([Field2],0)+Nz([Field3],0)

The SQL would look something like:

SELECT ThingID, Something, Anything, Nothing,
Nz([Something],0)+Nz([Anything],0)+Nz([Nothing],0) As Everything FROM
tblThings;

You'll need to replace Everything with something meaningful like TotalCost
or whatever, and you'll also need to change the table and field names to
reflect your actual names.

As to your last question about storing the calculation in a field in another
table: generally you don't. There are exceptions, however. Take a look at
this web page by one of the Access MVPs

http://allenbrowne.com/casu-14.html

HTH,
Brian


ROY A. DAY said:
What would be the syntax to create a sum of three fields in one record in a
query table (NOTE: I prefer an SQL syntax, if you know SQL)? Subsequently,
what is the procedure to place the aforesaid query table "sum" as a field in
another table ? If I am not clear on the aforesaid statement, request further
information.

Brian Bastl said:
I'll assume that you're not trying to write the value to the table, which in
most cases would be frowned upon. So the fourth "field" would be an unbound
text control. In its control source (with a default value of 0), just type:

=Nz([Field1],0)+Nz([Field2],0)+Nz([Field3],0)

If you wanted to do this with VBA, and for some valid reason you need to
store the value, then probably using the form's On Current procedure, you'd
use something like:

Me.4thControl = Nz(Me.1stControl,0) + Nz(Me.2ndControl,0) + ...

And then in the AfterUpdate of each of the first three controls, you'd need
to recalc the 4th control.

HTH,
Brian


ROY A. DAY said:
What would be the syntax for the "sum" in an "event" procedure after right
clicking the field in the design form?


:

In your query builder, to get the sum of the other fields, you'd
Click to expand...
type
use an
expression like:
ChooseAName: Nz([Field1],0)+Nz([Field2],0)+Nz([Field3],0)

HTH,
Brian


I have four fields that are "number" type and have used the "sum" syntax
to
add three of the fileds and place the "sum" in the fourth field. The
syntax
is not working properly. Would someone provide the "syntax" for
Click to expand...
"sum"
to
add
three "fields" and place the "sum" in the fourth field (I would
Click to expand...
like
the
fourth field to automatically "update" with the new sum for each record
when
the three other fields are entered with data-numbers). Thank you.
Click to expand...
Roy
A.
Day
([email protected])
Click to expand...
Click to expand...
Click to expand...
 
The post was very helpful! Thank you.

One last question: Are there any restrictions on using the C# language
(using Visual Studio 2005 and SQL SERVER 2005) on writing a program in which
"stored procedures" are used for the "saved sum" pertaining to the three
fields in one record and then posting the "saved sum" to a fourth field as
the "stored procedure"?

John Vinson said:
What would be the syntax to create a sum of three fields in one record in a
query table (NOTE: I prefer an SQL syntax, if you know SQL)? Subsequently,
what is the procedure to place the aforesaid query table "sum" as a field in
another table ? If I am not clear on the aforesaid statement, request further
information.
Click to expand...

The NZ() function is a builtin Access function which converts Null to
Zero (or to an optional second argument, e.g. NZ([Foo], 13) will
return 13 if the field Foo is NULL).

SELECT A, B, C, NZ([A]) + NZ() + NZ([C])) AS SumOfABC;

is a SQL syntax. To update a table (which you really should NOT need
to do; storing derived data is generally a Bad Idea), you can use the
same expression in an Update query.

John W. Vinson[MVP]
Click to expand...
 
The posts were very helpful! Thank you.

One last question: Are there any restrictions on using the C# language
(using Visual Studio 2005 and SQL SERVER 2005) on writing a program in which
"stored procedures" are used for the "saved sum" pertaining to the three
fields in one record and then posting the "saved sum" to a fourth field as
the "stored procedure"?


Brian Bastl said:
Hi Roy,

I'm not sure how proficient you are with the query builder, so I'll assume
very little.

1. Create New Query in design view. A "Show Table" window will pop up.
Select the table which you'd like to query, and click OK.
2. At a minimum, select the table's primary key from the table, and drag and
drop it onto the first empty column. Pull down any other fields you'd like
to display.
3. In the Field line of the next blank column, you'd type something like:

Everything: Nz([Field1],0)+Nz([Field2],0)+Nz([Field3],0)

The SQL would look something like:

SELECT ThingID, Something, Anything, Nothing,
Nz([Something],0)+Nz([Anything],0)+Nz([Nothing],0) As Everything FROM
tblThings;

You'll need to replace Everything with something meaningful like TotalCost
or whatever, and you'll also need to change the table and field names to
reflect your actual names.

As to your last question about storing the calculation in a field in another
table: generally you don't. There are exceptions, however. Take a look at
this web page by one of the Access MVPs

http://allenbrowne.com/casu-14.html

HTH,
Brian


ROY A. DAY said:
What would be the syntax to create a sum of three fields in one record in a
query table (NOTE: I prefer an SQL syntax, if you know SQL)? Subsequently,
what is the procedure to place the aforesaid query table "sum" as a field in
another table ? If I am not clear on the aforesaid statement, request further
information.

Brian Bastl said:
I'll assume that you're not trying to write the value to the table, which in
most cases would be frowned upon. So the fourth "field" would be an unbound
text control. In its control source (with a default value of 0), just type:

=Nz([Field1],0)+Nz([Field2],0)+Nz([Field3],0)

If you wanted to do this with VBA, and for some valid reason you need to
store the value, then probably using the form's On Current procedure, you'd
use something like:

Me.4thControl = Nz(Me.1stControl,0) + Nz(Me.2ndControl,0) + ...

And then in the AfterUpdate of each of the first three controls, you'd need
to recalc the 4th control.

HTH,
Brian


What would be the syntax for the "sum" in an "event" procedure after right
clicking the field in the design form?


:

In your query builder, to get the sum of the other fields, you'd type
use an
expression like:
ChooseAName: Nz([Field1],0)+Nz([Field2],0)+Nz([Field3],0)

HTH,
Brian


I have four fields that are "number" type and have used the "sum"
syntax
to
add three of the fileds and place the "sum" in the fourth field. The
syntax
is not working properly. Would someone provide the "syntax" for "sum"
to
add
three "fields" and place the "sum" in the fourth field (I would like
the
fourth field to automatically "update" with the new sum for each
record
when
the three other fields are entered with data-numbers). Thank you. Roy
A.
Day
([email protected])
Click to expand...
Click to expand...
Click to expand...
 
ROY said:
The post was very helpful! Thank you.

One last question: Are there any restrictions on using the C# language
(using Visual Studio 2005 and SQL SERVER 2005) on writing a program
in which "stored procedures" are used for the "saved sum" pertaining
to the three fields in one record and then posting the "saved sum" to
a fourth field as the "stored procedure"?
Click to expand...

Well it would be restricted to people who don't know what they're doing since
storing such a value is a bad idea and a waste of time.
 
Back
Top