Mall Database

Access Assignment “ Mall Database For this assignment you will be required to turn in screen printouts as you proceed through the assignment. The best way to do this is to copy and paste the screen printout into MS Word. Thus, CNTL Prt Scrn and paste into MS Word. Place your name (you can type it using a text box) on each of the screen printouts requested (identified by bolded text) and the step number that corresponds to the screen printout (i.e., John Doe, Part 1- Step 7). Please order and combine the screen printouts into one file before electronically handing them in. I do not want to have to open and look at 24 different files. After completion of the assignment you will need to submit the completed database and the screen print Word Document through the drop box. Remember to first use help when you have a question. Also, I have provided you with a link to an Access Tutorial, and of course there is always Google. Note1: Never delete records when creating a report or a query, deleting records is permanent. I provide you with two Excel spreadsheets that you need to import into the database. Note2: Never work out of D2L or e-mail, download files onto a drive you have control over (c: or your portable drive). Create an Access Database based on the following: Downtown Mall is a large, modern mall located in Downtown, IL. The Mall Operations Office is responsible for everything that happens within the mall and anything that affects the mall’s operation. Among the independent operations that report to the Mall Operations Office are the Maintenance Group, Construction Group, the Mall Security Office, Mall Mail, and the Information Desk. You’ll help the personnel at the Information Desk maintain a catalog of current job openings at stores and in Mall Operations. Rose Diamond, the director of the Mall Operations Office has requested that an Access database be created to store this information. 1. Start Access and name the database your Last Name_first initial and section number, i.e., DoeJ51_Mall. 2. Click on the Tables tag and create a table named Stores, with the following fields. You may use the Wizard or Design view (I usually use design view for tables). The field types and sizes are listed under the field name. All the fields are text fields except for floor, which is an integer. StoreID (text, size 3) StoreName (text, size 30) Floor (integer) LocationCode (text, size 3) No duplication ContactLast (text, size 20) ContactFirst (text, size 12) Extension (text, size 5) OF Mall Office 2 B1 Diamond Rose 3912 IN Information Desk 3 C3 Your Last Name Here Your First Name Here 4495 AA Alice’s Apple Display 4 D4 Morgan Alice 3954 BB Bennie’s Bagels 2 B2 James Jesse 1544 HC Hallmark Cards 3 C5 Sanchez Robert 2727 VT Victory Tees 1 A5 Jawahir Raj 1540 3. Make the StoreID the Primary Key, then change to Datasheet view. NOTE: Access is not like Excel or Word where you have to save everything you do. Access saves as you work, unless you modify the structure of a table or form, then it asks if you are sure. 4. Type in the first 3 records from the above table into the Datasheet view, screen print the datasheet in landscape orientation, make sure all fields show all data. 5. Close the Stores Table. Note: it is important to close tables, queries and forms as these show you the data at the time, a snapshot in time. 6. Click on the Forms tag and create a data entry form for the Stores table using the Forms Wizard. Select all the fields from the table by clicking on the >> button, click next, select Columnar and click next, select the style type you would like and click finish. 7. Input the next three records using the data entry form. Move back up to the 4th record and screen print the form for the current record only (at print dialog box, select current record). 8. Close the form and select the Stores table. 9. Screen print the Stores table datasheet in landscape orientation and then close the table. 10. Download Stores.xls from D2L. Select File, Get External Data, Import. Make sure the file type is Microsoft Excel. Select the Stores.xls, and click next. Select Show worksheets and select next. Make sure that you select First Row contains headings, and click next. Select into existing table and choose the Stores table, click next, and then click Finish. The Stores table does not import, why? This type of problem occurs frequently in the business environment. In order to get around this anomaly, import the data into a new table, StoresTemp and make sure that you specify field1 as the key field; otherwise Access will add an auto-number field as the key field (you do not want this). 11. Open the StoresTemp table, Screen print the StoresTemp table datasheet in landscape mode. 12. Next, highlight all the rows containing data, right click and copy. Close the StoresTemp table and open the Stores table, highlight the entire row with the * (asterisk) and paste. Your table should now contain 27 rows of data. Screen print the Stores table in landscape mode, and close the table. 13. Delete the StoresTemp table. 14. Now create a simple Query using the Query Wizard. Select the Query tab. Select the StoreName, Contact Last, Contact First, and Extension fields from the Stores table. In the second dialog box, make sure the Detail option button is selected. This second dialog box appears because the table contains numeric values. Click next, and name the query Contact List. 15. Sort the query results in ascending order by StoreName (remember the sort button is on the toolbar). Screen printthe query results, and then close the query. 16. Next use the Auto Report Wizard, select the Stores table and all the fields using the >> button. Then click next. Group by the Floor, click next twice (do not select any sorting order), select Stepped, Landscape for layout, click next, Select any style you wish, click next and Name the report Stores_Report. Select the two pages icon from the tool bar to view both pages of the report in Screen print Preview, make sure all data in the fields are visable. Screen print the report. 17. Download Job.xls into the database. Select File, Get External Data, Import. Make sure the file type is Microsoft Excel. Select the Jobs.xls, and click next. Select Show worksheets and select next. Make sure First Row contains headings is selected, and click next. Select into New table and click next. 18. Go through each field and ok the selections. On the JobID, make sure you select Indexed (Yes, no duplicates) and select Choose my own Primary Key, which should be JobID (otherwise the importer will assign its own Primary Key, starting with number 1 and incrementing by 1 with each additional record). Name the Table Jobs, and click Finish. The Jobs table should now contain 23 records. 19. Modify the structure of the Jobs table by completing the following: a. For the JobID field, enter a Description property of primary key and then change the Field to a Number/Long Integer and make sure the Required property is Yes. b. For the Store field, change the field size to 3, and type in the description foreign key c. For the SpecialRequirements field, change the field size to 50. d. For the ExperienceReq field, make it a yes/no. e. For the Position field, change the field size to 35. f. For Min Hours, change the field to decimal. g. For MaxHours, change the field to decimal. h. For the PayType field, change the field size to 2 and make sure that the only values allowed are H (for Hourly), S (for Salary), C (for Commission), use the validation rule( H or S or C) and validation text (Invalid Input “ H, S or C only). i. For the PayRate field make sure that two decimal places display, but do not display a $. j. For the FamilyOkay field, change the field type to yes/no. k. Save the table. You should receive a warning messages about lost data or integrity rules, but just go ahead and click the Yes button. 20. Screen print the Jobs table datasheet, and then close the table. 21. Create a query based on the Jobs table that only displays the records with Special Requirements, you will need to go to Query Design view after you select all the fields. Name the query Job Requirements. Screen print the Query Results. 22. Now create a data entry form for the Job table using the Forms Wizard. Select all the fields from the table by clicking on the >> button, click next, select Columnar and click finish. Name the form Job Data Entry. Close the form. 23. Use Access help and YouTube to determine how to create a parameter query. 24. Create a parameter query based on the Jobs table that will select input from the keyboard to determine the records to return, for the Family members, either Yes or No. Select the JobID, Position and FamilyOkay fields and name the query Nepotism Standing. Screen print the Query Results. 25. Open the Jobs table and modify the structure of the database as follows: a. Move the Position field so that it follows the StoreID field (Drag from one position to the other). b. Close the Jobs table. 26. Use the Jobs table form to update the database as follows: a. Add a record to the Job datasheet with the following fields, Screen printthe record. JobID: 10052 Store: JP Position: Salesclerk Min Hours: 20 Max Hours: 20 PayType: H PayRate: 12.50 FamilyOk: No b. Move to the record for Job 10039, Screen Print the record. c. Now delete this record (Job 10039). Close the form. d. Now reopen the Job table in Datasheet View so that records are displayed in primary key order. 27. Screen print the Jobs table datasheet, make sure that all the data displays and then close the table. 28. Take a break before starting on part 2. Now Rose wants to view specific information about the jobs available in Downtown Mall. Help Rose query the database by completing the following: 1. Open Access and your database. Do not open any of the tables you created previously. 2. Define a one-to-many relationship between the primary Stores table and the related Jobs table. Show both tables under Relationships. Drag the Primary key from the one table to the foreign key of the many table. Select referential integrity and both cascade options for the relationship. You should see a line between the two tables with an ˆž sign on the many side. 3. Create a new simple Query that displays StoreName, Floor, LocationCode, Position, Min Hours, Max Hours, PayType and PayRate fields, name the query Store_Jobs, and then run the query. Resize all datasheet columns to their best fit. In the Datasheet view, sort the query results in ascending order by the LocationCode field. Screen print the query datasheet, and then save and close the query. 4. Create a simple query that displays only those records with a Floor value of 1, displaying the StoreName, ContactFirst, ContactLast, LocationCode and Position. Save the query as First Floor Jobs, and then run the query. Modify the query to remove the display of the Floor field value from the query results, all you do is uncheck floor in design view. Save the modified query, run the query and screen print the query datasheet, close the query. 5. Copy/Paste the above query to re-display the Floor value (check back on). Change the requirement of Floor 1 to select the floor based on an input value (a parameter query). A parameter query shows the question in square brackets with a question mark, for example [What Floor?]. Sort on the ContactLast, ContactFirst. Save the query as Selected Floor Jobs, and then run the query, selecting Floor 2, screen print the query results, close the query. 6. Open the Store_Jobs query in Design view. Modify the query to display only those records with a Position value of Clerk. Run the query, save the query as Clerk_Jobs, screen print the query datasheet, and then close the query. 7. Modify the Clerk_Jobs query in Design view. Display only those records with a Position value of Clerk and with an Hours/Week value of 20-30. Run the query, and screen print the Query datasheet. Save and close the Query. 8. Use the Form Wizard to create a form containing a main form and a subform. Select all the fields from the Stores table for the Main form, and select the Position and Min Hours and Max Hours fields from the Jobs table for the subform. Use the Datasheet layout and style of your choice. Specify the title as Store_Info for the Main form and the title Jobs_subform for the subform. Resize all the columns in the subform to their best fit. Screen print the Just Purses main form record and its displayed subform records only. 9. Create a query using both tables to determine the spread between Min and Max hours available. You will need to create a query that displays the StoreID, the Position available, and the difference between the minimum and maximum hours available -(use builder ) to create the available hours field (this is the symbol that looks like a magic wand). Save the query as Available_Hours. Screen print the Query. 10. Now let’s create a new query based on the Job Table. Rose wants to find out how many of the open positions require prior experience and how many don’t require prior experience, display only the positions and a count of the number of jobs for each position. The query should group by those that require experience and those positions that do not require experience. Name the query Prior Experience. Screen print the Query. 11. Use the Report Wizard to create a report based on the Prior Experience query, provide a subtotal (count the number of jobs requiring prior experience and those that don’t require prior experience) at the bottom of each of the two groups, and a grand total at the end of the report. Screen print the Report. Close the report. 12. Rose wants to know the annual salary for each of the available jobs that are hourly (not those on Commission or Salary). She is only concerned with the first 40 hours. Thus, create a query that displays the Store, the available position, and the annual salary. Note: You will need to calculate the annual salary based on the Max Hours. If the position has a has the possibility of more than 40 hours, you only calculate the annual salary on the first 40 hours, thus you will need to check if the Max Hours are greater than 40. Sort by potential salary in descending order. Name the query Potential_salary and Screen print the query. 13. Now create a query that displays only those jobs that have the potential to work more than 40 hours. Name the query. Potential_Overtime and Screen print the query. 14. Use the Report Wizard to create a report based on the Store_Jobs query. Select all fields. View the data by Jobs. Specify two sort fields for the detail records: first, the Floor field in ascending order, then the LocationCode field in descending order. Choose Align Left 1 and Landscape orientation for the report. Choose the any style. Specify the title JOB TYPES for the report, switch to the Design View to make sure you have page numbers in the Footer section, then screen print the report. 15. Go to Database Tools and compress the database. 16. Congratulations you are almost finished. Exit access and attach the database and your screen print file to the drop box. For a custom paper on the above topic, place your order now! What We Offer: ¢ On-time delivery guarantee ¢ PhD-level writers ¢ Automatic plagiarism check ¢ 100% money-back guarantee ¢ 100% Privacy and Confidentiality ¢ High Quality custom-written papers