Access 2007: Database Fundamentals
Tables, Columns, and Rows
When you create a blank database, Access opens it in Datasheet view and includes an empty table (named Table1) ready for you to begin entering data. The table contains a default field called ID that contains a unique identifier (the primary key) for each record you add. In the training within this unit, you will see how to add records to a blank database and create the table structure. Following are some basic concepts:
- Each record in a table is subdivided into fields that store a piece of information; records are table rows.
- Each field in a table represents a column.
- Each table requires a field (called a primary key) that contains a unique value for each record. You can change the default name that Access assigned by double-clicking the cell and typing the new name (for example, Employee Number).
- There
are two ways of working with tables:
- Datasheet view is the default view that appears when you create or open a database, and it is where you enter table data.
- Design view is where you can define aspects of a table such as rules and formatting. You will be working with Datasheet view initially and will learn more about Design view later.
A field can have only one data type. The three basic types of data are text, numbers, and dates. Access automatically attempts to set the data type for a column based on the information you enter. For example, if you enter a date, the data type is set to Date/Time. Subsequently, if you try to enter a non-date value in a field within in that column, an error message appears telling you the value does not match the column's data type. Note you may find it necessary to tweak a table's data types to simplify data entry. More on this in "Table Settings."
To add data to your table:- Begin typing in the cell under Add New Field. Press [Tab] or [Enter] to add another new field. Each field you add is the beginning of a column within the table. All the fields together form a row. Tips about the ID field.
- Start a new row by clicking the blank cell next to (New).
- Change a column heading by double-clicking the current text and typing the new heading.
- Add data by clicking the blank cell beside (New) in the next row and repeat until all data has been input.
- When finished, click
[Microsoft
Office Button] and select "Save" from the pop-up menu. Type a name
for your table and click 
. - You can then close the database by selecting "Close Database" from the Office Button pop-up menu.
Access has a spell checker just like other Office applications.
However, there is a difference in that it only looks at text and memo fields
and skips numbers, dates, and everything else. To use the spell checker,
place your cursor in the field where you want to begin checking and then
click 
on
the Home tab
in the Records group.
Select the options to apply from the dialog box that appears.
To modify data in a table:
- Click the field to update.
- Make any necessary change by highlighting the existing text and typing the new text.
You can use Replace to do small-scale editing. To do this,
open the table to modify in Datasheet view. Select 
on
the Home tab in the Find group.
Fill in the Find What and Replace fields
with the appropriate information and then select the field to search (from Look
In) and any
desired Match and Search options. Click 
or 
or 
depending
on what you want to do.
To add, rearrange, or delete columns:
- Add a column by right-clicking a heading field and selecting "Insert Column" from the pop-up menu that appears. The new column will be added to the left of the currently-selected column.
- Delete a column by right-clicking its heading field and selecting "Delete Column" from the pop-up menu.
- Move a column to a different location by clicking the column header once, releasing the mouse button, and then dragging the column to its new location.
To add or delete rows:
- Add a new record (row) by clicking the first blank field under the last row in your table (or alternatively clicking anywhere in the column and selecting "New Record" from the pop-up menu that appears). Note you cannot insert a row between existing rows like in Excel.
- Delete a record (row) by right-clicking its
[Row
Selector] column and selecting "Delete Record" from the
pop-up menu.
To resize, hide, or freeze columns:
- Use any of these methods as appropriate for resizing:
- Resize a single column by clicking the column's right edge in the column heading row and dragging the the left to shrink the column or to the right to expand it.
- Resize a column to fit content by pointing to the column's right edge and double-clicking.
- Resize several adjacent columns by dragging the first column's header across the columns to select the ones you want, releasing the mouse button, and then dragging the right edge of the selected columns to the left or right.
- Resize a column to a specific width by right-clicking the column header and selecting the desired value.
- You can hide one or more columns to make viewing your table easier by limiting side-to-side scrolling. Hide a column by right-clicking the column header and selecting "Hide columns" from the pop-up menu that appears. Hide several columns at once by clicking the column header and dragging to select the columns to include. Unhide columns using the same method.
- You can freeze columns so they remain visible in Datasheet view at the left side of the table as you scroll to the right. Freeze a column by right-clicking the column header and selecting "Freeze Columns" from the pop-up menu. Freeze several columns at once by clicking the column header and dragging to select the columns to include. Unfreeze columns using the same method.
To add a new table:
- Choose the desired view (Datasheet or Design)
by right-clicking the table's tab and selecting "Design View" or by
selecting the Home tab
and clicking
[down
arrow] in the Views group
on the Ribbon. - Select the Create tab
and then click
[Table
button] or 
[Table
Design button] if you are a more advanced user and want to use Design view.
Remember you enter data in Datasheet view and create table rules and formatting in Design view.