CSC 455

Fall 2009

Lab 7: Introduction to Microsoft Access.

Boating Supplies. 30 points

This will be a working database for a small store that maintains an inventory of frequently forgotten but necessary items for the boating enthusiast.

Start Microsoft Access, and create a new, blank database called Boating Supplies, saving it to your USB drive or locally to Thawspace T:

Create a table called Suppliers (Create tab->Table Design) with the following attributes, setting the primary key as indicated:

Field Name Data Type Description Size

SupplierID Autonum Primary key

CompanyName Text 40

ContactFName Text 10

ContactLName Text 20

ContactPosition Text 25

Address Text 50

City Text 20

State Text 2

Zip Text 10

Phone Text 14

Fax Text 14

Enter Data

While in design view, add the following constraints:

Use the wizard or enter the following input mask for the Zip field: 00000-9999;0;_

Use the wizard or enter the following input mask for the Phone and Fax fields: !(999) 000-0000;;_

Enter CA as the Default value for state.

Change the type of the ContactPosition field to Lookup Wizard. Choose “I will type in the values I want”. Enter Sales in one cell and Customer Service in the cell below. Accept the rest of the default values for the lookup wizard, and finish it.

Switch to datasheet view, and enter the following 3 records, closing the table when you are finished. Notice the effects of the input masks, and the default value. There should also be a pull-down list for ContactPositions.

CompanyName	ContactFName	ContactLName	ContactPosition	Address	City	State	Zip	Phone	Fax
Navigating the World	Terry	Robinson	Sales	1500 Lake Forest Dr.	Lake Forest	CA	92630	714 830 1111	714 830 1212
Sailing Clothes	Ben	Morris	Customer Service	100 Pacific Coast Hwy.	Dana Point	CA	92629-1234	714 661 9898	714 661 9899
Greatest Sailing Books	Marge	Harrison	Sales	1200 Palm Tree Dr.	Mission Viejo	CA	92692 1254	714 582 1294	714 582 1294

Create a table by importing data:

The data for this table is in a file called Categories.txt which is located at http://people.uncw.edu/mferner/CSC455/labs/categories.txt. Please download that file to your local machine, and do the following in Access:

1. Click on the tab External data, and select Text file.

2. Click the Browse button to locate the downloaded file, and choose ‘Import the source data into a new table…’ The file to import is in tab delimited format. A delimiter is a character that separates fields in a text file, so tab delimited means that the Tab key was used to separate fields when the data was entered. Other choices for delimiters are commas, semicolons, spaces.

3. Work through the wizard to finish the import, give the field names indicated below.

4. Instruct the Wizard to set CategoryID as the primary key rather than adding a new one.

5. Accepting all other defaults.

6. Open the Categories table, verify that the data was imported, and adjust columns widths as necessary.

7. Switch to design view from the Home tab, and set the maximum lengths for the two text fields. Ignore any warning messages.

8. Close the table.

Categories table

Field Name Data Type Description Size

CategoryID Number Primary key

CategoryName Text 25

Description Text 75

Create a third table called Products with the following fields using design view:

Field Name Data Type Description Size

ProductID Autonum Primary key

ProductName Text 40

SupplierID Number Foreign key

CategoryID Number Foreign key

UnitPrice Currency

UnitsOnHand Number Integer

UnitsOnOrder Number Integer

ReorderLevel Number Integer

For the UnitPrice, UnitsOnHand, UnitsOnOrder, and ReorderLevel fields, enter the validation rule: >=0 and the validation text: Enter a positive number

Set the primary key

Create the table relationships:

Use the Lookup Wizard (data type) to create a lookup list for the SupplierID field. Choose ‘I want to look up the values in a table…”. Select the Suppliers table. Click next. Select SupplierID and CompanyName fields and move them to the right side. Accept the other default values, and finish the wizard.
Close the table, saving the changes.
Verify that the relationship was created by choosing the Database Tools tab and selecting Relationships. You should see a line between the Suppliers and Products table. That is the relationship. Right-click on the line, and select all three check-boxes. Click OK to save those changes.
You can also create a relationship by clicking and dragging from the one side to the many side of a relationship. Do this for the Categories table. If necessary, click on Show Table and choose Categories. Then drag the CategoryID field from the Categories table to the CategoryID field in the Products table. Select the three check-boxes again when the dialog box opens. Click OK to close.
Close the Relationships window, saving the changes.

Forms

In Access, a Form is a direct view of an Access Table, but it takes on a more user-friendly interface than the Datasheet View and typically presents one record (row) of the Table at a time for editing data.

1. Close any open tables then click once on the Suppliers table to select it.

2. Click the Create tab on the ribbon.

3. Click the Form button. A form is created for Suppliers which includes a sub-form for Products because of the relationship between the two tables.

4. If necessary, use the View button to change to Layout View.

5. Select and delete the SupplierID field. This only deletes it from the form, not from the table.

6. Use the View button to return to Form View

7. Scroll down to the bottom of the screen. The bottom-most arrow controls are for the Suppliers Form. The inner arrow controls are for the Products sub-form. Use the arrow controls at the bottom of the form to add three more records (given on the next page), and notice the results of the input masks and default values. The >* key is for a new record.

CompanyName	ContactFName	ContactLName	ContactPosition	Address	City	State	Zip	Phone	Fax
Life Rescue	Dick	Malley	Customer Service	1890 Boulder Dr.	Dana Point	CA	92629-1890	714 661 5566	714 661 6677
Personal Drug	Sue	Richardson	Sales	21000 Beach Rd.	Portland	OR	98776	814 221 8390	814 221 8391
Flags International	Ramon	Wilson	Sales	2500 Beach Rd.	Dana Point	CA	92629	714 661 9111	714 661 9000

When you add the last one, also add their two products in the subform:

Product

Name

CategoryID

Price

On Hand

On order

Reorder level

USA Flag 30x48

27.3

Mexico Flag

12x18

9.75

Close the Form and Save with the title Suppliers: Form

Now create a Products Form using the form wizard.

1. Click once on the Products table to select it.

2. Click on the Create tab

3. Click on the pull-down arrow to the right of the More Forms button, and choose Form wizard

4. Make sure Products in the table selected.

5. Move all product fields to the right pane to be included in the form

6. Continue through the wizard choosing Justified Layout, the style of your choice.

7. Save the form as Products: Form. Accept the default “Open the form to view or enter data.”

8. You decide that you don’t like that layout and want to customize it. Click on the Home tab on the Ribbon. Use the View button to change to either Layout or Design View. Edit the form to look similar to the one below. Notice that each field has two parts: the field name and the field data. You can edit both together by selecting while holding the Shift key.

a) Rearrange and resize fields as shown

b) Delete the Product ID field since it is Autonum, it does not need to be displayed or edited

c) Modify the Supplier and Category field titles (in the gray boxes, not the white)

d) Edit the Header to look similar to the one below but with your name:

When you switch to Form View, to add the following record, notice that the Suppliers field is a pull-down list of Supplier names but the Category field isn’t. Both are foreign keys referencing other tables. The difference is that the Suppliers table relationship was created using the Lookup Wizard and the Categories table relationship was created by connecting the fields in the relationships window.

Try to add the following record using the form:

Product

Name

Category

SupplierID

Unit

Price

UnitsOnHand

UnitsOnOrder

ReorderLevel

Hand Bearing Compass

Navigating the World

29.95

You should get an error message because there is no Category 7 in the Categories table. That’s what checking the Enforce Referential Integrity box helps with.

Press Esc to back out.

It would be better to have a pull-down list in the CategoryID field, too, so that you can choose the Category by name rather than number. To do that, complete the following:

1. Close all open objects.

2. Return to Database Tools->Relationships.

3. Delete the relationship between CategoryIDs by right-clicking.

4. Go to the Design view of the Products table.

5. Change the type of the CategoryID field to lookup wizard.

6. Select CategoryID and CategoryName as the values to include in the lookup column.

7. Accept the other default values.

8. Save and close changes.

9. Return to the Relationships screen and edit the relationship to Enforce Referential Integrity, etc

Notes:

· You can change the tab order of the fields by returning to Design View, Arrange tab

· For Prices don’t type the $ (it should be set as currency which will do it automatically)

· Try entering a negative number for the other numeric fields to see the result of the Validation rules in the table design.

· Because the Form was created before CategoryID was a pull-down list, it isn’t updated. From Design or Layout view, delete the existing Category boxes from the form and add the category field anew. The newly created one should contain the pull-down list.

Now add the following records:

Product Name	Category ID	SupplierID	Unit Price	UnitsOnHand	UnitsOnOrder	ReorderLevel
Hand Bearing Compass	Navigation	Navigating the World	29.95	10	0	10
Star Finder	Navigation	Navigating the World	27.95	10	0	10
Cruising Journal	Navigation	Navigating the World	19.95	24	5	25
Chart Magnifier	Navigation	Navigating the World	9.99	21	10	25

When you get to the last entry, print that one. DO NOT JUST HIT PRINT OR IT WILL PRINT ALL THE DATA. To print one record, choose the Print command from the Office button and select the circle next to Selected Record(s). Then print, hand in, and you are done for today!