Chapter 5 Scored Assignment

Access Database 5 (College Database)

Objective:  

Open the College 5SCR file from the Week 7 Files folder.
Save the file as College 5SCR_xx,  with your initials on the end of the filename, instead of the XXs.
Add your middle name as author in the file properties backstage.

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.

Edit Field Names
Open the Student Information table, and switch to design view.
Edit the field names as shown from the first name, last name and address fields.
Student First Name
Student Last Name
Street Address
This helps these fields be more descriptive.

Report Design
Right click on the Student Accounts report.  Review the various sections of the report, Report Header, Page Header, Tuition Due Date Header, Detail, Page Footer, Report Footer.

Each section produces a specific result.

Report Header:  Prints at top of first page.
Page Header:  Prints at top of every page, below the report header on the first page.
Group Header: Prints before every group of records. In this file, by Tuition Due Date.
Detail: Prints once for every record in the related table or query
Group Footer: Prints after every group of records.
Page Footer: Prints at bottom of every page.
Report Footer: Prints at the end of the report.

Calculations will be used in the report.  Here are some additional terms to understand.

Function: A built-in formula such as Sum or Count, Example =SUM([Tuition Due])
Arguments: Pieces of information needed for the function to create a result.
Field Names in Functions/Formulas:  Need to be typed exactly how listed in tables, with [ ] surrounded by square brackets.

Subtotal and Count Calculations in Report
Return to Layout View of the report.
The next directions will setup a subtotal and count at every change in month for two fields.  In other words, we need a subtotal for January Tuition Due and Count of Student ID, then the same statistics for February, etc.

Setup Subtotal & Grand Total
Switch to Design View
Choose the Group & Sort button from the Groupings & Totals group on the menu ribbon
Notice at the bottom of the report design, there is a group, sort and total section now.
Notice how the group is setup along with how it is sorted the data.
Select the More button.
Select with no Totals drop-down and setup the Tuition Due, with Grand Totals and Subtotal in group footer which gives totals for that field.
The new fields will appear in the locations on report design grid.  If you make a mistake, you can click on those field boxes and delete them and reset settings.
Switch to Layout view and see how the Tuition Due field has subtotals, by month, and a Grand Total at the bottom.  

Setup Count Field for Student ID Field
Now setup Count for Student ID field, so it counts up the number of students by month.
Switch to Design View, and hover the mouse over the Page Footer section, and drag down to give more room to Tuition Due Date Footer area.  It will be easier to adjust size of controls with more space.  You don't want to much space in that section though.
Use the Text Box button [ab] in the Controls group of Report Design Tools, and add it just below the StudentID field, in the Tuition Due Date Footer section.
In the text label box at the left, type Count of Students, and in the Unbound box type the math expression =Count([StudentID])
This tells Access to count up the number of entries per month.  The label helps describe the results.
Return to Layout View and the count should display.
Adjust the field control sizes for best fit and readability, aligning the statistic field boxes with your arrows on your keyboard for nudging boxes, left/right, up/down.
Make final adjustments to resemble report below.

Design View of New Report

Layout View of New Report

Mailing Labels (Also Considered Report)
Labels are also made using the Report tools.
Click once on the Student Information table.
Click the Create tab, find the Reports group, Labels button.
Proceed through the steps to create labels.
Choose English measurements.
Choose the manufacturer for Avery Labels.
Use label style #5160, which has three labels across. (If you cannot find it, pick one that is similar, with three across and ten down.)
Format the font, as you like as long as it is easy to read and business-like. Size 11 or 12 is probably best.
Set the labels up so they are formatted as shown below for each line.
Include one space between first and last name.

Setup These Fieldnames on Each Label on Each Row

Below is an Example of they might look in Preview.  Three lines, with spacing between fields as needed.

Evan Spiegel
63 Market St
Venice, CA  90291

Once setup, Choose Next. Sort the labels by First Name, Then Last Name
Use the suggested name given for the labels (Mailing Labels). Finish the Wizard.
The labels will appear three across.  Note that when reopened the labels will only show in one column, but print correctly on label paper.

Final Review
Make sure all your information is saved.
Submit the file.