MS Access work

Buad283: Assignment 02 – Microsoft Access

Scenario:

You have been asked to create a Microsoft Access database management system for an

educational publishing company. The company employs a staff of nearly 50 people, including

publishers, administrators, researchers, and salespeople, but contracts the development of each

media title to an outside author. The business rules and table relationships are as follows:

• A publisher (which is simply an employee with the job title “Publisher”) manages one or more media titles, controlling its budget, sales forecasts, and production schedules.

• An editor (an employee with the title “Editor”) can manage many authors, approving their contracts, workflow, and deadlines. Each author, however, reports to a single editor.

• Each media title is written by a single author; there are no co-author teams.

• All of the entities (tables), attributes (fields), and relationships appear in the ER diagram below. You do not need to add any additional elements to your Access database entities

beyond what appears in this ER diagram.

Figure 1. ER diagram for use in constructing an Access database

Using this ER diagram as your guide, you will modify an existing Access database in the

following steps. Once completed, upload the resulting database file into the Moodle dropbox.

Publisher

Editor

Note that the field “Topic” in Media is equivalent to “SubjectArea” in Authors.

Steps:

Perform the following steps to create this database management application.

(Note: You may use either Access 2010 or Access 2013 to complete this assignment.)

1. Download the AccessDB.accdb database file from Moodle. Open the database file and

browse its two table objects, Media and Employees, to familiarize yourself with the data.

2. Open the Media table in Design View. Ensure that the Code field is set as the Primary

Key (PK). Then, add two new fields for the Foreign Keys (FKs), as shown in the ER

diagram above. These FK fields must use the Number data type, as they will be used to

connect to the AutoNumber ID fields in the other two tables.

3. Ensure that all Short Text data types are 50 characters in size and that all Number data

types are set to Long Integer. Save your changes and return to Datasheet View.

4. Use the Find and Replace command to replace all instances of “BD” in the Media field

column with “Blu-ray”. Widen the columns (as necessary) to display their headings and

data, and then apply an alternate row colour of “Orange, Accent 2, Lighter 80%.”

5. Add a Field Validation Rule to the Media field column so that only the following values

are acceptable: Blu-ray, CDROM, DVD, VHS, and WEB. Add a Field Validation

Message that describes the available options. Sort the datasheet by Topic into ascending

order and then close it, saving your changes.

6. Open the Employees table in Design View. Rename the AutoNumber ID field to

“Employee ID” and confirm that it is the Primary Key (PK). As in the Media table,

ensure that all Short Text data types are 50 characters in size, but do not adjust the

Number data type fields. Save your changes and return to Datasheet View.

7. Set the datasheet font to Cambria, adjust the column widths (if necessary), and sort the

datasheet by Surname, into ascending order. Close the datasheet, saving your changes.

8. Create the remaining table object, Authors, using the fields shown in the ER diagram.

Specify an AutoNumber field called “Author ID” for the Primary Key. Add the required

Foreign Key to the table object as a Number data type and ensure that the Short Text

fields do not exceed 50 characters in size.

9. Add the following record data to the Authors table, but remove the “0” that appears in

the Employee ID field for each record. Save and then close the table once completed.

Author’s Name Subject Area Hourly Wage

Rachel Green Business $45.00 Ross Geller Geography $60.00 Monica Geller Science $40.00 Chandler Bing Math $55.00 Joey Tribbiani Computing $35.00 Phoebe Buffay Geography $45.00

10. Using the Relationships command on the Database Tools tab, establish the one-to-many

relationships shown in the ER diagram. Enforce referential integrity for each relationship,

but do not select the Cascade Update and Cascade Delete options. When finished, save

and close the Relationships window.

11. Create three form objects, named frmAuthors, frmEmployees, and frmMedia, that will

help users input and modify data in their respective tables. Remove the AutoNumber

primary key fields from the frmAuthors and frmEmployees forms, but leave the Code

field on the frmMedia form. Modify the titles of each form to reflect their contents (e.g.,

“Author Data Form”) and apply some basic formatting of your choice to the forms.

Lastly, enter a “Copyright (c) your name” text label in the footer area of each form.

12. Replace the three Foreign Key fields on the frmAuthors and frmMedia forms with combo

boxes that retrieve values from their respective tables. In the wizard, select the ID field,

Surname, and Given fields for display in the combo boxes. Sort the combo boxes by

Surname and hide the ID key column when asked. Remember to store the value in the

appropriate Foreign Key field and, lastly, delete the original text boxes and labels from

the form. Save and close all form objects.

13. Create a Select query, entitled qryMedia Titles, which displays the Title, Media, Grade,

PubYear, and Cost columns from the Media table (in that order). Sort this query by

ascending Grade and then, enter a criteria to display only those products that are DVDs

published since 2006 (inclusive of 2006). Add a Totals row to the resulting datasheet that

calculates the average Cost of the returned titles. (Note: Do not create a Totals query.)

14. Create a Parameter query, entitled qryEmployees, which includes all fields from the

Employees table, except for the Employee ID, and then sort the listing first by department

and then by surname. Use a criteria filter to display only those employees hired between

January 1st, 2000 and December 31st, 2006. Create a parameter filter which prompts the

user to enter a job title (e.g., Researcher) in order to filter the datasheet result.

15. Create a portrait, letter-sized report, entitled rptMedia by Topic, which displays a listing

of the media products. Design and format the report using the wizard, as follows:

• Select the Topic, Title, Media, PubYear, and Cost fields from the Media table.

• Group the data by Topic and sort the contents of each grouping by Title.

• Add a summary calculation to the report for the average cost of each Topic group.

• Apply bold to the Topic titles (e.g., Business) and the Avg Cost calculations.

• Edit the title of the report to read: Published Titles by Topic and make sure that all of the data and column titles are sized to display fully in Print Preview mode.

Important: Close all the open windows (inside of the Access work area) and

then exit (close) Access before uploading your database file into Moodle.