Chapter 4 Scored Assignment

Access Database 4 (College Database)

Objective:  

Open the College 4SC file from the Week 5 Files folder.
Save the file as College 4SC_xx,  with your initials on the end of the filename, instead of the XXs.
Add your name and term enrolled in the backstage view of the file properties.

Photo Source: Creative Commons,  "Alan Gordon / People Photographing People Photographing People by NewMindSpace, South Street Seaport, NYC / 20090919.10D.54179 / SML" Links to an external site. by See-ming Lee (SML) Links to an external site. is licensed under CC BY-SA 2.0 Links to an external site.

Photo Source:  BMCC Logo

Data Review (Get to Know Information)
Review the records in all the tables to understand data involved. 
Determine how many objects are in the file when starting.

Add Student Name
Add yourself as a student to to the Student's Information Table.  Fill in fields corresponding to typical entries.
Add your student ID number to the Student Account Table.  Fill in the fields corresponding to typical entries.

Join Tables 

Join the tables based on the graphic below.  Make sure all objects are closed before starting.
Database Tools>>Relationships>>Show Each Table>>Join Based on Join Types Shown, Enforce Ref. Integrity
Note:  Student Photo field won't be in your file yet.

Working in Form Views
There are three views when working in forms, Form View, Layout View, Design View
Typically to enter new data, work in Form View or to see results of field formatting.
To edit layout, or move fields, work in Layout  View, but some commands require working in Design view.
Keep that in mind as you work with forms.

Create Split Form
Create a split form from the Student Information table.
Review the layout of the various fields.  These will be changing.
Close the form.

Add New Field to Table
Open the Student Information Table
Add a new field in design view
Name the field Student Photo and make it an attachment field for its data type.
Yes, a photo will be saved to the record.
Close and save the table.

Add Photo To Record
Open Student Information Table.
Add the photo of Alan Gordon to his record in the table.
(Photo stored in Week 5 files)
Add a photo for yourself to your record if you would like.
Close the table.

Edit Split Form
Open Student Information Form
Switch to layout view.
Add the Student Photo Field to the form (refer to GCF tutorial if needed for process)
Switch to design view of form.
Select all the fields and field names in the form.
Click Form Design tools, Arrange tab, Table group, Remove Layout Button
This will allow users to resize fields independently, so they have varying lengths and sizes.
Return to Layout View.
Size the fields and format fields as shown below. 
Fields can be dragged to new locations, or arrow keys on keyboard can move fields and field labels too.
Be careful to adjust fields to right size and check to make sure words or entries are not cut short.
Apply the Slice theme to the form.

Create Courses Form
Close all objects
Click on the courses table.
Use the Create tab, Forms>>Form Wizard
Create a new form using all the fields in the courses table, set as a Justified layout using the Form Wizard steps.
Name the form Courses.
Switch to Layout View
Create a new Combo Box command with a drop-down list.
Use the setting, for typing the values you want,
Include in the typed list:  Fall, Winter, Spring, Summer, All  (each on its own line in the column)
Use the setting, Remember the value for later use.
Name the combo box, Term Offered.
Move the field down under the last form box of Department
Adjust its size to fit words. 
Switch to Form View
For the BA101 course, choose All terms for its entry.
Switch to Layout View
Change the alignment of the credits entry to LEFT, not right.  This is done through the fields property sheet setting.
Apply bold text setting to all of the field names, and format with a purple color.
Resize each field for Course Number, title, credits and department to about 2.5 inches wide.
Switch to Design View, and add a button control/command that Find Records.  Color the button a color of your choice.

Add Logo to Form Header

Switch Courses Form to Design View
Insert the BMCC logo in the top header of the form.  (Week 5 Files)
Resize and adjust as necessary, placing picture at right side of form.
Return to Form View to see results.

Sample: (Note:  Logo not added in this sample)

Create Report
Create a new report showing the Student ID, Student First Name and Last Name from the Student Information Table and the Tuition Due and Tuition Due Date from the Student Accounts table.
Use the Report Wizard to guide the process.
Move the Tuition Due Date Field for the grouping field, and sort last name, then first name in ascending order.
Name the report, Student Due Dates.
Switch to Layout View and Apply the Slice Theme to report.
Adjust all columns so data is visible and fits on one page.  Be sure all fields and field labels remain visible as you resize and move. (arrow keys can help nudge fields, versus mouse dragging)
Save and close the report.

Export Report to Word File
Use the External Data tab, Export Grouping, More button to export and save the report to Word.

Submit your Access College file and the exported Word document to Canvas.

Final Overview
Forms are another way to input data into related tables.  Forms can be adjusted, designed, and edited.   New buttons and controls can be added to forms. There are various views a user works in while using this Access object.  Forms can be designed to fit an organization's paper form, which then makes electronic entry easier.

Reports allow a more professional view of specific data and can be alphabetized and grouped for easier interpretation and readability.  

Export Access objects to another program allow users to share work in different software.