8. Working with a Table

8.1 Editing a Table

Create a new table

Add field | Add Record | Delete field | Delete Record | Edit

Edit an existing table

Create a new table from a query on a database

8. Working with a Table

Sometimes we need to work with a table only. ArcView lets you add existing tables, create new tables, and edit tables. Besides, you can also export the table you have worked on to dBase file, text file, and Arc/Info INFO. (INFO is the subsystem of Arc/Info that is specifically designed to work attribute data.) Furthermore, ArcView allows you to connect a database (e.g., MS-Access mdb file) indirectly to create a table using a SQL query.

8.1 Editing tables

Create a new table

To create a new table

Click New button in the Project window while the Table icon is highlighted. New Table window lets you name the file.

It will automatically lead you to the editing mode. There is neither a field nor a record in the table. Now we are going to add field, say id, name, address, and birthday.

To add a field

Choose Add Field… from the Edit menu in a table.

When you add a field to a table a dialog appears that asks you to specify the new field's name, type and length. You choose from four different field types:

° Number. You can specify the width of the field in digits in the Width field, and the number of decimal places in the Decimal Places field. Specifying 0 decimal places will make the field an integer. Note that the width includes a count for the decimal point and the negative sign (if applicable).

° String. You can specify the width of the field in characters in the Width field

° Boolean. Each value in a Boolean field is either True or False. When you type values into a Boolean field you can either type True or False in full, or just type t or f which ArcView will immediately convert to True or False for you.

° Date. Each value in a date field is eight digits in the format YYYYMMDD. For example, the 10th of February 1972 would be represented as 19720210. When you type dates into a date field they must be in this format.

To delete a field

You may want to delete a field in case you add a wrong field. Click the field bar you wish to delete and choose Delete Field from the Edit menu. This menu allows you to delete a field one by one.

To add a record

Choose Add Record from the Edit menu. It lets you add one record. You can add as many records as you want by choosing Add Record menu.

To edit the value one by one

Select the Edit tool . (The Select tool lets you select records while the Edit tool lets you input value into the record.) Place the cursor in the field of the record you want to edit. Type the new value into the field. (Enter key is used to commit the edit to each cell; if you don’t enter Enter key after typing the value in the cell, the value won’t be saved.)

To delete record

Select the record you wish to delete with the Select tool and choose Delete Record from the Edit menu.

To finish editing and save your changes, choose Stop Editing from the Table menu.

Edit an existing table

To add a table into a project

Use Add button in the Project window to open a dBASE, INFO or delimited text file as a table in your project. DBase file only will be editable. Other files can be editable also by exporting them into dBase file by choosing Export from the File menu. Even if the data is dBaes file, it is possible that Start Editing from Table menu is not enabled. It is because the data has read only property (e.g., in CD). In that case, copy into the local drive and remove the read only property.

The table added contains the information such as population and area. We will calculate the population density from them, and enter this value into the new field.

Add a new field; set a field name to popdnsty, type to number, width to 16, and decimal places to 5. Click OK.

Make sure the new field is active. (a field bar highlights)

To calculate a field value

Select the Calculate button ; In the Field Calculator dialog that appears, type the expression for the calculation in the input area of the dialog box. You can use the field and request list items to help you form the expression by double-clicking on field names and requests or you can type the expression yourself; Press OK to perform the operation.

ArcView displays the results in the table. The calculation applies to the selected set of records. If no records are selected, the calculation applies to all records. Choose the Stop Editing from the Table menu to save the edit.

Now we want to calculate a new field named Poplevel according to the value of other field. Add new field as follows: poplevel, string, 10

Click the field of popdensity to make it active. Choose the Statistics from the Field menu. Statistics dialog box shows the statistics of the active field. Now we know how the values are distributed.

Select the Query Builder button . Select records whose popdensity value is less than 2500.

Make sure the field [poplevel] is active. (Calculate operation applies to the active field.) Select the Calculate button . Set the value of a string field named [poplevel] to the string “low”. String has to be enclosed in double quotes in the text input area of the dialog box.

Press OK. The selected records will be populated with the value you just typed in the Field Calculator dialog box. Similarly you can populate other records by using query builder and calculator. That way, we have derived the value from the existing attributes (from numeric value – population density to ranked value – low, medium, high).

Using the Calculator, we can manipulate the value and fill the new field with the newly derived value. For example, you can concatenate the values stored in two fields, [First Name] and [Last Name], and calculate the resulting string to a third field named [Full Name]. Make [Full Name] the active field and type the following into the text input area of the dialog box:

[First Name] ++ [Last Name]

The ++ request concatenates the two strings and inserts a single space between them. Another calculation you may want to perform in a table is to replace one string in a field with another; for example, to change the name of all stores in a field named [Store Name] that are named "First" to "Acme" type the following in the text input area of the dialog box:

[Store Name].Substitute("First","Acme")

This expression examines all values and replaces any string that matches the first parameter with the second parameter's string.

If you want to join tables based on values that are stored as numbers in one table and as strings in the other, you need to convert one of the join fields so that the data types are the same. To convert a string field to a number field, add a new number field and calculate it using the following expression:

[zip_code].AsNumber

To convert a number field to a string field, add a new string field and calculate it with this expression:

[zip_code].AsString

Beside, you can also parse the value and calculate the resulting strings to the new field. For example,

[geo_id].Right(11)

Create a new table from a query on a database

Use the SQL Connect option in the Project menu.

Press Query button, then Output Table will pop up. This table is virtually displayed. So you may want to save it as the actual table by choosing Export from the Field menu.

Control a table display

¨ Hide a specific field

a. Make a table active

b. Choose Table Properties menu

c. Uncheck “invisible” in the Table properties dialog box

d. Click OK

¨ Display field names as different names

a. Make a table active

b. Choose Table Properties menu

c. Type in the new name in the Alias in the Table properties dialog box

d. Click OK

It only affects the display in the table.