Anthy 497G – GIS In Archaeology

Lab Exercise 10

Step 1: Create a new Access database
Start Microsoft Access
Create a new blank database called MIA_Survey.mdb

Step 2: Create a new table for Phase information
Make sure that the Tables object is selected and then double click on Create table in Design View.

Create a field for the Phase_ID (this will be the primary key for the table). Type Phase_ID for the Field Name, for Data Type select Text and change the Field Size to be 2 (we will arbitrarlyy ID the phases as 01, 02, etc.) and for Description type “Phase table arbitrary primary key”

Repeating the previous step add a field Phase_Name as a Text data type with field size 10 (currently the longest field name is Nativas with 7 characters), and a description “Archaeological Phase Name”.
Also add a field Start_Date, as a Numeric Long Integer type, clear the 0 from the default value and type >0 for the Validation Rule, give this field the description “Start date of phase in years B.P. – where present is used as 2000 A.D.”
Finally add a field End_Date, as a Numeric Long Integer type, clear the 0 from the default value and type >0 for the Validation Rule, give this field the description “End date of phase in years B.P. – where present is used as 2000 A.D.”

The final step is assigning the field Phase_ID to be the primary key field for the table. Highlight the Phase_ID field definition, then click the Primary Key icon from the toolbar. This will place a key icon next to the Phase_ID field definition.

Select File->Save As from the main menu and change the table name from Table 1 to Phases. Then close the table design. A new table called Phases has been added to the database.

Step 3: Data enter Phase information
Double click on the Phases table to open it up. Add the below data values to the table. Test out the table by occasionally trying to enter bad data into the table.

Try to enter Phase_ID values with more than 2 characters, Phase_Name values with more than 10 characters. Try to enter Start_Date or End_Date values that include text characters or that are negative numbers. Finally try to enter in two rows of data with the same Phase_ID value (e.g. 02 for both the Cruz II and Ramo phases). Notice that the database will not let you do any of these things. By applying appropriate constraint conditions and a primary key we have been able to ensure that our database will successfully reject certain obvious data entry errors.

Step 4: Create Ceramic Types data table
Using similar steps to those outlined in step 2 create a new table with the following fields:

Field Name Data Type Field Size Description
Type_ID Text 3 Ceramic type code
Type_Name Text 25 Ceramic type name
Phase Text 2 Archaeological phase code

Set the Type_ID field to be the primary key field for the table.
Save the table with the name Cer_Types

Step 5: Create Foreign Key Constraint
In step 4 we created a field called Phase in the Cer_Type data table. We set this up to be a text field with a field size of 2 because this field will store Phase_ID values that reference the phases we entered. We should not be able to enter a value into this field if no corresponding value exists in the Phase_ID field of the Phases data table. This type of relationship is a Foreign Key constraint.
To define a foreign key constraint in Access click the Relationships icon in the main Access toolbar (alternately you can select Relationships from the Tools menu). When the relationships window appears use either the Show Table tool, or select Show Table from the Relationships window to open the Show Table window. Select the Cer_Types table and then click the Add button, repeat this procedure to add the Phases table.

Once you have added the Cer_Types and the Phases tables, Click the Close button to close the Show Table window. The relationships window now contains windows for both the Cer_Types and Phases tables. The entry for each lists the fields in each table (notice that in both cases the Primary Key field is displayed in Bold face.

The Foreign Key relationship we want to set up links the Phase field in the Cer_Types table to the Phase_ID field in the Phases table. To make the relationship click and hold down the left mouse button on the Phase listing in the Cer_Types table, then still holding down the mouse button drag the mouse to the Phase_ID listing in the Phases table. Release the mouse button and the Edit Relationships window will be displayed. Click the check boxes next to the Enforce Referential Integrity, Cascase Update Related Fields, and Cascade Delete Related Records entries. Click the Create button to create the relationship.

Notice that there is now a line connecting these two fields. This indicates that the foreign key constraint has been created.

Click the Close icon to close the Relationships window and return to the Tables listing in Access, respond Yes when you are asked if you want to save the changes to the layout of the Relationships.

Step 6: Data enter Ceramic Types information
Open up the Cer_Type table to begin doing performing data entry. Looking our original flat file database the first line lists the ceramic type Red-on-Tan as dating to the Cruz I phase. For the data entry, enter in 001 as the Type_ID, Red-on-Tan for the Type_Name.
To test out the Foreign Key constraint we set in the previous step first try entering the non-existant phase code of 10 for the Phase. After you enter this value try to go to the second row in the table, Access gives you a message telling you that it can’t add the record because there isn’t a related record in the table Phases. By setting the Foreign Key constraint the database will ensure that only valid phase codes get entered into this field.
Click OK on the error message and return to the table entry. Change the phase value from 10 to the correct value of 01 (the code for the Cruz I phase in the Phases table).

Why did we enter the value of 01 in the Phase field? We entered 01 because if you look at the Phases table you will see that the Phase_ID for the Cruz I phase, is 01.

The next listed ceramic type entry in our original flat file is for Iglesia Polychrome dating to the Nativas phase. What phase code should we enter in for this row of data?

As you can see the only way to figure out what phase code to enter is by looking back at the Phases data table. This is a difficult, and potentially error prone methodology. It would be better if we could select the phase by it’s name and have the computer enter in the phase code for us.

Step 7: Set up a Combobox for Phase data entry
Fortunately for us Access provides a fairly easy procedure to allow us to select entries by their name and have Access look up the appropriate code value for us.
Close the Cer_Types table. Next highlight the Cer_Types table and click the Design icon to enter design mode.
Once you are in design mode, hightlight the row for the Phase field, then click the Lookup tab under the Field Properties portion of the window. Notice that the entries in the bottom portion of the screen change, and that the Display Control currently listed is a Text Box.

Highlight the Text Box entry and from the drop down list change the control to be a Combo Box. Notice that a new set of options is displayed.

Change the Combo Box options to be Row Source: Phases, Bound Column: 1, Column Count: 4, Column Widths: 0;1;0;0. Your screen should match the list below:

The reason we specified Phases as the Row Source is because the table Phases contains the list of all the potential phases we have to choose from.
The Bound Column is 1 because the first column in the Phases table is the Phase_ID field, this is the field that contains the value that we want to have stored in the Cer_Types data table.
The Column Count is 4 because the Phases table has 4 columns (Phase_ID, Phase_Name, Start_Date, and End_Date).
Column Widths is set to be 0;1;0;0 because we only want to see the data that is stored in the second column in our list (the Phase_Name column). Setting it to 0;1;0;0 means that the first column has a width of 0 (i.e. it’s invisible), the second column has a 1 (it is visible) and columns three and four also have 0 widths (so they are also invisible).
Exit from the Design view and respond Yes when it asks you if you wish to save your changes.

Step 8: Continue Ceramic Types data entry
Once again open up the Cer_Types table. You should currently only have one row of data (for the Red-on-Tan information).
Referring to the original flat file the second ceramic type listed is Iglesia Polychrome dating to the Nativas phase. In the second row of the Cer_Types table enter in 002 as the Type_Id, Iglesia Polychrome as the Type_Name, and notice now that when we get to the Phase column the table displays a drop down list of just the phase names. Select Nativas from the list.

What’s interesting is that although Access is showing us the list of phase names, behind the scenes it is actually storing the Phase_ID value in the Phase field. That’s to say when we select Nativas from the list, behind the scenes Access is entering a value of 05 in the data table.

Continue doing data entry to enter the rest of the ceramic types from the flat file. Notice that if a ceramic type is listed more than once we only need to enter in the first occurrence, after that any other rows would be redundant. Once you are finished your table should look like this:

Step 9: Create Sites and Ceramics Tables
Using the procedures outlined above create two more tables, one for Sites and one for Ceramics. Use the following field definitions for the tables:

Sites
Field Name Data Type Field Size Description
Site_ID Text 7 Unique Site Identifier
Site_Name Text 25 Common name for the site
Elev Number Long Integer Site elevation - meters ASL

For the Elevation field delete out the default value of 0 and set the Validation Rule to be >=0 And <=5500 so that the field will only accept values from 0 to 5500 meters ASL. Also set the field Site_ID to be the primary key for the table.

Ceramics
Field Name Data Type Field Size Description
Cer_ID AutoNumber Autonumber primary key
Type Text 3 Ceramic Type
Site_ID Text 7 Site ID

Set the Cer_ID field to be the Primary Key. For the Type field set the field properties to be a Combo Box, Row Source: Cer_Types, Bound Column: 1, Column Count: 3, Column Widths 0;1;0.

After creating the tables go into the Relationships view (as outlined in step 5). Use the Show Table tool to add the Sites and Ceramics tables to the window. Set up the relationships so that the field Type in the Ceramics table links to the Type_ID field in the Cer_Types table. Also link the Site_ID field in the Ceramics table to link to the field Site_ID in the Sites table. In both cases make sure that you checked the 3 check boxes in the Edit Relationships window.


Close the Relationships window and save the changes you made.

Step 10: Data Entry for Sites and Ceramics Tables
Open up the Sites and Ceramics tables and try entering the site and ceramic data from the first row of the original flat file. In the Sites table use Site_ID: MIA-001, Site_Name: El Arbol, Elev: 450. In the Ceramics table select Red-on-Tan as the ceramic type and set the Site_ID to be MIA-001, notice that you don’t have to do any data entry for the Cer_ID field because it is an autonumber field so the database automatically enters a numeric value for you.

We could enter all the rest of the data in these tables but there’s a fairly high probability that we would make some errors in linking the Ceramics entries to their appropriate Site_ID values.

Step 11: Create a Data Entry Form
Instead of having to enter in all the linking information ourselves Access provides us with a more efficient and easier way through the creation of data entry forms. As long as you use all the steps to set the appropriate control types, primary keys, and foreign key relationships, Microsoft’s wizards can be of great assistance.

On the database change the Objects to Forms

Double click the Create form by using wizard selection to bring up the form wizard.

Change the entry under Tables/Queries to be Sites. Notice that the list of available fields now changes to be the fields in the Sites table (Site_ID, Site_Name, and Elev).

Highlight the Site_ID field and then click the > button to move the entry from being under the Available Fields to the Selected Fields.

Repeat this procedure to move Site_Name and Elev to the Selected Fields list

Change the Tables/Queries entry to be Ceramics and only move the Type field to the Selected Fields list (leave the Cer_ID and Site_ID fields under the Available Fields list).

Finally select the Phases table and move the Phase_Name, Start_Date and End_Date fields to the Selected Fields list.

Click the Next > button to move to the next window. Through the next series of windows just leave the default selected values and click the Next > buttons to move through the windows. On the final window click the Finish button to complete the form design.

Along there way there were numerous ways we could have modified the look of this final form but this gives a very serviceable form with a minimum of effort. Notice that the form currently contains the information for the first site. Note how the site information is contained in the upper half of the form and the ceramic data is in the bottom half.

At the bottom of the window there are two Record selector bars:

On the bottom one of these two click the Right arrow icon to bring up a blank form where we can enter the information for the 2nd site.

Looking back at the original flat file the 2nd site is the Zotinco site at an elevation of 1230, but with no associated ceramic information. Enter in a site ID of MIA-002 and the name and elevation information data but leave the ceramic portion of the form empty.

Click the right arrow icon again to bring up a new form and this time enter Site_ID: MIA-003, Elev: 785 (leave the Name field blank because there was no name supplied in the original file). Next go to the first line in the Ceramics grid and select ceramic type Iglesia Polychrome from the list. Notice how the form automatically fills in the associated Phase information for us. This is part of the power of a good relational database design, the database management system is doing a lot of the work for us.

In the 2nd row of the ceramics grid select Hard Thin Gray as the ceramic type, again Access fills in the rest of the phase information for us.

Use the preceding techniques to enter the information for the remaining 3 sites (Guerrero, Rayal I, and Rayal II). When you are finished close the form (Access automatically saved your changes while you did the data entry.

Return to the Tables object and open up the Ceramics table. Notice how the data entry form automatically entered the linking site information for us. We entered the Site_ID only once on the form but it updated the values in both the Sites and the Ceramics table.

Once again the use of a relational database design makes the overall structure somewhat more complicated than a flat file but we have a greater amount of control and can use built in tools of the database management system to perform a variety of functions for us.

Close Ceramics table and exit Access to end this lab exercise.