05 Dec Cashman Access 2016 | Module 10: SAM Project 1b All Around Outside Maintenance

Cashman Access 2016 | Module 10: SAM Project 1b

All Around Outside Maintenance

USING SQL

 

GETTING STARTED

· Open the file SC_AC16_10b_FirstLastName_1.accdb, available for download from the SAM website.

· Save the file as SC_AC16_10b_FirstLastName_2.accdb by changing the “1” to a “2”.

· Hint: If you do not see the .accdb file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.

· Open the _GradingInfoTable table and ensure that your first and last name is displayed as the first record in the table. If the table does not contain your name, delete the file and download a new copy from the SAM website.

 

SPECIAL NOTE: SQL QUERIES

To ensure accurate grading in this project, use the conventions listed below when writing your SQL commands:

· Do not enclose field names and table names in square brackets [] when creating and modifying these SQL queries. All field names and table names consist of single words and do not require brackets.

· Use parentheses () in your SQL code only when specified.

· All SQL commands should end with a semicolon (;).

· Only modify the aspects of the SQL commands that are specified in the assignment steps.

 

PROJECT STEPS

Create a new query in SQL View based on the State table with the following requirements:

a. Add all fields from the State table to the new query using the asterisk (*) in the Select clause.

b. Be sure to end the SQL command with a semicolon (;).

c. Run the query to produce the results shown in Figure 1 on the next page.

d. Save the query as StateQuery, and then close the query.

 

Figure 1: StateQuery Results

Open the ManagerQuery in SQL View, and then add the City field to the SELECT clause (after the Address field). Run the query, and ensure that the results match those shown in Figure 2 below Save and close the ManagerQuery.

 

Figure 2: ManagerQuery Results

 

Open the TotalAmountQuery in SQL View. Add a computed field to the SELECT clause (after the Balance field) as described below:

e. The computed field should calculate the total amount for the client by adding the AmountPaid field value to the Balance field value. (Hint: Enter AmountPaid + Balance as the computation.)

f. Use TotalAmount as the name(alias) for this computed field. (Hint: Use the AS clause.)

g. Run the query, and ensure that the results match those shown in Figure 3 below. Save and close the TotalAmountQuery.

 

Figure 3: TotalAmountQuery Results

 

Open the ClientCriteriaQuery in SQL View. Add a WHERE clause to the query in the position shown in Figure 4 below that restricts retrieval to only those clients whose balance is greater than 3000. (Hint: Figure 4 demonstrates the proper location for the WHERE clause, but it does not show the criteria that should be included in the WHERE clause.) Run the query, and check your results. Save and close the ClientCriteriaQuery.

 

Figure 4: ClientCriteriaQuery in SQL View

Open the ClientTypesQuery in SQL View. Add an OR clause to the WHERE clause in the query so that the query retrieves only those records in which the ClientType field is equal to Commercial or Corporate. (Hint: Currently, the query only retrieves records where the ClientType field is equal to Commercial.) Run the query, and ensure that the results match those shown in Figure 5. Save and close the ClientTypesQuery.

Figure 5: ClientTypesQuery Results

Open the ClientStateQuery in SQL View. Add an AND clause to the WHERE clause in the query so that the query retrieves only those records in which the State field is equal to PA and the ClientType field is equal to Residential. (Hint: Currently, the query only retrieves records in which the State field is equal to PA.) Run the query, and ensure that the results match those shown in Figure 6. Save and close the ClientStateQuery.

Figure 6: ClientStateQuery Results

Open the PennsylvaniaCountQuery in SQL View. Modify the SELECT clause to count the number of properties that are located in PA. (Hint: Change the SELECT clause to COUNT(PropertyID)). Use the AS clause to set the alias of the function result to StateCount. Run the query and ensure that the results match those shown in Figure 7 below. Save and close the PennsylvaniaCountQuery.

 

Figure 7: PennsylvaniaCountQuery Results

Open the JoinQuery in SQL view. Add a WHERE clause that joins the Client table and the Manager table. The common field in both tables is ManagerID. You will need to qualify the ManagerID field in the WHERE clause. Run the query, and ensure that the results match those shown in Figure 8 on the next page. (Hint: All rows are not displayed in the figure. The order of the records may differ. There should be 21 records in the result.) Save and close the JoinQuery.

 

Figure 8: Portion of the JoinQuery Results

Open the ManagerCertificationQuery in SQL View. Add a caption to the CPR field in the SELECT clause (Hint: Use the AS clause.) Use CPRCertification as the caption for the CPR field. Run the query, and confirm that the last field in the query displays as CPRCertification, as shown in Figure 9 on the next page. Enlarge the size of the caption so the entire caption is displayed. Save and close the ManagerCertificationQuery.

 

Figure 9: ManagerCertificationQuery Results

 

Open the SortQuery in SQL View. Modify the query to sort the records in ascending order by the State field. (Hint: Use the ORDER BY clause.) Run the query, and ensure that the results match those shown in Figure 10 below. Save and close the SortQuery.

 

Figure 10: SortQuery Results

Open the GroupingQuery in SQL View. Modify the query by completing the following tasks:

h. Add the State field to the SELECT statement. The State field should appear before the COUNT(PropertyID) function.

i. Group the records by the State field.

j. Sort the records by the State field in Ascending order.

Run the query, and ensure that the results match those shown in Figure 11 on the next page. Save and close the GroupingQuery.

 

Figure 11: GroupingQuery Results

Open the ClientSortQuery in SQL View. Modify the query to sort the records first in descending order by the State field, and then in ascending order by the City field. (Hint: Use the ORDER BY clause.) Run the query, and ensure that the results match those shown in Figure 12 below. Save and close the ClientSortQuery.

 

Figure 12: ClientSortQuery Results

 

Save and close any open objects in your database. Compact and repair your database, close it, and then exit Access. Follow the directions on the SAM website to submit your completed project.

2