Add two fields of a table together.

[Karl]

Hello,

I have a table that has grass % cover and tree % cover. Each of these fields
are expressed as a number rather than a percent. Is it possible to add an new
field to my table that adds the grass % cover and tree % cover together to
give me a total % cover value.

Thanks,

 

[Klatuu]

Yes, but that would not be a wise thing to do. In fact, neither of your %
cover fields should be fields in your table. They are all calculated values.
Calculated values should not be stored in tables. They should be calculated
when you want to present the data to a user in a form or report.
Calculated fields take up disc space, calculating them takes less time than
loading them, and sooner or later, they will become incorrect.

 

[fredg]

Hello,

I have a table that has grass % cover and tree % cover. Each of these fields
are expressed as a number rather than a percent. Is it possible to add an new
field to my table that adds the grass % cover and tree % cover together to
give me a total % cover value.

Thanks,

Why would you want to?
If you already have the grass cover and tree cover values, any time
you need the total value simply add the fields.

In a Query:
TotalCover:Nz([GrassCover],0)+ Nz([TreeCover],0)

On a Form, or in a report, use an unbound text control.
= Nz([GrassCover],0)+ Nz([TreeCover],0)

In any event, do not store calculated values.

 

[Karl]

Hi,
The reason for entering them in tables is I always have a very difficult
time calculating values in a query as I am poor at programming. What does the
nz in that code mean? Where do I enter that code in the query field? In the
field of the query or in the criteria? Whenever I try to build a code with
the query builder for a field it never works, i always get something that
says expr or something. Why is that?

Thanks, for your help.

Hello,

I have a table that has grass % cover and tree % cover. Each of these fields
are expressed as a number rather than a percent. Is it possible to add an new
field to my table that adds the grass % cover and tree % cover together to
give me a total % cover value.

Thanks,

Why would you want to?
If you already have the grass cover and tree cover values, any time
you need the total value simply add the fields.

In a Query:
TotalCover:Nz([GrassCover],0)+ Nz([TreeCover],0)

On a Form, or in a report, use an unbound text control.
= Nz([GrassCover],0)+ Nz([TreeCover],0)

In any event, do not store calculated values.

 

[fredg]

Hi,
The reason for entering them in tables is I always have a very difficult
time calculating values in a query as I am poor at programming. What does the
nz in that code mean? Where do I enter that code in the query field? In the
field of the query or in the criteria? Whenever I try to build a code with
the query builder for a field it never works, i always get something that
says expr or something. Why is that?

Thanks, for your help.

Hello,

I have a table that has grass % cover and tree % cover. Each of these fields
are expressed as a number rather than a percent. Is it possible to add an new
field to my table that adds the grass % cover and tree % cover together to
give me a total % cover value.

Thanks,

Why would you want to?
If you already have the grass cover and tree cover values, any time
you need the total value simply add the fields.

In a Query:
TotalCover:Nz([GrassCover],0)+ Nz([TreeCover],0)

On a Form, or in a report, use an unbound text control.
= Nz([GrassCover],0)+ Nz([TreeCover],0)

In any event, do not store calculated values.

In Access, a Value plus a Null Value = Null.
25 + Null = Null.
The Nz() converts the Null to a value (i.e. 0 if no other value is
written) so that 25 + 0 = 25.

I included it so that if either the grass cover or tree cover did not
have any value entered you would get the proper total result.

Look up the Nz function in VBA help.
Press Ctrl + G.
Type Nz and press F1.

If you are wanting to add the fields in a query, the above should be
added as a new column.

 

[David Yu]

Karl,

You can do this in a query. In a new column and the first row (labeled as
"field"), type:

Total_Cover%: nz([grass % cover ],0)+nz([tree % cover],0)

where [grass % cover ] and [tree % cover] are your field name. The query
results will give you a new field [Total_Cover%] that includes the total
percentage. As the other guys suggested, it's better to put the calculated
value in a query instead of in a table.

 

[Karl]

Thanks Dave,
Thats exactly what I was looking for. Similarly, some of my sites have more
than one sample plot for grasses, shrurbs, or trees so I was wondering what
code I could put in the new column if I wanted to find the Average % Cover of
the Grasses fields (Their field names are [% grass 2m], [% Grass 15m], [%
Grass 28 m])

Thanks in advance,
Karl

Karl,

You can do this in a query. In a new column and the first row (labeled as
"field"), type:

Total_Cover%: nz([grass % cover ],0)+nz([tree % cover],0)

where [grass % cover ] and [tree % cover] are your field name. The query
results will give you a new field [Total_Cover%] that includes the total
percentage. As the other guys suggested, it's better to put the calculated
value in a query instead of in a table.

Hello,

I have a table that has grass % cover and tree % cover. Each of these fields
are expressed as a number rather than a percent. Is it possible to add an new
field to my table that adds the grass % cover and tree % cover together to
give me a total % cover value.

Thanks,

 

[John W. Vinson]

Thanks Dave,
Thats exactly what I was looking for. Similarly, some of my sites have more
than one sample plot for grasses, shrurbs, or trees so I was wondering what
code I could put in the new column if I wanted to find the Average % Cover of
the Grasses fields (Their field names are [% grass 2m], [% Grass 15m], [%
Grass 28 m])

WUPS.

You're "committing spreadsheet upon a database", a misdemeanor punishable by
being lectured by normalization geeks like me. <g>

Storing data - a plot size, apparently - in a fieldname IS SIMPLY WRONG
design.

If you have a one (site) to many (plots) relationship, you should model it as
a one to many relationship, with a Sites table (with information pertaining to
the site as a whole) and a Plots table (with a SiteID as a link to the sites
table, the identity of the site, the size of the site, and other information
about the specific plot).

You may need *yet another* table; if each Plot has multiple vegetation types,
and each vegetation type may appear on many plots, you should consider tables
like

VegetationTypes
VegetationType <Text, Primary Key> <e.g. "Grass", "Forbs", "Shrubs",
"Trees", "Barren ground">

Coverage
PlotID <link to table of plots>
VegetationType
Area <or Percentage>

As noted elsethread, you would be prudent to change your field name
convention; Access lets you use blanks and % and other special characters in
fieldnames, but you're really better off using Form and Report labels to
display such text, and use pure alphanumeric fieldnames.


John W. Vinson [MVP]

 

[Karl]

Hi John,

I appreciate your concern and I agree with your statements. However, I was
not responsible for the design of this database. This is a vegetation
database from a Masters study of someone else and therefore I'm stuck within
its limitations as I don't have the time to redesign his database to simply
pull out a few tidbits of data. Therefore thats the reason I'm looking for a
simple query that will allow me to find the average of his % cover's of the 3
plots in each site.

Thanks Dave,
Thats exactly what I was looking for. Similarly, some of my sites have more
than one sample plot for grasses, shrurbs, or trees so I was wondering what
code I could put in the new column if I wanted to find the Average % Cover of
the Grasses fields (Their field names are [% grass 2m], [% Grass 15m], [%
Grass 28 m])

WUPS.

You're "committing spreadsheet upon a database", a misdemeanor punishable by
being lectured by normalization geeks like me. <g>

Storing data - a plot size, apparently - in a fieldname IS SIMPLY WRONG
design.

If you have a one (site) to many (plots) relationship, you should model it as
a one to many relationship, with a Sites table (with information pertaining to
the site as a whole) and a Plots table (with a SiteID as a link to the sites
table, the identity of the site, the size of the site, and other information
about the specific plot).

You may need *yet another* table; if each Plot has multiple vegetation types,
and each vegetation type may appear on many plots, you should consider tables
like

VegetationTypes
VegetationType <Text, Primary Key> <e.g. "Grass", "Forbs", "Shrubs",
"Trees", "Barren ground">

Coverage
PlotID <link to table of plots>
VegetationType
Area <or Percentage>

As noted elsethread, you would be prudent to change your field name
convention; Access lets you use blanks and % and other special characters in
fieldnames, but you're really better off using Form and Report labels to
display such text, and use pure alphanumeric fieldnames.


John W. Vinson [MVP]

 

[John W. Vinson]

Thats exactly what I was looking for. Similarly, some of my sites have more
than one sample plot for grasses, shrurbs, or trees so I was wondering what
code I could put in the new column if I wanted to find the Average % Cover of
the Grasses fields (Their field names are [% grass 2m], [% Grass 15m], [%
Grass 28 m])

Ok:

AverageCoverage: (NZ([% grass 2m]) + NZ([% Grass 15m]) + NZ([% Grass 28 m]))/3


John W. Vinson [MVP]

 

[Ben A.]

This discussion is proving very relative to a question of mine. To expound
upon what's already been decided, however, I would like to ask how one might
express the averaging expression in case of null values. As its established
now, it will add the three columns and divide by three. What if one is null?

My situation calls for the average of anywhere from 2-5 data entries. (Test
results in lbf, cm, etc). It will usually be a full sample of 5 specimen, but
I need Access to be able to calculate an average in any case. Thanks in
advance!

Thats exactly what I was looking for. Similarly, some of my sites have more
than one sample plot for grasses, shrurbs, or trees so I was wondering what
code I could put in the new column if I wanted to find the Average % Cover of
the Grasses fields (Their field names are [% grass 2m], [% Grass 15m], [%
Grass 28 m])

Ok:

AverageCoverage: (NZ([% grass 2m]) + NZ([% Grass 15m]) + NZ([% Grass 28 m]))/3


John W. Vinson [MVP]

 

[Jeff Boyce]

Adding (or averaging, or finding the Maximum, or ...) of a(n indeterminant)
number of columns sounds like something you'd do ... in a spreadsheet! Why
are you trying to do this in Access? That is, what do you believe Access
can do that you can't get done in Excel?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

This discussion is proving very relative to a question of mine. To expound
upon what's already been decided, however, I would like to ask how one
might
express the averaging expression in case of null values. As its
established
now, it will add the three columns and divide by three. What if one is
null?

My situation calls for the average of anywhere from 2-5 data entries.
(Test
results in lbf, cm, etc). It will usually be a full sample of 5 specimen,
but
I need Access to be able to calculate an average in any case. Thanks in
advance!

Thats exactly what I was looking for. Similarly, some of my sites have
more
than one sample plot for grasses, shrurbs, or trees so I was wondering
what
code I could put in the new column if I wanted to find the Average %
Cover of
the Grasses fields (Their field names are [% grass 2m], [% Grass 15m],
[%
Grass 28 m])

Ok:

AverageCoverage: (NZ([% grass 2m]) + NZ([% Grass 15m]) + NZ([% Grass 28
m]))/3


John W. Vinson [MVP]

 

[John Spencer]

You could use a VBA function (see below)
Copy the function to a VBA module and save it. Make sure the module has a
different name than the function.

In your query you would have a calculated field
Field: TheAverage: fRowAverage([Fielda],[FieldB],[FieldC],[FieldD],[FieldZ])

Public Function fRowAverage(ParamArray Values())
'John Spencer UMBC CHPDM
'Last Update: April 5, 2000
'Calculates the arithmetic average (mean) of a group of values passed to it.
'Sample call:
'myAvg = fRowAverage("1","TEST","2", "3",4,5,6,0) returns 3 (21/7)
'Ignores values that cannot be treated as numbers.
'
' Max of 29 arguments can be passed to a function in Access SQL

Dim i As Integer, intElementCount As Integer, dblSum As Double
intElementCount = 0
dblSum = 0

For i = LBound(Values) To UBound(Values)
If IsNumeric(Values(i)) Then 'Ignore Non-numeric values
dblSum = dblSum + Values(i)
intElementCount = intElementCount + 1
End If
Next i

If intElementCount > 0 Then 'At least one number in the group of values
fRowAverage = dblSum / intElementCount

Else 'No number in the group of values
fRowAverage = Null
End If

End Function

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

This discussion is proving very relative to a question of mine. To expound
upon what's already been decided, however, I would like to ask how one might
express the averaging expression in case of null values. As its established
now, it will add the three columns and divide by three. What if one is null?

My situation calls for the average of anywhere from 2-5 data entries. (Test
results in lbf, cm, etc). It will usually be a full sample of 5 specimen, but
I need Access to be able to calculate an average in any case. Thanks in
advance!

Thats exactly what I was looking for. Similarly, some of my sites have more
than one sample plot for grasses, shrurbs, or trees so I was wondering what
code I could put in the new column if I wanted to find the Average % Cover of
the Grasses fields (Their field names are [% grass 2m], [% Grass 15m], [%
Grass 28 m])

Ok:

AverageCoverage: (NZ([% grass 2m]) + NZ([% Grass 15m]) + NZ([% Grass 28 m]))/3


John W. Vinson [MVP]