CPT 172 Midterm Part 1 to 3

Part 1

• Start Access. Open the database called Midterm_Part_1.accdb. This file is located in your D2L midterm dropbox. Save it on your USB drive or computer as Lastname_Firstname_Midterm_Part_1.accdb replacing Lastname_Firstname with your name. If necessary, click to Enable Content.
• Import the Excel file Midterm_Part_1_Data.xlsx into the database. The file is located in your D2L midterm dropbox. Import it as a new table with the first row containing column headings. Make the Student ID field the primary key. Name the table Students Do not save the Import Steps.
• In the Students table, change the Data Type of the Student ID field to Text and the field size to 20.
• Create a one-to-many-relationship between the Students table and the Guardians table. Be sure to join the correct fields in the two tables. Enforce Referential Integrity. Create a relationship report using the default name provided for the object name. Close and save the Relationships window.
• Open the Guardians table. Sort in ascending order by Last Name and by First Name. Make the Last Name the primary sort. Save and close the table.
• Open the Students table. Sort first in descending order by State/Province and by city in ascending order. Make the State/Provence sort the primary sort. Save and close the table.
• Create a query from the Students table that shows the student’s First Name, Last Name, and Date of Birth. Sort in ascending order by Last Name. Run the query. Save the query as Birthdays
• Create a query from the Birthdays query. Do this by copying and pasting the Birthdays query or by using the Save Object As feature. Set the criteria so only records with birthdays between 1/1/2001 and 12/31/2001 will show. Remove any previous sorts and resort the query in ascending order by Date of Birth. Run the query. Save the query as 2001 Birthdays Close the query.
• Copy the 2001 Birthdays query and rename the new query Birthdays after 2001 Open the Birthdays after 2001 query. Remove any previous criteria and sorts and add criteria so it finds all birthdays after 12/31/2001 Sort the query in descending order by Date of Birth. Run the query. Save and close the query.
• Create a query from the Guardians table showing all the fields. Find all the records that do not have a Home Phone number. Run the query. Save the query as Missing Phone Numbers Close the query.
• Create a query from the Students and the Guardians table. Add the following fields from the Students table: Student ID, Last Name, First Name. Add the following fields from the Guardians table: Relationship, First Name, Last Name. Add criteria to find only student’s mothers. Sort by the student’s Last Name and First Name, both ascending. Last Name should be the primary sort. Run the query. Save the query as Students Mothers Close the query.
• Copy the Students Mothers query. Keep the sort and change the criteria to list records with Mother OR Aunt. Run the query. Save the query as Female Guardians Close the query.
• Create a query using the Students table. Show all the fields. Add criteria to find records whose City begins with the letter A. Sort the city field in ascending order. Run the query. Save the query as Cities that Begin with A Close the query.
• Create a query using the Students table. Group by the City field and count how many students are in each city. Run the query. Save the query as City Count Close the query.
• Create a crosstab query using the Students table. The rows should represent the City field and the columns should represent the State field. The students should be counted. Save the query as Location Analysis. Close the query.
• Create a query using the Students table. Show the following fields: Student ID, Last Name, First Name, Tuition Amount. Create a new calculated field called Next Year Tuition The calculation should be the Tuition Amount field multiplied by 1.03 (for a 3% increase). Change the Format of the new field to Currency with no decimals. Run the query. Save the query as Tuition Increase Close the query.
• Close the database and submit in the D2L midterm dropbox before the deadline.

Part 2

• Open the Midterm_Part_2.accdb file in Access 2010. The file is located in the D2L midterm dropbox. Enable the content and open the Navigation Pane. Save the database as Lastname_Firstname_Midterm_Part_2.accdb onto your USB drive or computer.
• Copy the data and structure of the Personal Information table to a new table, giving the new table the name of Employee Personal Data
• Open the Employee Personal Data table, delete the Position field, and then close the table.
• Open the Employee table in Design view. Add the following field to the table: See image.
• Change to Datasheet view, saving the table in the process, and add the following records to correspond with the Empl ID. See image.
• Spell check the table ignoring proper names but correcting misspellings. Close the table.
• Create a one-to-one relationship between the Employee and Employee Personal Data tables. Enforce referential integrity. Close the relationship and save the layout.
• Create a new table with the following fields and properties to show where employees have invested in retirement options. See image.
• Make Empl ID the primary key and save the table as Retirement Plans
• Continue adding the following fields. When creating the Lookup field, use No Plan as the default value and do not limit entries under the lookup. Eligibility date is calculated by adding 160 days to the Start Date. See image.
• Change to Datasheet view, saving the table in the process, and enter the following data, filling in the rest with your own information. Close and save changes to the table. See image.
• Open the Employee Personal Information table and switch to Design view.
• For the Home Phone field, use the Input Mask Wizard to assign the Phone Number input mask, storing the symbols with the data.
• Assign a Validation Rule that requires the State field to be TX or OK. Assign a default value of TX. Assign the following Validation Text field property: The state must be TX or OK.
• Save any changes. Close the database and submit the file to the D2L midterm dropbox before the due date.

Part 3

• Open the Midterm_Part_3.accdb file. The file is located in the D2L midterm dropbox. Save the file as Lastname_Firstname_Midterm_Part_3.accdb Enable content if necessary.
• Create a query in Design view. Add ID # and Item from the Snow Boarding Inventory table to the design grid. Add a calculated field Profit Per Item by subtracting Cost from Selling Price. Save the query as Profit Per Item and Run. Increase the column width if necessary to fully show the results and column headings.
• Create a query in Design view. Add ID #, Item, Selling Price, and Quantity In Stock from the Snow Boarding Inventory table to the design grid. Add a calculated field Retail Value by multiplying Selling Price * Quantity In Stock. Save the query as Inventory Retail Value and Run. Display the Totals Row and choose the Sum function in the Retail Value column.
• Create a query using all fields except Supplier from Snow Boarding Inventory and all fields from Snow Boarding Suppliers. Run the query and save as Items & Suppliers.
• Create a crosstab query based on the Items & Suppliers query that sums the cost of the items provided by each supplier. Include row totals and save as Items & Suppliers Crosstab. Turn the Totals row on and choose Sum for the three columns of numbers.
• Create a query to locate items in the Surfing Inventory table that are unmatched in the Surfing Suppliers table. Choose to display all fields, and save as Surfing Inventory Without Matching Surfing Suppliers
• Create a query displaying ID #, Item, and Quantity in Stock from the Snow Boarding Inventory table. Enter a parameter for quantity in stock of <[Order Point]. Run the query, entering 5 as the low amount of inventory in stock. Increase column width if necessary to fully show the results and column headings. Save the query as Order Inventory Parameter Query
• Create a Make Table query including all fields from the Snow Boarding Suppliers table that creates a table named All Suppliers in the current database. Run the query and close; save the query as Make All Suppliers Table Open the newly created All Suppliers table and increase column width if necessary to fully show the results and column headings. Add an input mask for the Phone Number field and make Supplier ID the primary key field.
• Create an append query adding the records (including all of the fields) in the Surfing Suppliers table to the All Suppliers table. Run the query and close; save as Append To All Suppliers
• Create a delete query in Design view to delete the records containing Supplier 103 from the Surfing Inventory table. Save the query as Delete Supplier 103 and Run.
• Create a query including Supplier and Phone Number from the All Suppliers table and Item from the Snow Boarding Inventory table. Create a join between Supplier ID in the All Suppliers table and Supplier in the Snow Boarding Inventory table. Save the query as Snow Boarding Inner Join.
• In Design view change the join type for the existing relationship in Snow Boarding Inner Join to the second option. Run the query and save as Snow Boarding Outer Join.
• Close the database and exit Access.