Article Index
Microsoft Access Tutorial
Queries
Creating A Database
Tables
Creating Tables
Queries
Forms In Microsoft Access
Reports In Microsoft Access
Data Access Pages
Controlling The User's Input
Subdatasheets
Designing Forms
Exploring And Analyzing Data
Tables Looks
Design And Exploration
Forms Design Propertie
Controlling Data Output
Microsoft Access Controls
Design And Improvements
Importing And Exporting Data
Macros
Switchboards

You will spend most of your time thinking about the functionality of your database. Although tables mostly work behind the scenes, you need to design and tune them up carefully. You need a little more organization.

We have already created tables from Datasheet and Data View. What about using wizards, or importing data from other documents.

Creating A Database From Wizards:

The wizards in Microsoft Access allow you to use suggested, predefined fields for your table.

Of course, this doesn't mean that you have to exclusively use the wizards.

Start Microsoft Access and choose to create a Blank Access Database...

Save the database as Music Collection in C:Microsoft Access Exercises.

A Simple Table Using The Wizard:

In the Database Window, the Tables button should be pressed already; otherwise, click the Tables button.

Double-click Create Table By Using Wizard. (If you are using MS Access 97, from the Tables tab, click New and double-click Table Wizard). The Table Wizard starts.

There is an infinite number of flexible items you can choose from.

Click the Personal radio button (this doesn't mean that the database you are creating is for personal use, however).

In the Sample Tables list box, click Recordings.

To select or unselect a field as part of your table, you can use the four buttons in the middle of the wizard page. The first button (>) allows you to include the currently selected field in the Sample Fields. The second button (>>) allows you to include all fields from the Sample Fields list into your table. The third button (<) unselects the currently highlighted field in the Fields In My Table list. The fourth button (>>) deselects all fields in the Fields In My Table list, putting them back where they came from.

In the Sample Fields, double-click RecordingID.

After you make your choices, you can keep the names provided by Microsoft Access into your table, or you can rename any field to suit your needs; that's what the Rename Field ... button is used for.

Since we are creating a music collection database, we want to use a "cooler" name for the recordings.

In the Fields In My New Table list box, click RecordingID and click Rename Field... Type AlbumID to rename the field.

From the Sample Fields, double-click RecordingTitle and rename it to AlbumTitle.

From the Sample Fields, double-click RecordingArtistID and rename it simply Artist.

From the Sample Fields, double-click MusicCategoryID and rename it MusicCategory.

From the Sample Fields, double-click RecordingLabel, rename it to Label.

From the Sample Fields, double-click YearReleased, Format, NumberofTracks, and Notes.
Click Next.

Name your table tblMusicAlbums, and let the wizard set the Primary Key for you. Click Next

Read whatever is on the next dialog, but don't change the default second radio button set for you. Click Finish.

Switch to the Design View.

Set the captions of the AlbumID to Album ID, and that of the album title to Album Title.

Change the Data Type of both the Artist and MusicCategory fields to Text. The MusicCategory's caption will be Music Category.

Change the caption of the YearReleased to © Year.

Click the Save button on Microsoft Access toolbar to save your table, then close the tblMusicAlbums table.

Change the caption of the NumberOfTracks to Tracks.

Mixing Various Fields In The Wizard:

Microsoft Access is equipped with a flexible table wizard that allows you to create different kinds of tables, you can even mix fields from different categories.

From the Database Window, double-click the button to Create A Table By Using Wizard. (If you are using MS Access 97, from the Tables tab, click New and double-click Table Wizard).

When the table wizard comes up, click the Personal radio button .

From the Sample Tables, look for and click Categories.

The Categories sample table provides two fields you can select to include in a table.

In the Sample Fields for the Categories sample table, there are only two choices. Click the >> button to choose all of them.

Rename the CategoryID to MusicCategoryID. Then, rename the CategoryName to MusicCategory.

You can include any of the sample fields available from other categories.

To add a new field to our table, click the Business radio button then scroll down in the Sample Fields until you find the Notes field and double-click it. Then, click Next.

Name the table tblMusicCategories and let the wizard set the Primary Key. Click Next twice.

When you have created a table using a wizard, you can still switch straight to the design view to do more configuration.

In the last page of this wizard, click the first radio button to Modify The Table Design. Then, click Finish.

You are switched to the Design View.

Set the MusicCategoryID's caption to Music Cat ID.

Set MusicCategory's caption to Music Category. Live the Notes field as is.

Close the tblMusicCategories table and accept to save it.

Inserting Fields From The Wizard:

When creating a table from the wizard, you can include and insert fields from any of the sample tables, sample fields, and categories.

Create a New, Blank Database. Name the new database Employees and store it in C:Microsoft Access Exercises.

From the Database Window, double-click-Create Table By Using Wizard.

In the first page of the Table Wizard, click the Business radio button and choose Employees from the Sample Tables list box.

From the Sample Fields, double-click EmployeeID, EmployeeNumber, FirstName, MiddleName (rename it to MI), LastName, Title, Address, City, StateOrProvince (rename it State), PostalCode (rename it ZIPCode), Country, HomePhone, EmrgcyContactName, EmrgcyContactPhone, Notes.

When you are using the wizard to create a table, not only can you include as many fields as you want, but you also can set any order of appearance. That means you can use fields that belong to different categories, also you can insert fields anywhere in your list.

To insert a new field where you have already set other fields, you click to highlight the field that will precede the new field and make your choice from the Sample Table to the Sample Fields.

Still in the first page of the wizard, in the Fields In My New Table, click Title once to select/highlight it. In the Sample Fields, double-click WorkPhone, then Extension to include both and respectively between Title and Address.

In the Sample Tables, click Customers; in the Sample Fields, double-click EmailAddress to include it after Extension-Click Next.

Name your table, tblEmployees and let the wizard set the Primary Key. Click Next, then click Finish.

Open the tblEmployees in Design View.

Set the EmployeeNumber's caption to Empl #.

Set the Extension 's caption to Ext

When you have finished, save and close the table.

Then close the database.

Creative Processes:

To create your tables, you don't have to stick to one particular process. You can start from the wizard, add fields in the Datasheet View, switch to and add fields in the Design View, and use the Build process to add some of the fields available from the wizard.

Create a new Blank Database and name it Silver National Bank.

From the Database Window, in the Tables tab, click the New button. From the New Table dialog, double-click Table Wizard.

In the Sample Tables, click Personal, and click Accounts.

In the Sample Fields, double-click AccountID, AccountNumber, and Description.

In the Sample Tables, click Addresses. In the Sample Fields, double-click EMailAddress. In the Fields In My Table, click Description.

Click the Business radio button. In the Sample Tables, click Contacts. In the Sample Fields, double-click FirstName and LastName. Click Next.

Name the table tblAccounts. Let Microsoft Access Set The Primary Key and click Next. Click Finish.

Click somewhere in the LastName column. On the main menu, click Insert -> Column. On the main menu click Format -> Rename Field and type MI.

Right-click the Email Address column header and choose Insert Column. Double-click Field1 and type Address. Enter.

Switch to Design View.

Click the FirstName field. On the main menu, click Insert -> Rows. Type Title.

Click the EMailAddress field. On the Standard toolbar, click the Insert Rows button. Type City.

Right-click EMailAddress, choose Insert Rows and type State.

Right-click EMailAddress, choose Insert Rows and type ZIPCode.

In the last field under EMailAddress, type Extension.

On the Standard toolbar, click the Build button.

From the Field Builder dialog, click the Personal radio button. In the Sample Tables list, click Addresses. In the Sample Fields list, click HomePhone and click OK.

Right-click Extension and choose Build. In the Field Builder dialog, click the Business radio button. In the Sample Tables, choose Mailing List. In the Sample Fields, double-click WorkPhone.

Save your table and close it. At this time, you can also close the database.

Exercises:

The GCS database.

Open the Georgetown Cleaning Services database.

In Design View, create a table named tblCleaningOrders with the appropriate fields.

Still in Design View, right-click the Pants field and choose to Build a new field based on OrderDate of the Orders sample table. Rename the field DepositDate and change its Format to Long Date (people who leave their clothes for dry cleaning are more interested on the day they left them, rather than on the actual date).

Right-click on the row selector of the OrderDate field and choose Copy. Right-click on the Pants field and choose Insert Rows. Right-click the row selector of the new empty field and choose Paste. Change the name of the new OrderDate field to RetrieveDate. Close the tblCleaningOrders table.

The Silver National Bank database.

Open the Silver National Bank.

In Design View, create a table with AccountTypeID (Primary Key, AutoNumber) and AccountType. Save the table as tblAccountTypes.

Create another table called tblTransactionTypes with TransactionTypeID, TransactionType, and Remarks fields.