General Question

coelacanth's avatar

How do I create a simple program that will pull information from a database, insert it into a few formulas, and spit out the answers?

Asked by coelacanth (222 points ) March 17th, 2012

My knowledge of programming is non-existent, which makes it hard to even know what to search for to learn how to do this! Help, por favor!

For example, consider the following data:
Quadrat, Species
1, ASTCAN
1, ASTPIL
2, ASTCAN
2, ASTPIL
2, ASTERI

ASTCAN has a Conservation Value of 10, ASTPIL is 0, AND ASTERI IS 5, but I don’t want to have to enter those every time I enter the data.

How can I create a program that will then spit out the average Conservation Value of the quadrats I entered, for example?

=(sum of Conservation Values of Species column)/# species
QUAD 1 = (10+0)/2
QUAD 2 = (10+0+5)/3

P.S. If you recognize these values and names, it is because a program called FQA, Floristic Quality Assessment, exists for this exact purpose. My intention is to be able to create my own database of information to use with a set of formulas that this program does not offer.

Observing members: 0 Composing members: 0

2 Answers

CWOTUS's avatar

Welcome to Fluther.

I have only the vaguest notion of the survey that you’re trying to accomplish, but I do understand data.

What will serve you very well for this is a Microsoft Access (or equivalent, or better) database.

The first thing that you’ll want is a table for Species. Let’s call it tbl_Species.

The table should consist of the following minimum fields:
Species_ID – Autonumber – Primary Key
Species – Text – Indexed, Unique (no duplicates)
ConsVal – Integer (apparently) – “Conservation Value”

Save the table design, and start to enter your species (in any order) and associated conservation values.

Now you have a table of unique species, identified by also unique “Species_ID” numbers (meaningless to you, but important to the database) and associated Conservation Values that can be used in later computation.

Make another table of Quadrats, called tbl_Quad, consisting of:
Quad_ID – Autonumber
Quadrat – Text (Can be a name or number)
[You may eventually want to add another table to the database for “Survey” to identify each new survey that you want to record, and that would enable you to add another field for “Survey” to the Quadrat table in order to help make each Quadrat unique. By that I mean you may do a survey for Boston – and even “surveys for Boston” may not be unique; you may want to do one in the spring, one in the fall, and additional ones in succeeding years, and you’ll obviously want to differentiate each from the other – and you do not want to have to set up separate tables or databases for each survey. But that’s getting into more complexity than I’m going to deal with in a single response here.]

With those tables built, you are now ready to create the final (minimum) table required, which we can call tbl_Survey. The first field will be another Autonumber / Primary Key field which we’ll call “Obs_ID” (for Observation ID).

Next you’ll create a field called “Quadrat”, which will be a “Lookup” value into tbl_Quads so that data entries in tbl_Survey will be restricted to Quadrats which have already been included in the proper Quadrat table.

Following that field you’ll have a “Species” table which pulls only species you have already entered into tbl_Species.

Save the table and enter some “observations” such as the ones you have listed above.

Now you can build a Query of the database.

The Query will link tbl_Survey with tbl_Species on the Species_ID field (common to both tables) and pull the related “Conservation Value” field from tbl_Species. See how that works? Since you have a unique ID in tbl_Species every value of “Species” in tbl_Survey corresponds to one and only one Species_ID in tbl_Species, and can bring over whatever data is related to that Species_ID. It never gets lost, misplaced, mistyped, forgotten, etc.

Your query can also store the math equation you want to store (any number of them, in fact). Different queries can perform in different ways. What you want, apparently, is a type of “Summary” query which will perform the averages you want to record, quadrat by quadrat.

That just gets you started. So far, this seems to be a simple enough exercise, which will only get more complex as your understanding improves and your needs increase.

LostInParadise's avatar

What @CWOTUS said is good advice. You are going to have to learn enough about databases to understand how to use a select statement. Using the suggested database tables and fields, the query would look something like:

Select sum(tbl_Species.ConsVal)
from tbl_Survey, tbl_Quadrat, tbl_species
where tbl_quadrat.Quadrat = tbl_survey.Quadrat and
tbl_species.species = tbl_quadrat.species

The basic idea is that the select clause tells what you want to look at, the from clause tells what tables are needed, and the where clause tells how to relate the tables and can also include any restrictions you want to include in the data looked at.

One problem with the above query is that each species gets counted once for each quadrat it appears in and, from my limited understanding of FQA, each species should be included only once. That would require a more advanced query operation, which I do not want to get into here, but which I would be glad to discuss in a PM

On the other hand, if you want to compute the FQA of each individual quadrat, this could be done by a simple modification of the above code:

Select quadrat.quadrat, sum(tbl_Species.ConsVal)
from tbl_Survey, tbl_Quadrat, tbl_species
where tbl_quadrat.Quadrat = tbl_survey.Quadrat and
tbl_species.species = tbl_quadrat.species
group by quadrat.quadrat

Answer this question

Login

or

Join

to answer.

This question is in the General Section. Responses must be helpful and on-topic.

Your answer will be saved while you login or join.

Have a question? Ask Fluther!

What do you know more about?
or
Knowledge Networking @ Fluther