Access 2013 Chapter 3 Creating And Using Queries 252636

Access 2013 Chapter 3 Creating And Using Queries Last Updated 22715

Access 2013 Chapter 3 Creating and Using Queries Last Updated: 2/27/15 Page 1 USING MICROSOFT ACCESS 2013 Independent Project 3-6 Independent Project 3-6 Courtyard Medical Plaza wants to create a query to provide details about its tenants and their rental payment history. The starting database file is provided for you. Use Design view to create the query. Edit the query to add calculated fields. After saving and testing the query, edit the query to add a parameter.

Skills Covered in This Project ï‚· Create a query in Design view. ï‚· Edit a query in Design view. ï‚· Add compound criteria to a query. ï‚· Execute a query. ï‚· Save a query. ï‚· Sort query results. ï‚· Add a parameter to a query. 1. Open the CourtyardMedicalPlaza-03.accdb start file. 2. The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor, and save it. 3. If needed, enable content in the security warning. 4. Create a new query in Design view. The query should display the rental invoice history. a. Add both tables into the Query Design window. b. Increase the size of the table objects to display all of the fields. c. Add the following fields into the query: LastName, InvoiceID, DueDate, DateReceived, AmountDue, and Discount. d. Run the query to make sure it works. The datasheet should display 54 records. 5. Save the query as InvoiceHistory. 6. Edit the query in Design View to add calculated fields. a. Insert a column to the left of InvoiceID. b. In the inserted column, add a calculated field to concatenate the FirstName and LastName fields using the following formula: Doctor: [FirstName] & " " & [LastName]. c. Don’t show the LastName field. In the next step you will use that for query criteria. d. Insert a column to the left of Discount. e. In the inserted column, add a calculated field to determine the number of days early the rent was paid using the following formula: DaysPaidEarly: [DueDate] - [DateReceived]. f. To the right of the Discount field enter a calculated field to determine the net amount of rent due with the following formula: NetRent: [AmountDue] - [Discount]. 7. Save your changes and run the query. The datasheet should look similar to Figure 3-107 and display a total of 54 records. Step 1 Download start file Access 2013 Chapter 3 Creating and Using Queries Last Updated: 2/27/15 Page 2 USING MICROSOFT ACCESS 2013 Independent Project . Edit the query to add parameters to search either by last name or by a date range. a. In the LastName field, type [Enter the last name or leave blank to select all doctors] as the criteria. b. In the DueDate field, type Between [Enter the Starting Date] And [Enter the Ending Date] on the Or row. 9. Sort the results in ascending order by LastName and DueDate. 10. Save the query. 11. Run the query. a. In the Enter the Last Name Enter Parameter Value dialog box, type Flores and click OK. b. In the Enter the Starting Date Enter Parameter Value dialog box, don’t enter anything and click OK. c. In the Enter the Ending Date Enter Parameter Value dialog box, don’t enter anything and click OK. d. The datasheet should show the six payments received from Dr. Flores (Figure 3-108). Access 2013 Chapter 3 Creating and Using Queries Last Updated: 2/27/15 Page 3 USING MICROSOFT ACCESS 2013 Independent Project . Close the query. 14. Reopen the query in Design view. Notice that Access has moved the LastName field to the right side of the Design grid and also entered a copy of the DueDate field with the Show row check box deselected. This is due to the way Access processes the query (Figure 3-110). 15. Save and close the database. 16. Upload and save your file. 17. Submit project for grading. Step 2 Upload & Save Step 3 Grade my Project 1. Write SQL to correction create the OCEAN table as specified below. 2. Write SQL to inset the data into the OCEAN table as give below OCEAN: Ocean AverageDepth PercentOfSurface Pacific Atlantic Gulf Ocean is the primary key When finished “SELECT FROM OCEAN;†should produce a table that looks something like this 3. Write SQL to correction create the BEACH table as specified below. 4. Write SQL to inset the data into the BEACH table as give below BEACH: When finished “SELECT FROM BEACH;†should produce a table that looks something like this Beach State Ocean Manele Bay Hawaii Pacific Myrtle Beach South Carolina Atlantic Nantucket Massachusetts Atlantic Clearwater Beach Florida Gulf Coronado Californa Pacific Beach is the primary key, ocean is a foreign key, set up a cascade on insert and cascade on delete for the foreign key referential integrity constraint 5. Use an alter table statement to add a constraint that PercentOfSurface must be between 0 and 100. 6. Update the Ocean Column to say “Gulf of Mexico†instead of “Gulf†7. Write an SQL statement to delete the Nantucket record. 8. Write an SQL Statement to add the column “TravelChannelRating†to the table BEACH. 9. Write the SQL statement to insert the value 1 for Manele Bay, the Value 2 for Myrtle Beach, the Value 5 for Clearmater Beach, and the Value 7 for Coronado (for the column you just created). 10. Write the SQL necessary to drop the Ocean table.