|
Tables are the foundation of Microsoft Access databases
because they store the data used by all other Access objects. The
organization of the tables in a database determines how efficient the
database is and how easy it is to access the information in the database.
Before you create a database, you should consider the information that the
database will contain and how the tables in the database will organize
that information.
After you’ve planned the tables that you want to
include in your database, you can create them using the
Table Wizard,
which walks you through making a new table, or you can make a more
customized table on your own without the help of the wizard. In this
lesson, you’ll learn how to create tables and then use Design view to
modify them by creating new fields, rearranging existing fields, and
deleting unwanted fields. You’ll also learn how to add new records, change
the information in records, and print a table after you modify it.
You
will create all the practice files used in this lesson.
Creating a
database requires some preparatory work. Before you even start Access, you
should plan how the database will be organized and what tables you will
need to create in the database. At a minimum, you should:
■
Decide what information
you want to store in the database and determine the most logical way to
arrange the tables. Typically, you create a table for each general
category of information to avoid putting the same information in more than
one table.
■
Gather all the
information that will become part of the database, such as other computer
documents, spreadsheets, files, pieces of paper, ledger books, and so on.
■
Decide what information
you want to store in each table. The different types of information will
become the different fields for your table. For example, in a table
listing reservation information for Adventure Works, you might want to
include information such as the reservation number, the guest
identification number, the check-in and check-out dates, the number of
guests, the suite that they are staying in, and whether they are eligible
for a group discount.
■
Interview the people who
will use the database. Let them know what tables you plan to create and
the fields you plan to put in the tables. The users might have
suggestions, such as particular categories of information that they would
find useful.
Using these techniques to plan your database will help you
figure out the purpose of the database and whether you can use a database
template
to create it.
Access has several database templates, which are ready-made databases
containing all the formatting required to build a special-use database.
For example, if you need a database to track time and billing, you can use
the Access database template with features specific to time and billing.
Access has database templates for asset tracking, contact management,
event management, expenses, inventory control, ledgers, order entry,
resource scheduling, service call management, and time and billing.
Be sure to start Access before beginning this exercise.
Create a database without using a wizard
In this exercise, you will create a new database named
Adventure Works 02.
With Access open, if the New File task pane is not
visible, click the New button on the Database toolbar.
The New File task pane appears on the right side of the
window.
In the New File task pane, click the Blank Database option
in the New section of the task pane.
The File New Database dialog box appears.
Click the Save In down arrow, click the icon for your hard
disk, and double-click the Access Practice folder.
Access will save the new database in the Access Practice
folder.
In the File Name box, delete the existing text, and type
Adventure Works 02.
Click the Create button.
Access saves the Adventure Works 02 database in the Access
Practice folder. The Database window for the Adventure Works 02
database appears with Tables already selected on the Objects bar. You have just created a new database, but it does not yet contain any objects. You will now create a table
called tblSuppliers, which will contain information about the
companies that supply materials and services to the Adventure Works resort.
In the Database window, double-click Create Table By Using
Wizard.
The first Table Wizard dialog box appears. In this dialog
box you choose a category of tables, select one or more tables from the
list, and select fields to be included for the new table from each selected
sample table.
In the Sample Tables list, scroll down, and click
Suppliers.
The possible fields for the Suppliers table appear in the Sample Field list, with the SupplierID field already selected.
Click the > (Add) button.
The SupplierID field moves to the Fields In My New Table
list, and Access selects the SupplierName field, the next field in the list.
Click the > (Add) button.
The SupplierName field moves to the Fields In My New Table
list.
Click the Address field, and click the > (Add) button.
The Address field moves to the Fields In My New Table
list, and Access selects the City field.
Click the > (Add) button.
The City field moves to the Fields In My New Table list,
and Access selects the Postal Code field.
Repeat step 6 to add the PostalCode and StateOrProvince
fields to the Fields In My New Table list.
All fields have now been added for this table.
Click the Next button.
The next Table Wizard dialog box appears.
In the What Do You Want To Name Your Table? dialog box,
delete the existing text, type tblSuppliers, and then verify that
the Yes, Set A Primary Key For Me option is selected.
Click Next.
The next Table Wizard dialog box appears. This dialog box
lets you choose between opening the table in Design mode or in
Datasheet mode, where you can begin entering data records.
Verify that the Enter Data Directly Into The Table option
is selected and the Display Help On Working With The Table check box is
cleared.
Setting these options will cause the table to open in
Datasheet view when you click the Finish button, and the Access Help
system will not open automatically.
Click Finish.
The new table, tblSuppliers, appears in Datasheet view.
On the Table Datasheet toolbar, click the View button.
The table appears in Design view. The field names that
appeared from left to right in Datasheet view now appear from top to
bottom in the Field Name column.
Close the tblSuppliers table, and keep the database open
for the next exercise.
Although the Table Wizard
provides a simple way to create a table, you might need to create a table
from scratch if the wizard does not provide the fields that you need. For
example, to create a table that keeps track of the suites at Adventure
Works, you need to build the table from scratch because there isn’t a
template in the Table Wizard that contains the fields that you will need,
such as the type of suite or the suite price. When you create a table from
scratch, you determine the field names.
You previously created
a table for your supplies using an Access wizard, and that table is now
visible in the Tables list in the Database window. You now need to create
a table named tblSuites to keep track of the rooms at the resort. You will
create this table from scratch using Access Design view.
In the Database
window, double-click Create Table In Design View.
A blank table appears
in Design view, with the insertion point already in the first cell in the
Field Name column.
Type SuiteID into the
Field Name cell.
On the Table Design toolbar, click the Primary Key
button.
A primary key symbol
appears to the left of the SuiteID row, designating SuiteID as the primary
key field. Text appears as the default data type.
In the Data Type
column, click in the first cell, click the down arrow that appears, and
then click AutoNumber.
The numbers in the
SuiteID field will be generated by Access. AutoNumber is a serial counter
that will add 1 to the last key value currently in the table when a new
record is created, and assign the new value as the key of the new record.
In the Field
Name column, click in the next blank cell, type
SuiteType,
and then press Tab.
Access selects the next
blank cell in the Data Type column, a down arrow appears, and Text appears
as the default data type. Text is the correct data type for this field.
In the Field
Name column, click in the next blank cell, type
SuiteRate,
and then press Tab.
Access selects the next
blank cell in the Data Type column, a down arrow appears, and Text appears
as the default data type. You will change this data type.
Click the down arrow,
and click Currency.
Access will format data
entered into the SuiteRate field as currency, with a dollar sign and two
decimal places.
On the Table Design
toolbar, click the Save button.
The Save As dialog box
appears.
Type tblSuites
into the text
box, and click OK.
Access saves the new
table as tblSuites.
On the Table Design
toolbar, click the View button.
The table appears in
Datasheet view. Note that in the blank record, the SuiteRate field is
already formatted as a currency value. That is because you applied the
Currency data type to it in Design view.
After creating tblSuites, which keeps track of the
guest suites at Adventure Works, you decide to add three new fields
to the table: a Building field (which will designate the name of the
building that the suite is in), a #ofOccupants field (which will
specify how many people can stay in the suite), and a Notes field
(which will hold notes about the suites, such as repairs that need
to be made). The process for adding fields to an existing table is
similar to the process for adding fields to a new table, which you
did in the previous exercise.
You just created
the tblSuites table in Design view, and defined three fields for the
table. You now realize that you need to add additional fields. You
will again use Design view to add the three new fields to the
existing table.
On the Table
Datasheet toolbar, click the View button.
tblSuites appears
in Design view.
In the Field
Name column, click in the first blank cell, type Building, and press
Tab.
Access selects
the next blank cell in the Data Type column, a down arrow appears,
and Text appears as the default data type. Text is the correct data
type for this field.
In the Field Name
column, click in the next blank cell, type
#ofOccupants,
and press Tab.
Access selects
the next blank cell in the Data Type column, a down arrow appears,
and Text appears as the default data type. Text is not the correct
type for this field so you will change it.
Click the down
arrow, and click Number.
The #ofOccupants
field now has the data type of Number.
In the Field Name
column, click in the next blank cell, type
Notes,
and press Tab.
Access selects
the next blank cell in the Data Type column, a down arrow appears,
and Text appears as the default data type. Text could work for this
field, but there is a better data type to use so you will also
change this type.
Click the down
arrow, and click Memo.
The Notes field
has the data type of Memo.
On the Table
Design toolbar, click the Save button.
Access saves the
changes you have made to the tblSuites table.
On the Table
Design toolbar, click the View button.
The table appears
in Datasheet view with the field names SuiteID, SuiteType, SuiteRate,
Building, #ofOccupants, and Notes. There is no data in the table
yet.
So far in this lesson, you created a database, two
tables for that database, and the fields for those tables. Now you
need to put some data in the tables that you created. Data is
entered into a table in Datasheet view by clicking in the cell where
you want the data to go and typing the data into that cell.
To
edit existing data in a table, you click in the cell that you want
to change, use traditional editing keys such as the Backspace key to
delete text to the left of the insertion point and the Delete key to
delete text to the right of the insertion point, and then type the
new data. You can also select part or all of the entry with the
mouse pointer and start typing, which deletes the selected data and
replaces it with the new data.
In the previous
exercises, you have created a new table called tblSuites and added
several fields to the table. In this exercise, you will add three
records to the tblSuites table, and then you will modify the data in
one of these records.
In the SuiteType
field, click in the first empty record, type
2BR,
and then press Tab.
Access selects
the SuiteRate field in the first record.
Notice that the
AutoNumber data type in the SuiteID field automatically gives the
first record the Suite ID of 1.
Type
75,
and press Tab.
Access selects
the Building field in the first record when you press the Tab key.
Notice that Access
automatically formats the data in the SuiteRate field in currency
format at $75.00. This is because you defined the field with a
Currency data type.
Type
Lake View,
and press Tab.
Access selects
the #ofOccupants field in the first record.
Type
4,
and press Tab.
Access selects
the Notes field in the first record.
Type
Corner Room.
The
first record is complete.
In the SuiteType
field, click in the first blank record, and follow the previous
steps to enter the following information into the record:
SuiteType:
3BR
SuiteRate:
99
Building:
Lake View
#ofOccupants:
6
Leave the
Notes field blank.
In the SuiteType field, click in the first
blank record, and follow the previous steps to enter the following
information into the record:
SuiteType:
3BR
SuiteRate:
99
Building:
Mountain View
#ofOccupants:
8
Notes:
Has bunk
beds in one bedroom
Your datasheet should now contain three records. In
the next step, you will edit the data in one of the records.
In the first record, click in the SuiteType field, delete
the existing text, and then type 1BR, and then press Tab.
Access selects the text on the SuiteRate field.
Delete the existing
text, and then type
69.
In the first record, click in the #ofOccupants field, delete the existing
text, and then type
2.
After you
start using a table, you might realize that you included some unnecessary
fields in the table, or that data entry would be more efficient if the
fields were in a different order. Deleting fields, which appear as columns
in Datasheet view, is different from deleting individual records, which
appear as rows. If you delete a field, you lose all the data in the field
for every record in the database. You can delete or rearrange fields by
displaying the table in Design view and then deleting or rearranging the
rows. Each row in Design view represents one of the fields that appears as
a column in Datasheet view. So, if you delete a row in Design view, you
delete a field (or column) in Datasheet view. When you move and delete
rows in Design view, you use the row selector to select all the cells in
the row.
Now that
you have added data to your table and examined it in Datasheet and Print
Preview modes, you notice that you would like to change the appearance and
content of the table. In this exercise, you will change the order of the
fields in tblSuites and delete the #ofOccupants field.
On the Table
Datasheet toolbar, click the View button.
The table appears in
Design view.
In the Field Name column, click in the Building row.
The row selector
appears to the left of the word
Building.
Click the row
selector for the Building row to select the entire row.
Access selects the
Building row.
Drag the row selector up to just below the primary key symbol that is
to the left of the SuiteID row.
The Building row moves
below the SuiteID row.
In
the Field Name column, click in the #ofOccupants row.
The row selector
appears to the left of the word
#ofOccupants.
Click the row
selector for the #ofOccupants row to select the entire row.
Access selects the #ofOccupants
row.
On the Table
Design toolbar, click the Delete Rows button.
An alert box appears,
asking if you want to permanently delete the field(s). If you have
selected the wrong row, or have changed your mind about deleting the
field, you can click No and the field will remain in the table. In this
case, you are going to delete the field.
Click Yes.
Access deletes the
#ofOccupants row from the table.
On the Table Design
toolbar, click the Save button. Access saves the table.
On the Table Design
toolbar, click the View button. The table appears in
Datasheet view. Note that the Building field now displays after the Suite
ID field, and the #ofOccupants field is no longer present.
Deleting a record, an individual row of data containing information from
many fields, is a simple procedure that should be done carefully. Once a
record is deleted, it cannot be restored. Therefore, make sure that you
are removing the correct records. If you plan to delete several records,
it is a good idea to create a backup of the entire database in case you
accidentally delete the wrong records. Unlike fields, records are deleted
in Datasheet view.
After examining the data in your table, you decide that tblSuites should
be limited to records of suites only in the Lake View building. In this
exercise, you will delete the record for the suite in the Mountain View
building.
Click
anywhere in the record for the suite in the Mountain View building.
The record selector appears to the left of the record.
On the Table
Datasheet toolbar, click the Delete Record button. An alert box
appears, asking you if you want to delete the record(s). You can click No
to cancel the operation and retain the record if you have selected the
wrong record to be deleted
Click Yes to
confirm the delete and remove the record from the table.
Access deletes the
record.
Close tblSuites.
Close the Adventure Works 02 Database.
Close Access.
Okay here is
the twist. I need you to make sure you have exited Access, then I
need you to find where you saved, once you find and rename it Adeventure
works 02.joe yes .joe. it is a long story but Microsoft will not let
you email a mdb file so we have to fool it
Email your completed file as an attachment
to me. Good job well done.
|