Transcript IAGAP Access Database

IAGAP Access Database
A Tutorial
Databases
• There are several databases available from
the IAGAP Project.
– IAGAP_Fish_Data
– IAGAP_Fish_Data_Reviewed
– IAGAP_Fish_Data_Cross_Reference
– IAGAP_Fish_Data_Entry
– IAGAP_Fish_Data_final
– Predicted_segments_by_species
– Historic_Data_Not_Entered
Databases
– IAGAP_Fish_Data: original database- contains all data collected
initially. This includes samples and sampled species that were
thrown out by reviewers, as well as other possible mistakes. I
left it intact, just in case.
– IAGAP_Fish_Data_Reviewed: contains all data collected with
reviewers comments incorporated. This is the database that is
used by IRIS.
– IAGAP_Fish_Data_Cross_Reference: contains information
gathered from raw data (such as collector’s original sample
numbers, etc.) that allows one to track back to the original data
from the IAGAP database. It was not updated with the reviews,
so it reflects the original IAGAP_Fish_Data database more than
the other databases (may contain mistakes but is still extremely
useful)
Databases
– IAGAP_Fish_Data_Entry: this is the database that contains the
forms for data entry. It is the easiest way to look at individual
samples. Currently related to original IAGAP_Fish_Data
database. Therefore changes made will only be found in that
original database.
– IAGAP_Fish_Data_final: this is the database that was used for
the official IAGAP modeling process and was included in the final
IAGAP CD. It only contains the samples used in the modeling
process.
– Predicted_segments_by_species: this database contains
information about which segments are predicted to contain
which species. Unlike other IAGAP products that predict at the
individual species level, this allows for all species in a particular
segment, i.e. a community sample.
– Historic_Data_Not_Entered: this database simply lists the
sources of data that were not entered into any of the above
databases. The sources are located in a Iowa State University:
University Extension box and are labeled with the corresponding
number.
Access Basics: Tables
Tables:
• A table is a collection of data about a specific topic, such as
collections or species. Using a separate table for each topic means
that you store that data only once.
• Tables organize data into columns (called fields) and rows (called
records). For example, each field in a species table contains the
same type of information for every collection, such as the species’
name. Each record in that table contains all the information about
one species, such as the species' name, ID number, federal ranking,
and so on.
• Tables are related to one another using a common field found in the
tables so that Microsoft Access can bring together the data from the
tables for viewing, editing, or printing.
Example Species Table
A record:
in this case,
all the
information
about one
species
A field: in this case, common
name of all species
Example Species Table
SampleID is the common
field that relates the
Samples table to many
other tables
Species table is related to
Samples table through
the SampledSpecies table
Tables
• When you first open the database you will get to choose what objects you
•
would like to work with: tables, queries, forms, etc.
Just click on the object to highlight it (here Tables has been selected), then
double-click the table, query, etc. to look at the data in the table.
Tables
• The data in a table looks like this:
Tables
• Without changing any data, you can organize what you
are looking at in ascending
or descending order
for any field. This example used Species ID
Ascending
Descending
Tables
• Or you can click on the design button
field definitions
to look at the design of the table including
Tables
• Without changing any data, you can organize what you are
looking at by hiding fields. Go to Format: Unhide columns, and
uncheck fields you would want to hide
Tables
• You can also search for specific records in a table using the
find
search tool. Place the cursor somewhere in the field you wish to
search and click
. Enter in value and choose which part of
field it will search, then click find next
Tables
• You can export the table you are working to another database or
Excel. With the table open, Go to
File: export
• Or you can use the ‘OfficeLinks’ shortcut.
It allows you to
export data, simply and easily to either Microsoft Word, Microsoft
Excel, or merge the data with a Microsoft Word Document.
• You can export tables, queries and reports.
Access Basics: Queries
Queries:
You use queries to view, change, and analyze data in
different ways. There are several types of queries in
Microsoft Access.
• Select Query: A select query is the most common type of query. It
retrieves data from one or more tables and displays the results in a
datasheet. Select Queries do not make changes to the database
• Action Query: An action query is a query that makes changes to or moves
many records in just one operation. There are four types of action queries:
– Make-Table Queries A make-table query creates a new table from
all or part of the data in one or more tables. Make-table queries are
helpful for creating a table to export to other Microsoft Access
databases. This is the most useful action query for the IAGAP
databases.
– Delete Queries A delete query deletes a group of records from one or more
tables. Should rarely be used for IAGAP databases, if ever.
– Update Queries An update query makes global changes to a group of
records in one or more tables. Should rarely be used for IAGAP databases, if
ever.
– Append Queries An append query adds a group of records from one or more
tables to the end of one or more tables. Should rarely be used for IAGAP
databases, if ever.
Queries
• As I mentioned before with tables, as you look at the database view, you
•
can choose an object to work with: tables, queries, forms, etc.
Just click on the object to highlight it (here Queries has been selected),
then double-click a pre-existing query to look at the resulting data.
Select Query
Make Table Action
Query
Queries
• The power of queries, however, is that you can create them
yourself, pulling together data of interest to you.
• There are two ways of creating your queries, using the Query
Wizard or using the design view.
Select how you would like to create a query
Queries
• Using the Wizard, gives you choices of which fields to include.
• First chose a table (or query). It will give you a list of all the fields in
that table.
Queries
• Select the fields you want to include by clicking on the Add button
Add Fields
Queries
• Then choose another table and include addition fields until you have
chosen all the fields you wish to include
• Click the Next button
Queries
• You can choose to show all the fields (Detail). This is the most
common use of a query. Or to summarize the data (Summary)
• Click the Next button
Queries
• Give your query a name.
• Click the Finish button
Queries
• The new dataset will be displayed.
• You can modify this query by clicking the design
button.
Queries
• In the design view, you can hide a field so that it doesn’t show in
the final dataset. You can add addition tables. You can change the
type of query it is.
Change from Select
query to Make Table Query
Add Tables
Unclick to Hide
Queries
• In the design view, you can add, delete, sort and qualify fields. The original query
•
asked for all of the selected fields. But in the design view, you can specify specific
information. For example, only the records for a particular species or year range or
sort by a particular field or do all three.
When you have changed the query, click the Run button.
Queries
• The new dataset will be displayed.
Queries
• You can also make a query from the design view by clicking on
“Create query in Design View”
• Choose the Tables you wish to include by clicking the Add button,
click close when finished.
Queries
• To choose fields, highlight a field and drag it to one of the columns below.
• If the tables are related, they will automatically be joined in the query.
• Make sure to include in any query the fields that relate one table to another. In this
example, you need to include SpeciesID from tblSampleSpecies to get information
from both tblSamples and tblSpecies, because it relates the two tables together.
Joins indicating
the relationship
between tables
Drag field
Queries
• When you have changed the query, click the Run button.
Access Basics: Data Entry
using
IAGAP_Fish_Data_Entry
When you open the IAGAP_Fish_Entry
Database, this window will automatically open.
To look at individual samples, add sample or edit
Samples, click the Enter Sample Data button.
To look at individual samples
already entered, either use
Search Tool
Place cursor in any box
to type in new value such as
SampleID, StreamName,
Month,Day,Year, etc.
Add Source from List
( if you know the exact source)
Open Source List
to add new Source
or to see which source
to choose from
the Drop down List
Place cursor in box to edit or add information
Choose Gear Type
from Drop down List
Open Collectors List to add new
Collector or to add sample to
Collector’s list of samples
Helps you navigate between samples
Helps you navigate among collectors
Open Negative Data to add new
Negative data information
Type in the appropriate SampleID
and the species that were targeted but not found
Helps you navigate among records
To add species to a sample,
choose either the Drop
Down list for Scientific name
Or choose to add a species to a
sample using the Drop
Down list for Common name
ID Codes will automatically be
entered. If you know only an
ID code, you can choose a
species using the appropriate
drop down list as well
If abundance numbers are
available, place cursor in box
to type in value
To add additional species, click
button
When you are finished with
this sample, you can exit
the database by clicking button
OR
You can add another sample by clicking
icon
For Additional Help
• Tutorial Box.com Access 2000 Tutorial
– http://www.tutorialbox.com/tutors/off2000/access/index.htm
l
• A Quick Microsoft Access 2000 Tutorial
– http://www.cwnresearch.com/resources/databases/access/t
utorials/access2000/Access2000Tutorial.html
• Microsoft Access 2000 Icons
– http://www.ricehahn.com/access/access2k_icons.html