Access Assignment 3 for CS172: University of Montana


Home Dickinson UM FVCC

Access Assignment 2
Table Relationships and Data Entry

Schedule | Syllabus

Clarifications may be added to this assignment.

SCENARIO: Now that you have data in all your database tables, you are ready to use that data in real life applications.
First, the local newspaper has an Events section. You will send them a list of the classes you have coming up Jan - March 2009. The newspaper wants to know the names of the classes, and their starting dates and times. The newspaper will not accept your submission, if it contains extraneous information.
Second, you need to print an upcoming class list (all classes scheduled after 12/31/08) for your teachers. All the teachers will see the full schedule, because they often make recommendations to students who want to take more classes.
Third, you need to post a list of classes appropriate for teenagers at the local high schools for their Flagship programs.
Fourth, you need to print your catalog, so you need a query for all the basic course information on upcoming classes. Do not include personal information on your instructors beyond their names.
These four items will be set up as a Query, then you will create Reports for printing. Export the first query as tab delimited text to email to your newspaper. You will also be doing some other maintenance tasks in your database.

Access Skills

Building Queries

Using Queries to create a dropdown menu

Creating Reports

Specific Instructions

Make a new copy of your database to have a backup, if something goes wrong. Name your new file with your Last Name and Access2. Ex. McDougallAccess2.
DO NOT DO THIS ASSIGNMENT IN ACCESS2 - IT IS A BACKUP!

Open your orevious Access file. Submission: you will be submitting your original Access file. Files that are named incorrectly and/or don't have the previous assignments in them will be penalized 5 points.

Open your form and go to Design View.

Delete the Subform. And create a new Subform that includes the Instructor's First and Last names. Do not add the Combo Box to this version.

For Access Assignment 3, you needed 10 class times, now add 6 more Course Dates. Be sure that two course dates fall within the time frame, two fall before the time frame and two fall after the time frame. Notice that when you enter an InstructorID, the First and Last Names populate automatically. 10 points.

Create your newspaper query. Be sure that this query shows the Course Name, the Start Date, the Start Time, and the Instructor's Name. This list should only show classes between 1/1/09 and 3/31/09. If you are missing fields to show this data, add them to your tables. 10 points

Export your Newspaper Query to an Excel file. Name the file AccessExport. 5 points.

InstructorSchedule Query. Fields will be reviewed in class, however, be sure that you have CourseHours and a SessionHours fields in your CourseDates table. Name this Query - Instructor Schedule. Be sure to sort by the Instructor's Last Name. 10 points

Create a calculated field in Instructor Schedule that shows how many sessions of the course there will be. 5 points

Instructor's Report. Group by Instructor. Put the Instructor's First Name in the same row as the Instructor's Last Name. Be sure to set the Report to print a separate sheet for each Instructor. 15 points

School Query. If you don't have Age Groups in your tables and Form, add them. Add the Age Groups as a dropdown. This query should include the same information as the Newspaper Query, but it should contrain the Age Group to Teen, however, the Age Group field should not show. 20 points

School Report. This report should include your logo and contact information in the header. 10 points

Catalog Query. As the database manager, it is not your problem to format the information for the catalog. All you have to do is provide the correct data. So, you just need to create a query with the correct fields in the correct order with the correct records. We will go through this query in class, or you can use the linked catalog as an example. Remember that, if you are missing fields in your table to create this report, you will need to add them. Example of course catalog: http://dllc.montana.com/acehtml/The%20Lifelong%20Learning%20Center%20Fall%2008.pdf
10 points

Attach Access and Excel files to an email to cs172homework@thecomputergal.com with the email subject: your last name and Access3. Ex. McDougall Access3.