Tag: Management Information Systems – Assignment Microsoft Access

Management Information Systems – Assignment Microsoft Access. Management Information Systems – Assignment Microsoft Access. BUAD 283 MIS – Microsoft Access
Scenario:
You have been asked to create a Microsoft Access database system for an educational publishing company. The company employs editors in-house, but contracts the writing to outside authors. Each editor is responsible for managing many authors, but an author reports to only one editor. The ER diagram shown here better explains the relationship. The company’s current “Media” catalog is provided in an Excel workbook to help get you started.
Steps:
Perform the following steps. (As discussed, you may use the Microsoft Access Help system, but you may not open a Web browser and “Google” your way to answers.)
1. Create a new Microsoft Access database file, named using your student ID (i.e., 300123456.accdb).
2. Import the Media.xlsx workbook into the database to create the Media table. Define Code as the primary key field and ignore any warnings that may appear as a result.
3. Once the data is imported, remove any extra columns or rows that were added and then verify that Code has been set as the PK. Furthermore, PubYear should be displayed as a number and Price should be displayed as currency. Display the Media datasheet window and adjust the column widths so that all of the data is visible. Save your work.
4. Create the two remaining table objects (Editors and Authors) using the field attributes shown in the ER diagram. Ensure that all text fields are 50 characters in size and that the primary keys are set to auto-numbered. Lastly, establish the one-to-many relationship shown in the ER diagram with referential integrity enforced, but no cascading features.
5. Add the following record data to the Editors table: Editor’s Names Office Phone Subject Area (not stored in table)  Leonard Hofstadter C750 604-765-4710 Business  Sheldon Cooper D945 250-789-7745 Computing  Howard Wolowitz B770 604-765-4716 Math & Science  Raj Koothrappali B772 604-765-4720 Geography & Languages
6. Add the following data to the Authors table. Use the EditorID (PK) value created in the previous step to fill in the EditorID (FK) value in this table. For example, Rachel Green (Business) is managed by and should be linked to Leonard Hofstadter (Business). Author’s Names 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
7. Create two form objects, named frmEditors and frmAuthors, that will help users enter data into their respective tables. Remove the PK Autonumber fields (and rows) from both forms, reduce the width of the text entry boxes, and modify the titles to read “Editor Input Form” and “Author Input Form” respectively. Lastly, add a text label to the footer of each form with the phrase “Copyright (c) by your name” (replacing your name with your full name, of course.)
8. On the frmAuthors form, replace the EditorID text box with a combo box control that displays the FirstName and LastName fields from the Editors table. Sort the contents of the combo box by ascending LastName and then store the result in the EditorID field.
9. Create a Select query, entitled qryMedia Titles, which displays only the Title, Media, Grade, and Price columns from the Media table. Sort this query by ascending Grade and then enter criteria to display only those DVD products published since 2006 (inclusive).
10. Create a Parameter query, entitled qryEditor Portfolio, which prompts the user with “Enter an Editor’s First Name:” in order to filter the datasheet result. When the query is run, the resulting datasheet should display columns for the Editor’s FirstName and LastName, along with each Author’s FirstName, LastName, and SubjectArea fields. Lastly, sort the results into alphabetical order by the Authors’ last names.
11. Create a report object, entitled rptMedia by Type, which displays a listing of the media products. Design and format the report as follows:
 Include all Media fields, except for Code and Topic.
 Group the data by Media type (e.g., DVD, WEB, CDROM, VHS, BD).
 Within each grouping, sort the report into ascending order by Title.
 Add a summary calculation to the report for the average price of each media group.
 Edit the title of the report to read: Our Titles by Media Type and make sure that all of the data and column titles display fully in Print Preview’s Portrait mode.
12. Close all the open windows (inside of the Access work area) and then exit (close) Access before uploading your database file into Moodle.

Management Information Systems – Assignment Microsoft Access

Management Information Systems – Assignment Microsoft Access