DEPARTMENT OF BUSINESS AND TECHNOLOGY
Syllabus for CGS 1543: Database Design and Implementation
Fall 2015 CRN: 10810 (Blended: 1 hour face-to-face each week)
Course begins: R August 27, 2014, 2:00 - 3:50AM Bldg 18, Room 202
 Professor: Floyd Jay Winters Email:  WinterF@scf.edu (please use Canvas email)
 Phone:  941 752-5488 Home/Website:  http://faculty.scf.edu/winterf/winterf.htm
 Office:  Bldg 18 Rm 131 Best Office Hours:  Best if after class
This is a live, interactive syllabus. Do NOT print.
Below (in blue background) are quick links for all assignments with detailed
Grading Criteria, directions, keystrokes, and specific point values for all assignments:

Click here for 30 Second Welcome YouTube Video from Professor Floyd Jay Winters
Assignments: Photo | Design | Table | Form | Query | Report | Tables | Forms | Queries | Reports

Course Description: Database Design and Implementation (3) (A.S.). Three hours of lecture per week, plus laboratory as assigned by instructor. Prerequisite: CGS 1000 or CGS 1570 or COP 2170 or permission of instructor. Students will study relational database design principles in the context of a popular database management software package. Relationships, entity-relationship diagrams and normalization will be emphasized. Implementation will include the creation of tables, queries, forms, reports, macros and programs.

During the course you will be analyzing, designing, and creating a business Database to support a particular business. The business can be real or make-believe. But you will work with the same business and same database throughout the course, and in the end create a valuable Portfolio Project.

Course Performance Standards: Access course performance standards by clicking on the link below:
http://scf.edu/Academics/BusinessTechnology/computer_science/course_performance_standards.asp

Email: So that I know what class you are from, and so we have a good record of our communication:
All questions and correspondence for this course must only be through Canvas email.
Proofread, spell-check, professionally write, and include a meaningful Subject line for all email.
Such as - Subject: Report Footer
I try to check Canvas email at least once or twice a day, at least 6 or 7 days a week.

Text/Materials Required: ISBN-13: 978-159136-633-1
Microsoft Access 2013 Essentials by Winters/Manchester, Labyrinth.
Disclaimer: Your professor coauthored this book. However, he was paid by the page; he earns no royalties, and he does not make any money from any sales or orders of this book.

You must use Microsoft Office 2007 or 2010 or 2013 or Office 365 for your projects. If Microsoft Office software is not available from a PC at home or work, you may use the Computer Lab at SCF. Sorry, but you can NOT use Microsoft Works or another program.
The good news: I believe students can now obtain a copy of Office 365 through SCF. You can also download a 60-day trial version for free from Microsoft: http://office.microsoft.com/en-us/try
If you do not have Microsoft Office or easy access to our computer labs, do NOT take this course.

  • Send ALL EMAIL about this course through Canvas.
  • LATE work will be 3 points for each day late, regardless of circumstances.
    Click to see why.
  • You will be dropped as a No Show if you do not submit the first project before the due date.

Remember, Life happens: If you must go out of town take a laptop or use an Internet Cafe or a hotel computer or the computer of whom you are visiting. So START EARLY! Use the SCF computer lab if necessary.

If you have problems: Be specific - give the exact message and exactly what you were doing when it happened. When appropriate, tap the [Print Screen] key to capture your error message or problem screen, then open Paint (Start > Programs > Accessories > Paint) and paste the screen copy into Paint (Ctrl + V). Save it as a JPG [File > Save As > Save as type: JPEG (*.jpg)] and attach the JPG screen capture file to me in an email so I can see exactly what is happening. Also, feel free to visit me in my office in 18-131. Finally - if you wait till the last minute, I probably cannot help you solve your problem before it is due. So be sure to do your work early.

Computer Access: You can do all assignments and take all exams in the SCF computer lab. If you plan to do your work at home, you must have access to a Computer connected to Internet and Microsoft Office. You are also expected to have "backup computer" plans: at a friend's computer, a relative's computer or at the library. Establish computer plans in case your usual computer access is disrupted during the semester. The course will continue to move along regardless of whether your computer works or not. Be sure your virus scanner is updated frequently to avoid problems during the semester. If your computer does not work for any period of time during the semester, you are expected to find a way to do the work.

SCF Helpful Links:
Department Web Page: http://www.scf.edu/pages/177.asp
Virtual Library: http://www.scf.edu/pages/1967.asp
Academic Resource Center: http://www.scf.edu/pages/144.asp
Student Services: http://www.scf.edu/pages/242.asp
DL Home Page: http://www.scf.edu/pages/108.asp  Distance Learning Self Assessment

Technical Assistance: helpdesk@scf.edu or call SCF at 941.752.5357
or contact Online Academic Services Help Desk 24/7 Toll free: 866.506.1188
or https://supportcenter.embanet.com/scf

Standards of Conduct: Students are expected to abide by all SCF Student Handbook guidelines.

Withdrawal Policy: In accordance with the State College of Florida policy as stated in the college catalog, students may withdraw from any course or all courses without academic penalty of a WF by the withdrawal deadline as listed in the State College of Florida academic calendar. See the online Academic Calendar for important dates.  The student must take responsibility for initiating the withdrawal procedure. Students are strongly encouraged to talk with their instructors first before taking any withdrawal action. In addition, students should note that faculty may also withdraw students for violating policies, procedures, or conditions of the class, as outlined in individual class syllabi, and such action could affect financial aid eligibility.

Statement of Plagiarism: Plagiarism is the use of ideas, facts, opinions, illustrative material, data, direct or indirect wording of another scholar and/or writer -professional or student- without giving proper credit. Expulsion, suspension, or any lesser penalty may be imposed for plagiarism. Copying another student's work will result in a zero for both students.

Disability Resource Center Information: SCF has a Disability Resource Center (DRC) to assist students. To request academic accommodations due to a disability, please contact the DRC at 752-5295 in Bradenton or 408-1448 in Venice, or by email at DRC@scf.edu. If you have already requested academic accommodations through the DRC for this course please meet with me to discuss your specific needs.

Course Evaluation: All assignments and their grading criteria are shown below.
Late work will lose 3 points for each day late, regardless of circumstances. Click to see why.
There are projects due every week.

It is the student's responsibility to keep track of your grades and averages throughout the semester.
You can view your grades in Canvas.

Grade Scale: A: 90 - 100, B: 80 - 89, C:70 - 79, D: 60 - 69, F: Below 60

If you have questions, feel free to visit me after class.

The link below is a very handy link of Microsoft Office Shortcuts:
http://faculty.scf.edu/winterf/0ClassFolders/Common/MicrosoftOfficeShortcuts.htm
Due Homework:

R Aug 27
Class
Begins

All Assignments must be submitted online through Canvas.
During the entire course you will be doing one Database project to support a particular business. The business can be real or make-believe. But you will work with the same business and same project throughout the entire course, and in the end create a Portfolio Database Project. So before you start, carefully think about what business you will adopt. Past examples: Beauty shop, Paint ball park, Computer repair, Custom jewelry, a Tattoo parlor, a Lawn service, Pet service, Auto Detailing, …

Lesson AC01
Exploring
Access

R Sep 03

Quiz Shortcuts

 If your computer is not properly working or configured to use Microsoft Access, then go to the SCF lab or to friend's home to do your work. 

See below for how to use the MOS certification test to replace missing or low grades or your final exam.
For a 1 page pictorial summary of a database see: 0ClassFolders/1543DB/DatabaseTerms_diagram.pdf
For extra credit there will be an Canvas Photo assignment, an online Office and Windows Shortcut quiz, an online Windows Quiz.
There will be a quiz at the beginning of almost every class.
We will do a practice grade on the next project that will be due after the quiz.

M Sep 07

 Upload Your Photo to your Canvas Profile
There is no Photo Assignment dropbox. But please add your photo to Canvas so that your professors can learn your name easily.

Lesson AC02
Database
Design

Due:
M Sep 07
11:55PM

R 10 pt Quiz

01 Gather a list of reports, records, forms, invoices, pay stubs, etc... that you would collect as you prepare to design a database. Then using Microsoft Excel, logically enter and arrange this information to plan your database Tables, Fields, Key Fields, Data Types and Field Sizes. Give this Excel file an appropriate name, such as WinchesterWebsites_DesignPhase.  (See sample in text book)
Note: the final portfolio product will be a business database that involves calculations and professional reports. Please plan accordingly.
20 List probable Tables in column A
20 List probable Fields for each table in column B (break down to smallest part)
ex: Name is not a good field. It is better to use FirstName and LastName so you can sort and search
20 List Key Fields in column C (Key fields link tables together; they create Relationships)
Note: the Primary Key is usually listed as the first field in a table
20 List the Data Type for each field in column D
ex: list the type of data (Text, Number, Date, Currency, Autonumber, Yes/No...) not actual values
20 List the probable Field Size for each field in column E
Upload the Excel file as an attachment through the appropriate Canvas Assignment Dropbox.

Key Term: Normalization - Break your data into the smalles logical pieces. For instance, do not use Name; instead use LastName and FirstName.

For a sample see:
http://faculty.scf.edu/winterf/0ClassFolders/1543DB/ac13-L02-DesignStageInExcel.png

When done - Go back the to Design assignment dropbox.
Click the Lessons tab > [Assignment Dropbox] > Design
If you do not see anything there you did not successfully submit it.
Note: the final portfolio product will be a business database that involves calculations and professional reports, subforms, subtotals and grandtotals. Please plan accordingly.

For a sample Database see:
http://faculty.scf.edu/winterf/0ClassFolders/1543DB/WinchesterCSI_2010.accdb

Lesson AC02
Database
Tables

Due
M Sep 14
11:55PM

R 10 pt Quiz

02 Using Microsoft Access create at least three related tables in Design View that will be needed for your database. (Do NOT design a Flat File Database which has repetitive data entry, resulting in unnecessarily large file sizes and data entry errors.) Give your file an appropriate name, such as WinchesterWebsites.
Note: the final portfolio product will be a business database that involves calculations and professional reports, subforms, subtotals and grandtotals. Please plan accordingly.
10 Define the fields, providing good names without spaces (ie: FirstName)
Also be sure to give a good descriptive name to your database. Database1 is not a good name. InventoryCopy2 is not a good name. (You should not be working with the copy, you should be working with the main database, which might be named Inventory.)
10 Assign at least one Key Field per table
10 Assign the appropriate Data Types for all fields. As you define your fields, make sure all of the following data types are used: Short Text, Long Text, Number, Date/Time, Currency, Yes/No, Hyperlink
10 Assign field sizes for all fields
10 Enter at least 6 realistic and complete sample records per table
* a10 Sort the records for all tables by a Key Field
* b10 Apply a Filter Records by Selection for at least one table
* 10 Use at least two wildcards
* d10 Apply a Filter Records by Form for at least one table
* e10 Use a Find and Replace
Upload the Access database file as an attachment through the appropriate Canvas assignment Dropbox. 
* In the message section of the Canvas assignment Dropbox, list a-d and discuss how and where you a. sorted your records, and how you b. filtered your records by selection, c. used wildcards, d. filter by Form, e. used a Find and Replace...

Start to set your relationships now. See page AC06.3 in your textbook. Also see:
http://faculty.scf.edu/winterf/0ClassFolders/1543DB/Relationships_Simple.htm
and
http://faculty.scf.edu/winterf/0ClassFolders/1543DB/Relationship_Simple_View.png

When done - Go back the to Tables assignment dropbox.
Click the Lessons tab > [Assignment Dropbox] > Tables
If you do not see anything there you did not successfully submit it.

Lesson AC03
Forms

Due
M Sep 21
11:55PM

R 10 pt Quiz

 03 Using the same file that you submitted last week, create at least two forms that will be needed for your database. Because you may have an Invoice and a Invoice Details or an Orders and an Orders Details or a Payroll and a Payroll Details, which will require subforms, it may be better to save these forms till later in the semester when we cover subforms. Instead do at least two forms such as very professional Employees, Customers, Clients, Products, Services, Inventory... forms
10 Make sure you have attractive form headers that include both a appropriate Titles and Subtitles.
10 Neatly align all labels and text boxes
10 No spaces for textbox field names, however use appropriate spaces in associated labels (ie: Field name, without spaces= FirstName,  Label with space=First Name)
10 Appropriately set the width and height properties for all labels and text boxes
10 Appropriately space horizontally and vertically all labels and text boxes
10 Apply the same attractive Theme to all forms
10 Use the Property Sheet to appropriately format ALL numeric fields
10 Use the Property Sheet to set at least two different distinctive fonts and two different distinctive colors for each form
* 10 Add at least one unbound control to each form
10 Add an image or logo to each form
10 Make sure each forms Tab Order is top to bottom and left to right for all forms.
* In the message section of the Canvas assignment Dropbox, list and discuss how and where you added the unbound control.

Lesson AC04
Queries
Used to:
Search
Sort
Select

Due
M Sep 28
11:55PM

R 10 pt Quiz

 04 Using the same file that you submitted last week, create at least two queries that will be needed for your database.
10 Create at least two queries in Design View which omit one of more table fields and which have at least one or two fields in a different order than appears in the tables
10 Create at least one query which links to fields from more than one table
10 Create at least one query which uses at least one of the following criteria expressions:
< or > or <= or >= or < >
10 Create at least one query which uses at least one "Between" criteria expression based on a date.
    Example: Between #06/05/2011# And #06/06/2011# But field must be set as a Date Datatype
10 Add at least one appropriately formatted calculated field (Ex: SubTotal: [Price]*[Quantity])
10 Use at least one wildcard such as a * or ? or Like criteria
    Example: you type Jeff* Access displays Like "Jeff"
    Example: you type Like "?ill" Access displays Bill and Will
10 Use at least one AND or OR Criteria (Use Criteria Row)
10 Sort at least one query (Use Sort Row)
10 Add at least one GrandTotal, Minimum, Maximum, Average or Count Total (Use Total Row)
10 Create at least one Crosstab Query. Include the word Crosstab in the query name.

Set your relationships now. See page AC06.3 in your textbook. Also see:
http://faculty.scf.edu/winterf/0ClassFolders/1543DB/Relationships_Simple.htm
and
http://faculty.scf.edu/winterf/0ClassFolders/1543DB/Relationship_Simple_View.png

-5 to -10 points will be deducted for each relationship problem:
Every table should somehow be connected directly or indirectly to all other tables.
Ex: Orders connected to Order Details which is connected to both Products and Customers. Each of the tables above must be in a One-To-Many relationship: One Product can be sold many times; one Customer many have many purchases.

Lesson AC05
Reports

Due
M Oct 05
11:55PM

R 10 pt Quiz

 05 Using the same file that you submitted last week, use the Report Wizard to create at least two Attractive and Professional Reports that will be needed for your database.
Note: If the Access Wizard does not display all fields, start again and the next time do not add as may fields or dislay report in Landscape.
10 Include both a Title and Subtitle in the Header of each report
10 Appropriately set the width and height properties for all labels and text boxes and appropriately space horizontally and vertically all labels and text boxes
10 Apply the same attractive theme to all reports
10 Use the Property Sheet to appropriately Format ALL numeric fields
10 Use the Property Sheet to set at least two different distinctive fonts and two different distinctive colors for each report
10 Add at least one Unbound Control to each report
10 Add a relevant Logo to each report
10 Create at least one attractive report which includes Grouping by a key field
(Examples: Sales by EmployeeID, Customers by State, Employees by Admin or Sales)
10 Create at least one attractive report which includes a Line Total for a numeric field
(Calculations can be easy when you build a report from a query with calculations.)
10 Neatly display the page number and date in the Footer of each report

-10 points will be deducted for each relationship problem:
Every table should somehow be connected directly or indirectly to all other tables.
Ex: Orders connected to Order Details which is connected to both Products and Customers. Each of the tables above must be in a One-To-Many relationship: One Product can be sold many times; one Customer many have many purchases.
R Oct 8 Work in pairs and in class with professor to make sure your database, and especially its relationships, are working properly.

Lesson AC06
Advanced Tables

Due
M Oct 19
11:55PM

R 10 pt Quiz

 06 Using the same file that you submitted last week, create or modify at least two tables that will be needed for your database, meeting the following criteria:
10 All Relationships are properly set for all tables: key field linked to key field
10 Most, if not all, tables will have one-to-many relationships (1- ∞)
(These were probably already set from earlier lessons after in class lectures)
10 Enforce Referential Integrity for most, if not all, tables (cannot sell a product that is not on Products table)
PLEASE DO ALL OF THE FOLLOWING ON ONE TABLE
* 10a Add at least one Yes/No Data type (Ex: College Degree, Certified, Tax Exempt)
* 10b Set Alternate Row Color for at least one table (Home > Text Formatting)
* 10c Make at least one field Required at least one table (See Required field property in Design View)
* 10d Set a Default value in at least one table (See Default field property in Design View)
* 10e Set an Input Mask in at least one table (ex: Date, Social Security, Phone)
* 10f Set a Data Validation rule and message in at least one table (Validation Rule & Text property)
* 10g Use a Lookup Wizard in at least one table (It is a Data Type found in Design View)
* 10pts In the message section of the Canvas assignment Dropbox, list a-f give the TABLE where you a. added a Yes/No data type, b. set gridlines, c. made at least one field required d. set a default value, e. input mask...
Midterm
R 10/22 		100 Points 40 Question Multiple Choice Exam. For Review see: Midterm Review Terms
Note: there is a built-in extra "hurricane/snow date." I may move the last assignments up one week.

Lesson AC07
Advanced
Forms

Due
M Nov 02
11:55PM

R 10 pt Quiz

 07 Using the same file that you submitted last week, create or modify at least one form (preferably the Invoice or Orders or Services form) that will be needed for your database, meeting the following criteria:
(It may be easier to create the new form and Subform from scratch at the same time.)
30 Add an attractive, appropriate, formatted and professional Subform to a form using related tables. (Ex: Create > Forms > Forms Wizard then choose desired fields from Invoice table, then choose desired fields from InvoiceDetails table.) Use an appropriate and descriptive Title and Subtitle. The subform should have a linked lookup feature that allows you to select products or items from the first column which will automatically populate some of the columns to the right. Ex: Chose ProductID from dropdown in the 1st column to automatically fill the associated values for Description and Cost in the 2nd and 3rd columns.
20 Use Calculated Totals on the Subform.
(Ex: In the subform, add a textbox in the Control Source Property enter = [Price] * [Quantity])
10 Add the Current Date to your Form that contains the Subform
(Choose Design > Header/Footer and double-click on Date and Time. It will place the Date and Time Control in your Header. Drag it to where you would like it, such as in the Form Footer.)
* 10a Lock a form field (Select field, choose the Data tab in Property Sheet, set Locked Yes)
Set the color of the locked field to red.
* 10b Add a Control Screen Tip to the Locked field
(Select field, choose the Other tab in Property Sheet, set ControlTip)
* 20c Create a Pop Up Form (Create > Forms > Forms > in Property Sheet choose Other tab > Pop Up)
Include PopUp in the Form Name, ex: Customers PopUp form.
* In the message section of the Canvas assignment Dropbox, list a-c and discuss how and where you added these features (specifically what form and what fields)

Lesson AC08
Advanced
Queries

Due
M Nov 09
11:55PM

R 10 pt Quiz

08 Using the same Access database file that you submitted last week, create or modify at least two queries that will be needed for your database, meeting the following criteria:
BEFORE STARTING THIS ASSIGNMENT: BACK UP YOUR DATABASE!!!
Create a Advanced Query whose name starts with the letters AQ1_ that will:
10 Select records that match a field value specified in the query grid
10 Include a calculated field
(Ex: LineTotal: [Price]*[Qty])
10 Add a Totals row to Sum the LineTotal values
(Ex: Choose !Run or Datasheet View > Home > Records > Totals > Sum)
Add a Advanced Parameter Query element that will:
10 Prompt the user to enter a CustomerID or EmployeeID or ProductID and find all matching records
(Ex: in Criteria row enter: [Enter Product ID])
* In the Canvas message specifically tell me what Parameter value to enter so that I can test the query.
10 Use the Totals row to Count all the records for the selected CustomerID or EmployeeID or ProductID
10 Create a new table named AQ2_New containing records that match the specified value for the CustID, EmpID or ProductID
(Design > Query Type > Make Table > Table Name: AQ2_New  > !Run)
10 Create an attractive well formatted Report based on the AQ2_New query named AQ2_NewReport showing all the calculations and totals, with appropriate Title and Subtitle.
10 Create a query that deletes the records you just placed in the new table from the original table
Name this query AQ2_Delete
(If you delete records from your Make Query Table everything should work well. However, note: if you delete from a related table you may have problems and get an error such as: "Microsoft Access can't delete one record(s) in the the delete query due to key violation due to key violation" - This means the table is in a relationship and if you delete a record from the table in question it already may have a record in another related table. For example - you cannot delete a customer from the Customers table if that customer bought a product and is shown in another table. One solution is to set the relationship to Cascade Deletes; another it to delete records from tables without conflicting relationships.)
10 All fields should be neatly placed and all fields should be properly formatted
10 Choose View > SQL View and copy and paste your SQL code into the Canvas message box.
See: SQL and Queries

Lesson AC09
Advanced
Reports

Due
M Nov 16
11:55PM

R 10 pt Quiz

 09 Using the same file that you submitted last week, create or modify at least two reports that will be needed for your database, using an appropriate and descriptive Title and Subtitle, meeting the following criteria:
BEFORE STARTING THIS ASSIGNMENT: BACK UP YOUR DATABASE!
10 Add an attractive and consistently placed and descriptive Title, Subtitle and Theme to ALL the reports in your database, including the imported ones.
10 Add an attractive and consistently placed Logo to ALL the reports in your database, including the imported ones.
10 Download http://faculty.scf.edu/winterf/0ClassFolders/1543DB/WageDemo2010.accdb
10 Import the Employees table from the WageDemo database. (If you already have an Employees table the new imported table will be named Employees1) Rename the imported table as ImportEmployees
(External Data > Import and Link)
20 Import the Employees List report from the WageDemo database. Notice it will not work. You will have to set the record source of the imported report to ImportEmployees.
20 Create and add a subreport to a new or existing report
Practice doing this with a backup of your database: Ctrl + C, then Ctrl + V before you do this on your working database. There are several ways to do this listed below. Try more than one method and submit the Report and Subreport that looks and works best. Make sure the final report has a Title, Subtitle, dates, page numbers and every thing is professionally spaced and aligned.
One way to do this is to build a query that includes multiple related tables such as Invoice, Invoice Details, Employees, Customer, and Products table. (Do not include all fields).  be sure to add a column to calculate price times quantity. Then you can build your report and subreport from the query sing the Report Wizard, choose the Summary option.
Another way is to: Select a table > Create > Report > Design > Controls > Subreport then click in the Details section then draw the subreport and use the wizard to create a subreport from existing related tables.
Or: Select a table > Create > Report > Design > Controls > Subreport then click in the Details section then draw the subreport and use the wizard to choose an existing subform - be sure to do an appropriate calculation.
Name the report as AR_Report
Ex: Create an Employees Report, then in the detail section add a subreport control, then use the wizard to add the Invoice Details table to summarize all invoices that the employee sold.
The subreport shrinks or grows depending on the number of detail lines it contains
10 Save a subreport as a report (File > Save As > Save Object As) name it AR_SaveSubreport
10 Run the Performance Analyzer on all the reports in the database and fix any Recommendations that Access lists (Database Tools > Analyze > Analyze Performance)
Do a screen capture of this report (Alt + PrintScreen) to capture the active window - then paste into Paint and save the file as a .jpg or pasteinto Word and save, or use the Snipping Tool and save as a .jpg. Upload your database and the Performance Analyzer .jpg file to the assignment dropbox.
Lesson AC10
Splitting
 &
Interface
 &
Startup

Due
M Nov 23
11:55PM

R 10 pt Quiz

10 Using the same file that you submitted last week, add an Interface and Startup feature, meeting the following criteria:
BEFORE STARTING THIS ASSIGNMENT: BACK UP YOUR DATABASE!
5 Set your personal information: Your Full Name and Initials (File > Options > General)
5 Set an application title, subject, author and company (File > Info > View and Edit Database Properties)
10 Create a backup copy of your database (File > Save As > Save Database As)
Notice how you can also Save as an Access 2003 .mdb database.
During the save, name the backup database Backup_OriginalName (or after the save right-click and choose rename). (Yes, Ctrl + C and Ctrl + V would be another way to back up the database.)
10 Split the copied backup database (Move the tables to a new back-end database):
(Close all open object, then Database Tools > Move Data > Access Database > Split Database)
This will create a second file that ends with _be.accd. Notice the tables in the main file now have a blue arrow next to them, indicating that they are linked to the Back-end database, so that users cannot damage the main table structure. Notice also that you can not edit the design of the back-end tables. This allows users in a multi-user environment to create their own queries and reports without risking damage to the underlying tables and relationships.
20 Using your OriginalName file, not the backup file, create a Navigation Form (Create > Forms > Navigation > Horizontal Tabs - then drag your main forms to the tabs that read [Add New])
(Note you could also choose Horizontal Tabs and Vertical Tabs Left to display all related objects together, such as the Employees Table, Employees Form, and Employees Report) Save and name the new Navigation form as Navigation Form.
10 Make sure the Navigation form displays on startup (File > Options > Current Database > Display Form > Navigation Form). Close and reopen the database to verify that the Navigation Form automatically opens when the database opens.
10 Make sure the Navigation Form includes named tabs for each main form in the database (Customers, Inventory...)
5 Include a logo and title on the Navigation form.
5 All labels are formatted attractively, all sections have attractive background colors
10 In the Navigation form Footer include two buttons to display the two most important Reports (Design > Contols > Button > Report Operations ...)
10 Include a command button to exit Access (Design > Contols > Button > Application...)

UPLOAD first the OriginalName file, and second the _be.accdc file and third the Backup_OriginalName file.

Lesson AC11
Import &
Export

Due
M Nov 30
11:55PM

R 10 pt Quiz

11 Using the same file that you submitted last week, demonstrate that you can import and export, meeting the following criteria:
BEFORE STARTING THIS ASSIGNMENT: BACK UP YOUR DATABASE!
10 Add a picture attachment to some records in a table. (Open a table in Design view, add a field named Photo, Datatype: Attachment)
List the table name with the picture in the Canvas Assignment dropbox message section.
10 Add a hyperlink to the same table in your database. (Field named Website, Datatype: Hyperlink)
List the table name with the hyperlink in the Canvas Assignment dropbox message section
10 Download from my SCF website then Import the following Delimited Text file: http://faculty.scf.edu/winterf/0ClassFolders/1543DB/Clients.txt
External Data > Import & Link > Text File > Browse > Delimited [Next] > Comma and check "First Row Contains Field Names" [Next] > pay attention to the Indexed field [Next] > choose the Indexed field as the Primary key [Next] > and keep the imported table name Clients
10 Import the following Excel file: http://faculty.scf.edu/winterf/0ClassFolders/1543DB/Payroll.xlsx and use the name Payroll_Import.
10 Link the same Excel spreadsheet to your database use the table name Payroll_Linked.
There will be a blue arrow next to the table and a green Excel icon indicating that it is linked.

10 Export your main table to Excel and name it ExportedTable.xlsx.
Confirm it works, then Upload the new Excel file (Do not type the .xlsx extension)
10 Export your main report as a PDF and name it ExportedReport.PDF.
Confirm it works, then Upload the new PDF file (Do not type the .pdf extension)
10 Export a report in HTML format and name it ExportedWebPage.htm
Confirm it works, then Upload the new HTML file
10 Copy or publish a table in your database into a new Word document.
Confirm it works, then Upload the new Word document as PublishedWordTable
10 Merge name and address fields from a table in your database into a Word document
Confirm it works, then Upload the new Word document as MailMerge.docx
Create a nice, professional word document that would be sent to multiple people. Do not type any data (such as names or addresses), the merge feature will add the data. Make sure the sender's address (your address) and the date is included at top of page.
In Word: MAILINGS tab > Start Mail Merge > Letters
Select Recipients > Use and Existing List > Browse to your Database and select a relevant table, such as Customers, Employees or Clients
Add the Address Block above the Salutation. There will be two addresses: Your address on top, followed by the date, and the recipients address block under it, followed by the salutation.
Insert a Merge Field for the First name after the Dear part of the salutation. Notice the chevrons « FirstName » which surround the fieldname. You will upload the word file that contains the chevrons.

Upload in this order 1. your access Database with Clients, Payroll_Import and Payroll_Linked, 2. ExportedTable.xlsx, 3. ExportedReport.pdf, 4. ExportedWebPage.htm, 5. PublishedWordTable, 6. MailMerge.docx with the file that shows the chevrons « ».
Lesson AC12
Maintaining
a Database

12/07
11:55PM
Please do before.		 12 Using the same file that you submitted last week, demonstrate that you can meet the following criteria:
* Create navigation buttons for a form (Previous, Next, First, Last, Find)
Design View > Controls > Button
* Create a macro that displays a message when it is run
Create tab > Macro
* Add a command button to a form that runs a macro that displays adaptable reports
* Make a copy of an object and then rename the copy
* Delete the copied object
* Make a backup copy of your database
* Analyze the performance of your database
* Run the Database Documenter on at least four objects within your database
* Run the Compact and Repair Database function
File > Info > Compact and Repair Database
* Set a password for your database (be sure to write down or store your password)
First File > Open > Browse > Open drop down > Open Exclusive
File > Info > Encrypt
R Dec 10
 Final Exam Review See 0ClassFolders/1543DB/CGS1543 Final Exam Review Terms.pdf
or http://faculty.scf.edu/winterf/0ClassFolders/1543DB/CGS1543 Final Exam Review Terms.docx
R Dec 17
2:00 PM		 Final Exam 400 Points
Replace a missed assignment or low grade:
MOS Exam

Due anytime up to five days before finals.		 A Microsoft Office Specialist Certificate (MOS) will look great on your resume and help your average.

As a way to raise your average and replace a low grade or to use in place of your final exam, you may choose to take the Microsoft Office Specialist Certification Access exam at the Lakewood Ranch campus or any other authorized testing center. When you purchase an exam at many testing centers, it also often includes a free retake. This is a nationally recognized Microsoft Office certification exam. You will have to email me (winterf@scf.edu) the URL that links to your Authenticated Digital Transcript of Certification from the Certiport secure site to verify the test results. (You should be given this link after passing the exam.) If you pass you will get a 100 for a low grade. If you do not pass, you will get whatever score is on your test results - mail me a copy of your printed results if you do not pass (a 65 is much better than a 0). You can use this grade to replace a missing project or your lowest grade. It will be used and averaged in at the end of the semester. Cost at Lakewood Ranch campus: $98 (includes a free retake if you fail it the first time). For SCF Lakewood Ranch MOS test registration information call: 941-363-7199 or 941-363-7254. Click here for MOS Exam Hints.