ACC5FAS Systems Essay Is A Meaningful Sentence With The Addition Of The Word "Essay" And It Is Already Within The 70-character Limit.

This assignment must be submitted in both soft and hard copy. To submit your soft copy (electronic submission), you are required to submit MS Word, Access and Excel files and your power point slides online. Your file names should include all student IDs in the group, separated by underlines. You also need to provide your names and IDs in MS Word file, and power point slides on the first page. To submit the hard copy (printed version); you MUST submit your answers to every question underneath the related question in a printed MS word document. To do this, you are required to take a screen?shot of the answer you provide to every question and then transfer that screen?shot to MS Word file, underneath the related question for both Access and Excel questions. Apart from the screenshot, you are also required to provide the formula that you use and full steps and the instructions you follow to answer the Excel and Access questions underneath the related questions.

 

Please provide the name and the student ID number of all the group members on the front page of the printed version submission. How to Create and Insert a Screen Shot into Word By pressing PRINT SCREEN button on your keyboard, you capture an image of your entire screen, also known as a "screen?shot" or "screen?capture", and it is copied to the Clipboard in your computer's memory. You can then paste the image into a word document, or other file. Copy the entire image on the screen 1. Open everything you want copy and position it the way you want 2. Press PRINT SCREEN 3. The text you see on your keyboard might be PrtSc, PrtScn, or PrntScrn The other text on the Print Screen key is usually SysRq 4. Paste (CTRL+V) the image into a Microsoft Office program or other application Copy only the image of the active window The active window is the window that you are currently working in. Only one window can be active at a time. 1. Click the window that you want to copy

 

2. Press ALT+PRINT SCREEN 3. The text you see on your keyboard might be PrtSc, PrtScn, or PrntScrn The other text on the Print Screen key is usually SysRq 4. Paste (CTRL+V) the image into a Microsoft Office program or other application Once you get the image or screen shot into your Microsoft Word or other application such as PowerPoint program, then you are able to edit the image by using the Format tab on the Picture Tools Contextual tab. From this tab, you can adjust the brightness or contrast; change the style; position the image within your document; and change the size or crop it. You can also use Microsoft Paint (Start, All Programs, Accessories, Paint) to crop the image, or save it as a JPEG file. Page 2 of 7 Use Paint to crop your capture image and save as JPEG 1. Capture the screen 2. Click on the Start button, slide up to All Programs, then slide up to Accessories, and finally slide over and down to Paint 3. Press Ctrl + V to paste in the picture and then press Esc key.

 

4. If you want just a portion of the image you can use the Select tool to grab just a portion of the screen. 5. Press Ctrl + X to cut what you have selected 6. From the File menu choose New 7. Click No when asked to Save 8. Press Ctrl + V to paste in the new picture 9. You can continue to edit this size 10. When ready, select and copy the image and then paste it into Word (You can also go to the File menu and choose Save As) KELLY’S BOUTIQUE Kelly’s Boutique sells books as well as woman’s shoes. Kelly is eager to incorporate computers in her business. She uses a database to keep a record of her book inventory. She has a partial list of books which can be a table in her database. The list includes each book’s ISBN, department code, related supervisor and supervisor phone number, book title, publisher and publisher contact number, author and list price. 1) Create a new database file using the following information and name the file StudentName_Access. a. Use the information below to create a book table, department table and publisher table in Access, and then PRINT each table. (3 Marks) .

 

Book Table ISBN DeptID BookTitle Author PubNum ListPrice 684872153 1 Angela’s Ashes McCourt 7 $ 7.99 60244151 2 Betsy?Tacy Lovelace 2 $ 12.95 670175919 2 Blueberries for Sal McCloskey 5 $ 16.99 27136701 2 Caddie Woodlawn Brink 4 $ 17.00 60173289 1 Divine Secrets of the YaYa Sisterhood Wells 2 $ 24.00 394800168 2 Green Eggs and Ham Seuss 6 $ 7.99 439064864 2 Harry Potter and the Chamber Of Secrets Rowling 9 $ 17.95 439136350 2 Harry Potter and the Prisoner Of Azkaban Rowling 9 $ 17.95 590353403 2 Harry Potter and the Sorcerer’s Stone Rowling .

 

9 $ 17.95 140286276 1 The Deep End of the Ocean Mitchard 3 $ 12.95 Page 3 of 7 Department Table DeptID Dept Supervisor Phone 1 Adult Nancy Wine 555?9754 2 Children Barbara Manchester 555?1974 Publisher Table PubNum Publisher Contact Phone 1 Mass Market Paperback Smith 555?9745 2 Harper Collins Potter 555?7481 3 Penguin Frued 555?8974 4 Simon & Schuster Gonzales 555?9874 5 Viking Press Hu 555?1654 6 Random House Ouimet 555?9144 7 Scholastic Press Salazar 555?9888 8 Touchstone Books Chi 555?1112 9 Arthur A.

 

Levine Books Robinson 555?5118 b. Establish the appropriate relationship between each table, PRINT the relationship report. (3 Marks) c. Create and PRINT a query that lists department, book title, author, supervisor, publisher and contact. Save this query as BooksByDepartment. (6 Marks) d. Create a form that shows all fields from the book table in a columnar format. Save it as Book Form and PRINT the first record. (7 marks) e. Create and PRINT a report that contains the book title, ISBN, publisher, and phone number. The report should have no grouping, be sorted by book title in ascending order, and be formatted in a tabular portrait layout and all field information should be visible. Save this report as Book Report. (7 Marks)

 

Save the database as KellyData01. =26 marks Page 4 of 7 Kelly would like to make some adjustment to the tables by adding and deleting some records, adding some OLE fields and pictures, and changing the structure of the database. Make a copy of previous database and save it as KellyData02. 2) Make the following changes to Kelly’s database, KellyData02: A. Add the new records to the following tables Publisher Table PubNum Publisher Contact Phone 10 Warner Yee 555?7894 Book Table ISBN DeptID BookTitle PubNum Author ListPrice 039480029 2 Hop on Pop 6 Seuss $ 7.99 039480001 2 The Cat in the Hat 6 Seuss $ 7.99 446676098 1 The Notebook 10 Sparks $ 16.95 (2 Marks) B. Add a picture field for OLE objects to the book table.

 

Then add pictures to the Book table for Hop on Pop, and Deep End of the Ocean. Picture files are saved by book name and are located on your student disk in an Images folder. PRINT a screenshot of Book Form showing the first book with its image. (4 Marks) C. Establish a phone number input mask for the Phone field of the Publisher and Department tables. PRINT a screenshot of the input mask in Publisher table. (2 Marks) D. Enforce and PRINT referential integrity between the Book table, Department table and Publisher table. (4 Marks) E. Create and PRINT a validation rule for the List Price field of the Book table, making the field a required one and making sure the price is no less than $ 1 but no more than $ 100. (3 Marks)

 

F. Establish Children as the default value for the Department field of the Book Table and PRINT the table. (2 Marks) Save the database KellyData02. =17 marks Kelly would now like to create, run and print some select, parameter, and action queries. . Make a copy of previous database and save it as KellyData03. Page 5 of 7 3) Make the following changes to Kelly’s database, KellyData03: 1. Add Quantity field (Data Type=Number) to the Book Table and then enter values as follows: (1 Mark) BookTitle Quantity Angela’s Ashes 18 Betsy

 

?Tacy 4 Blueberries for Sal 5 Caddie Woodlawn 4 Divine Secrets of the YaYa Sisterhood 3 Green Eggs and Ham 15 Harry Potter and the Chamber Of Secrets 10 Harry Potter and the Prisoner Of Azkaban 8 Harry Potter and the Sorcerer’s Stone 6 Hop on Pop 8 the Cat in the Hat 4 The Deep End of the Ocean 2 The Notebook 3 Add Markup field (Data Type=Number, Field Size=Decimal, Format=Percent, Scale=2, Decimal Places=0) to the Department table and then enter values as follows: (5 Marks) DeptID Dept Markup 1 Adult 100% 2 Children 50% 2. Create and PRINT a select query that lists the author and book title for all books written by Seuss. Save this query as KellyCaseA before you print it. (2 Marks)

 

3. Create and PRINT a select query that lists the author and book title for all books purchased and supervised by Barbara Manchester, sorted in ascending order by book title. Save this query as KellyCaseB before you print it. (3 Marks) 4. Create and PRINT a select query that lists the book title and List Price for all book title start with Harry. Save this query as KellyCaseC before you print it. (1 Mark) 5. Edit the query you just created. Add fields for author and for publisher and delete the List Price. Change the criteria from ‘starting with Harry’ to ‘containing the word Secrets’. Save this query as KellyCaseD before you print it. (2 Marks) Page 6 of 7 6.

 

Create and PRINT a select query that lists the book title and List Price, quantity and retail value (a computed field equal to list price x quantity), stored in descending order by retail value. Be sure to format the field as Currency. Save this query as KellyCaseE before you print it. (2 Marks) 7. Create and PRINT a select query that lists the book title and List Price, markup, unit cost, quantity and cost for books in children department. Unit cost is a computed field (list price divided by 1 plus markup). Cost is another computed field (unit cost x quantity). Be sure to sort the query alphabetically by book title.

 

Save this query as KellyCaseF before you print it. (3 marks) 8. Modify the query created in last part so that it includes the DeptId field. Save the query as KellyCaseG1. And then use this query as the query that sums the cost of inventory by department. Save this query as KellyCaseG before you print it. (3 Marks) 9. Create and run an action query that increases all books’ List price by 5 percent. Be sure to backup your file first! Save this query as KellyCaseH. Create another query that lists the book title and List Price for all books, sorted alphabetically by book title. Save this query as KellyCaseH1 and then PRINT it. (2 Marks) 10.

 

Create a parameter query that lists books from a particular publisher. The query should ask “Enter Publisher’s Name:” and then lists the publisher, book title, and quantity for that publisher. Save this query as KellyCaseI. Run and print the query after entering “Harper Collins” as the publisher. (4 Marks) 11. Create and run and PRINT a delete query that deletes all products with a quantity of Zero. Save this query as KellyCaseJ. (2 Marks) Save the database KellyDatabase03. = 30 marks It’s time for Kelly to analyse her data in Excel sheets. Save your database and then export Book Table to MS Excel as an Excel Workbook. Remember to export data with formatting and layout. Save your file as BookSheet.

 

Open the file and try to make the following changes using Pivot Table, IF statement, Filters and VLookup: i. In a new sheet, present a summary of book titles and quantity of each title for any publisher (PubNum) chosen by user. (10 Marks) ii. Add two new columns to the sheet called Unit?Cost and Cost. Unit cost is a computed field (list price divided by 1 plus 50%). Cost is another computed field (unit cost x quantity). Calculate these columns for any book in Children department. (8 Marks) iii. Display a subset of records that only list books written by Seuss. (4 Marks)