Can you set up a numerical range and sort/create a report?

[Bryan]

I have a large database that contains crop yield and soil fertility. Is there
a way to set up a numerical yield range and then get the avg. fertility
within each range? So a report would look something like the following:

Yield Range: Ave. Phosphorus
0-120 bu/ac Calculated Value
120-150 bu/ac " "
150-170 bu/ac " "
170-200 bu/ac " "
200> bu/ac " "

Thanks!
Bryan

 

[Steve Schapel]

Bryan,

I assume you have a field with numerical values for Yield? If so, you
can do this in a query, and then base your report on the query. In this
case, I would use the Switch function, like this...

Yield Range:
Switch([Yield]<120,"0-119",[Yield]<150,"120-149",[Yield]<170,"150-169",[Yield]<200,"170-199",True,">200")
& " bu/ac"

Make it a Totals Query (select Totals from the View menu in query
design), leave Group By in the Totals row of this Yield Range column,
and put Avg in the Totals row of the Fertility (or whatever it's called)
column.

 

[Bryan]

Yes, both of them are numberical fields. I am very new to access (2 days
ago!) so I have to ask you how to enter in a switch function. When I go into
design query, i selected "Yield" for the field. Then what would I need to
pick for Total, Sort, and where do I enter in the switch function? In the
Criteria?

Thank you for your help. I appreciate it.

Bryan,

I assume you have a field with numerical values for Yield? If so, you
can do this in a query, and then base your report on the query. In this
case, I would use the Switch function, like this...

Yield Range:
Switch([Yield]<120,"0-119",[Yield]<150,"120-149",[Yield]<170,"150-169",[Yield]<200,"170-199",True,">200")
& " bu/ac"

Make it a Totals Query (select Totals from the View menu in query
design), leave Group By in the Totals row of this Yield Range column,
and put Avg in the Totals row of the Fertility (or whatever it's called)
column.

--
Steve Schapel, Microsoft Access MVP

I have a large database that contains crop yield and soil fertility. Is there
a way to set up a numerical yield range and then get the avg. fertility
within each range? So a report would look something like the following:

Yield Range: Ave. Phosphorus
0-120 bu/ac Calculated Value
120-150 bu/ac " "
150-170 bu/ac " "
170-200 bu/ac " "
200> bu/ac " "

Thanks!
Bryan

 

[Bryan]

Awesome, I got it. Now the only thing is when I sort the ranges in ascending,
it puts the >200 on the top. Any ideas on how to get that down to the bottom
of the ranges?

Another related question, is there a way to have access automatically break
the Yield field into 5 (or whatever number) of ranges by equal distribution
instead of user defined?

Thanks again for the help. I didn't realize some of the capabilities!

Yes, both of them are numberical fields. I am very new to access (2 days
ago!) so I have to ask you how to enter in a switch function. When I go into
design query, i selected "Yield" for the field. Then what would I need to
pick for Total, Sort, and where do I enter in the switch function? In the
Criteria?

Thank you for your help. I appreciate it.

Bryan,

I assume you have a field with numerical values for Yield? If so, you
can do this in a query, and then base your report on the query. In this
case, I would use the Switch function, like this...

Yield Range:
Switch([Yield]<120,"0-119",[Yield]<150,"120-149",[Yield]<170,"150-169",[Yield]<200,"170-199",True,">200")
& " bu/ac"

Make it a Totals Query (select Totals from the View menu in query
design), leave Group By in the Totals row of this Yield Range column,
and put Avg in the Totals row of the Fertility (or whatever it's called)
column.

--
Steve Schapel, Microsoft Access MVP

I have a large database that contains crop yield and soil fertility. Is there
a way to set up a numerical yield range and then get the avg. fertility
within each range? So a report would look something like the following:

Yield Range: Ave. Phosphorus
0-120 bu/ac Calculated Value
120-150 bu/ac " "
150-170 bu/ac " "
170-200 bu/ac " "
200> bu/ac " "

Thanks!
Bryan

 

[Steve Schapel]

Bryan,

No, you would enter the entire expression I gave you before into the
Field row of the first column in the query design grid. This is in
place of the Yield field - do not put the Yield field in the query grid.
Note that the expression I gave you, including the Yield Range: at the
beginning, has probably been wrapped onto more than one line in your
newsreader, but in the query grid it is all suppoosed to be in one line.
The entry in the Totals row of the grid for this column should be
'Group By' and then the Avg goes in the Totals row of the other data field.

 

[John Vinson]

I have a large database that contains crop yield and soil fertility. Is there
a way to set up a numerical yield range and then get the avg. fertility
within each range? So a report would look something like the following:

Yield Range: Ave. Phosphorus
0-120 bu/ac Calculated Value
120-150 bu/ac " "
150-170 bu/ac " "
170-200 bu/ac " "
200> bu/ac " "

Thanks!
Bryan

I'm not sure I understand. Where is the "calculated value" coming
from? Are you trying to store five numbers, one for each range? or
generate a report averaging the value of P in your table based on the
yield, with those ranges?

John W. Vinson[MVP]

 

[Steve Schapel]

Bryan,

Anything's possible. But you have to be precise about what you
need. So how do we work out the ranges? Increments of 10? 20% of the
difference between the lowest and highest? You'll notice I also changed
your original ranges so you didn't have double-ups of the value at the
boundary between the ranges, e.g. if the reading is 120, which group
does it go in?

Change the ">200" in the expression to "200+" will that be ok?

 

[Bryan]

Steve-

I changed it to "200>" instead of ">200" and it worked perfectly.

Bascially what I have is a very large database of crop prodcution
information; everything from the different types of corn or soybeans planted
to fertility to yield and everything in between for farm fields (I am a crop
consultant/GIS data processor). All of the spatial interpolation and the
combining of all these differnt crop production attributes into one database
table is done with ArcView GIS software. So my table in access is linked to
my table in ArcView which obviously updates changes live.

So I am now in the process of trying to create queries to sort through this
data and produce some informative reports, which could aid producers in
becoming better managers by choosing their fertilizers and hybrids more
effectively.

A few questions:
1) Some of my numerical fields come into access with a lot of decimal places
that are not necessary. Since it is a linked database, access will not let me
limit the number of decimal places in the design view of the table. So now
when I create any reports or queries, I get a pH value, for example, that is
7.3267546574 instead of just 7.3, which is plenty. It just makes the reports
look sloppy. Is there any remedy? I do not think I can efficiently limit the
decimal places in ArcView.

2) Is there a way to set a "null" value for any fields that are "0"? For
example, when I combine a table (In GIS) from one field that was soil sampled
for phosphorus with one that was not, those "P" cells for the field that was
not soil sampled become 0's in access, which totally throw off my queries.

Thanks again for the help. I think once I get some of these things figured
out, I will have a pretty neat tool.

Bryan

 

[Steve Schapel]

1) Some of my numerical fields come into access with a lot of decimal places
that are not necessary. Since it is a linked database, access will not let me
limit the number of decimal places in the design view of the table. So now
when I create any reports or queries, I get a pH value, for example, that is
7.3267546574 instead of just 7.3, which is plenty. It just makes the reports
look sloppy. Is there any remedy? I do not think I can efficiently limit the
decimal places in ArcView.

There are a number of approaches that can be taken here, depending on
the specific requirements. You could use the Round() function.
However, in the example you gave, probably if you go to the properties
of the textbox in design view of the report, and set the Format property
to Fixed and the Decimal Places to 1, should do the trick.

2) Is there a way to set a "null" value for any fields that are "0"? For
example, when I combine a table (In GIS) from one field that was soil sampled
for phosphorus with one that was not, those "P" cells for the field that was
not soil sampled become 0's in access, which totally throw off my queries.

There will certainly be a way to handle it. However, I can't quite
grasp exactly what you mean. Can you give an example with some sample
data to illustrate what is happening?

 

[Bryan]

Steve-

Yep, that worked well for the decimal point adjustment.

The second question; I will try to explain a little better.

Basically in ArcView GIS I overlay all of the different attributes (Yield,
Soil Type, Fertility, Management Practices, Crop Type, etc.) and the sofware
divides the fields into 60 ft square cells, and populates those cells by
looking at the infomation that is layed on top of those cells. It is the only
way to combine non-uniform spatial data into one database for querying.

The next step is to combine multiple fields database's into one so it then
becomes one large anonomous databse that all producers can use to benefit
their operation. So what I then have is one large database with thousands and
thousands (500,000 +) records. When I combine these tables from multiple farm
fields, if one farm field's database does not have a phosphorus populated
field in it's database, access automatically assigns 0's for those cells. So
when I run a query to get the average phosphorus of the complete database,
those 0's throw off the average of what the actual is.

So I more less need to find a way for access to recognize a "0" and
automatically create a null value for those cells. There should never, in any
circumstance, be a "0" in any part of this database.

The only thought I had was in my queries to always have to say Soil P >0
blah blah blah.

Does that help? Thanks again for your help and tips!

Bryan

 

[Steve Schapel]

Bryan,

It sounds like the data is being imported into your Access tables with
the Default Value of the fields in question set to 0. If this is the
case, maybe yu can try removing the Default Value property setting in
the table design.

Onother approach may be to run an Update Query on the data to update all
applicable fields to Null if they are 0.

Otherwise, as you suggested, you will need to exclude them in the
queries by using criteria such as <>0