Assignment 11 (Access 1) (Learning Outcome 1 from e-Syllabus)
Databases and Database Objects: an Introduction
Objectives:
Describe the features of the Access window
Create a database
Create tables in Datasheet and Design views
Add records to a table
Close a database
Open a database
Print the contents of a table
Create and use a query
Create and use a form
Create and print custom reports
Modify a report Layout view
Perform special database operations
Design a database to satisfy a collection of requirement
GCF Office 2016 (Access 2016) link: http://www.gcflearnfree.org/access2016/Links to an external site.
Use this link to cover the Access 2016 topics that are required for this assignment. Access 2016 also has a help function that is also explained in the above GCF web site.
ASSIGNMENT:
There is one file for this assignment. Upload your file before you submit the assignment in CANVAS.
DIRECTIONS:
Open this Access file and follow the directions below to modify it Lab 11 – Database-1.accdb Download Lab 11 – Database-1.accdb, the following table shows you screen shots of the completed parts of this assignment.
Lab 11 – Database – Client Financial Report – design view.GIFDownload Lab 11 – Database – Client Financial Report – design view.GIF
Lab 11 – Database – Client Financial Report – report view.GIFDownload Lab 11 – Database – Client Financial Report – report view.GIF
Lab 11 – Database – Client query – datasheet view-1.GIFDownload Lab 11 – Database – Client query – datasheet view-1.GIF
Lab 11 – Database – Client query – designview-1.GIFDownload Lab 11 – Database – Client query – designview-1.GIF
Lab 11 – Database – Client Table – design view.GIFDownload Lab 11 – Database – Client Table – design view.GIF
Lab 11 – Database – Client Table with records – datasheet view.GIFDownload Lab 11 – Database – Client Table with records – datasheet view.GIF
Lab 11 – Database – Service RepTable – design view.GIFDownload Lab 11 – Database – Service RepTable – design view.GIF
Lab 11 – Database – Service RepTable with records – datasheet view.GIFDownload Lab 11 – Database – Service RepTable with records – datasheet view.GIF
Add the following records to the Service Rep Table
Service
Rep
Number
Last
Name
First
Name
Street City State
Postal
Code
Start
Date
Monthly
Salary
21 Kelly Jenna 25 paint St. Kyle SC 28797 5/14/2012 $3,862.45
45 Scott Josh 1925 Pine Rd. Byron SC 28795 4/28/2014 $3,062.08
24 Liu Mia 265 Marble Dr. Kyle SC 28797 1/7/2013 $3,666.67
37 Martinez Mike 31 Steel St. Georgetown SC 28794 5/13/2013 $3,285.00
Re-size the column to best fit the data and save.
Import the Excel file “Lab 11 – Client Data” into the database as a new table Lab 11 – Client Data.xlsxDownload Lab 11 – Client Data.xlsx
Minimize File Preview
. The first row of the workbook contains the column headings (these will be the field names in the Access Table once imported). Client Number is the primary key for the new table. Assign the name “Client” to the table. Save the Import steps and assign the name ” Import-Client Data” to the steps. Assign Import Client Data as the description.
Open the Client table in design view and make the following changes:
Change the field size for the Client Number field to 5. Change the field size for the Client Name field to 30. Change the field size for the Street and City fields to 20. Change the field size for the State field to 2 and the field size for the Postal Code field to 5. Change the field size for the Service Rep Number field to 2.
Add the caption CL# to the Client Number field
Add the caption SR# tot he Service Rep Number field
Save the changes to the Client table. If Microsoft Access dialog box appears with the “Some data may be lost” message, click yes.
Open the Client table in Datasheet view and resize all column to best fit the data. Save the change to the layout of the table.
Create a query using the Simple Query Wizard for the Client table that displays the Client Number, Client name, Amount Paid, Balance Due, and Service Rep Number fields. Save the query as “Client Query”.
Create the report shown in item 2 from the Client table. The report should include the Client Number, Client Name, Amount Paid, Balance Due, and Service Rep Number fields. Include totals for the Amount paid and Balance Due fields. Be sure to change the column headings to match the screenshot for the report in item 2 above. HINT: use the Report Wizard! Will do most of this for you except the totals.
Upload via Assignment 11 link in CANVAS (do not submit until the lab is uploaded and you see it in CANVAS, the file name should be “Lab 11.accdb”. I will need the entire database (with the .accdb extension) so when you download the database from this page, note the location. If you are unsure about the location, it is likely to be in the downloads folder.
