microsoft exam

juicywang

accessexam2013.docx

Home >Computer Science homework help >microsoft exam

grayscale_logo with Microsoft® Office 2013

Access 2013 Exam

Note: When the instructions say Lastname Firstname you are to replace it with your last name and your first name.

Open the desktop database AccessExam2013

1. Open the Students table and find Student ID 3852938 then replace Your First Name and Your Last Name with yours.

2. For the ID field in the Workshops table do the following: Change the Name & Caption to Workshop ID, change the Data Type to Short Text, change the field size to 5, and make it the Primary Key field of the table. Finally set the Description to Five digit Workshop ID number

3. Create a form based on the Students table. Set the theme to Slice for this object only. Change the title to College Students Form Save the object as Lastname Firstname Students Form

4. Create a report based on the Majors table. Delete the Major ID field from the Report Layout. If necessary move the remaining fields and their column headings to the left. Sort the report by Major Name field in ascending order. Change the Font Size of the Report title to 20. and save the report as Lastname Firstname test Majors Report.

5. Delete all student workshops on April 18th from the Workshops table.

6. (10 points) Open the Relationships Database Tool and add the Faculty, Students, Majors and Scholarships tables. For all relationships you are about to create you should Enforce Referential Integrity, Cascade Update Related Field and Cascade Delete Related Records. Create a relationship between the Students table and the Majors table. Create a relationship between the Faculty table and the Students table. Create a relationship between the Students table and the Scholarships table. Move the tables around so none of the relationship lines intersect. Create a Relationship report and set the Margins to Normal. Save the report as Lastname Firstname Relationship Report

7. Add a filter to the Students table to only show students who live in the city of Austin or Red Rock. Sort the query so the students from Red Rock appear at the top of the list. Apply Best Fit to all fields. Be sure to save the changes when you close the students table.

8. Open the Faculty table and sort the table by Last Name in Ascending order and Rank in Descending Order. Set the Page Layout to Landscape and the Zoom to One page in Print Preview. Add a total row showing the average salary for all faculty.

9. Create a new query based on the Scholarships table with the fields Scholarship Name, Amount, and Type. Sort by Scholarship Name in Ascending order and add the criteria to display all records >=300 for Amount. Run the query. Set all columns to Best Fit. Save the query as Lastname Firstname $300 or More Query

10. Create a new query based on the Students table and the Scholarships table and add the fields Last Name, First Name, Email, and Phone from the Students table and the fields Scholarship Name, Type, and Amount from the Scholarships table. Hide the Type field. Sort by Scholarship Name in Ascending order and add the Criteria Country Club or Foundation. Run the query and save it as Lastname Firstname Country Club or Foundation Query.

11. Create a new query based on the Students table and the Scholarships table. Add the Last Name and First Name fields from the Students table and the Awarding Organization field from the Scholarships table. Sort by Last Name ascending order. Add the Criteria so only records that begin with Texas in the Awarding Organization field show up when the query is run. Apply Best Fit to all columns. Save the query as Lastname Firstname Wildcard Query

12. Create a new query based on the Students Scholarship Query with the fields Student ID, Scholarship Name, and Amount. Sort by Student ID in ascending order. Enter an expression to calculate an Alumni Donation of the Amount. The Alumni Donation should be 50% of the amount field. Add a second calculated field to calculate the Total Scholarship by adding the Amount plus the Alumni Donation. Set all columns to Best Fit and save the query as Lastname Firstname Alumni Donations Query

13. Create a new query based on the Students Scholarships Query with the Type and Amount fields. Group by Type, and Sum the Amount field. Change the Sum by Amount column header to say “Total Amount”. Format the Amount in currency with 0 decimals. Save the query as Lastname Firstname Total by Type Query

14. Create a new query based on the Students table with the fields Last Name, First Name, Address, City, State, and Postal Code. Add the criteria to enter the parameter Enter a City Run the query and enter Elgin Save the query as Lastname Firstname City Parameter Query

15. Create a form based on the Scholarships table. Add a label to the footer with your name in it. Change the text color of your name to Red with a font size of 14. Set the Left property of the label to 1”. Save the form as Lastname Firstname Scholarships form

16. Open the Scholarships form and add the following record

Scholarship ID	Scholarship Name	Amount	Type	Award Date	Student ID	Awarding Organization
S-31	J. W. Ellis Memorial Scholarship	$500	Foundation	5/1/2018		J. W. Ellis Foundation

17. Create a report based on the Scholarships table using the Report Wizard with the fields Scholarship Name, Amount, Type, and Awarding Organization. Group the report based on Type. Sort by Scholarship Name. Select the option to Sum the Amount field and show Detail and Summary. Use a Block Layout. Set the Title to Scholarships Report. Select Portrait orientation and adjust field width to fit on one page and that the Amount field is not right next to Awarding Organization field. Title the report Lastname Firstname Scholarships Report.

18. Create a report based on the Faculty table using the Report Wizard with the fields Rank, Last Name, First Name and Salary. Group the report based on Rank. Sum and average the salary field by Rank.Title the report Lastname Firstname Faculty Rank Salary Report. To get the salary field to show up it may be necessary to increase its width.

19. David Adams would like a report showing all of his scholarships. Put the fields Last Name, First Name, Scholarship Name, Amount, Award Date and Awarding Organization into the report. Sum up the Amount column. Display report in Landscape orientation. Call the report Lastname Firstname David Adams Report. Make the report look good. David Adams has 5 scholarships thus you should have 5 records in the report.