Access Chapter 3 Simulation

Set the sort order so the results will display records with the highest number of credits first. Run the query and review the results.

Click the Credits field's sort row, click the arrow, then click Descending. On the Design tab, in the Results group, click Run.

Add a new calculated field named Tuition in the first empty column to the right of the Credits field. The new field should calculate the value in the Credits field multiplied by 150. Run the query to view the results.

Click in the first empty cell in the Field row in the query grid to the right of the Credits column. On the Design tab, in the Query Setup group, click the Builder button to open the Expression Builder. Double-click the Credits field and type "*150" to finish the expression. Click OK. Click the new field and replace Expr1 with "Tuition" as the name for the field. On the Design tab, in the Results group, click Run.

Modify this query to show only students who are freshmen. Add criteria to the Classification field to return only records where the classification is Fr. Run the query.

Click the Classification field's criteria row, type Fr. On the Design tab, in the Results group, click Run.

Add criteria to this query to return only the records where the value in the Credits field is greater than 120 and less than 130. Run the query to view the results.

Click the Credits field's criteria row, type ">120 AND <130". On the Design tab, in the Results group, click Run.

Add criteria to this query to return only the records where the value in the Credits field is >124 and the value in the Classification field is Sr Run the query to view the results.

Click the Credits field's criteria row, type ">124". Click the Classification field's criteria row, type "Sr". On the Design tab, in the Results group, click the Run button.

Limit the query results to records where the value in the Credits field is greater than 120. Run the query to view the results.

Click the Credits field's criteria row, type >120. On the Design tab, in the Results group, click Run.

Modify this query so the DeptCode field is hidden in the query results, and then run the query.

Click the DeptCode column's Show box. On the Design tab, in the Results group, click Run.

Create a parameter query where the user will enter a value to use as the criterion for the DeptCode field. Use the prompt Enter Department Code. Run the query using the Department Code FIN.

Click the DeptCode field's criteria row, type "[Enter Department Code]". On the Design tab, in the Results group, click Run. Inside the Enter Parameter Value dialog, type FIN. Click OK.

Add criteria to this query to return only the records where the value in the DeptCode field is ENG or CIS. Run the query to view the results.

Click the DeptCode field's criteria row, type END. Click the DeptCode field's or row, type CIS. On the Design tab, in the Results group, click Run.

Add criteria to this summary query to include only records where the value in the Days field is TTh. Do not include the Days field in the query groups. Run the query.

In the Total row, under Days, click the arrow and select Where. In the Criteria row, under Days, type "TTh". On the Design tab, in the Results group, click Run.

Start a new query without using one of the query wizards.

On the Create tab, in the Queries group, click Query Design.

Create a new query in Design view using the following fields in this order: From the Students table, add the LastName, FirstName, Classification, and ResidenceHall fields. From the ResidenceHalls table, add the ResidenceName and FreshmenOnly fields. Run the query.

On the Create tab, in the Queries group, click Query Design. Inside the Add Tables dialog, select Students, then click the Add button. Double-click LastName, FirstName, Classification, ResidenceHall. Inside the Add Tables dialog, select ResidenceHalls, click the Add button. Double-click ResidenceName and FreshmenOnly. On the Design tab, in the Results group, click Run.

Use the Simple Query Wizard to create a select query for a single table. Include these fields in this order: LastName, FirstName, Classification, and Credits fields from the Students table. Include every record in the results. Select the option to open the query to view information. Name the query Tuition and view the query results.

On the Create tab, in the Queries group, click the Query Wizard button. Inside the New Query dialog, click OK. Click the drop-down arrow and select Table: Students. From the list, select LastName, click >, select FirstName, click >, select Classification, click >, select Credits, and click >. Click the Next button. Click the Next button. Type "Tuition" in the What title do you want for your query? input, and click Finish.

Modify this query so results are grouped by values in the DeptName field and values in the Credits field are summarized with the Sum function. Run the query.

On the Design tab, in the Show/Hide group, click the Totals button. Click the Credits field's Total row, click the arrow, then click Sum. Click Run.

Display the Total row in this table and calculate the sum of the values in the Amount field.

On the Home tab, in the Records group, click the Total button. Click the Amount column in the Total row. Click the arrow to expand the list of functions and select Sum

Add criteria to this query to return records where the student LastName field begins with the letter A. Run the query.

Type "Like "A*" in the Criteria row in the LastName column. On the Design tab, in the Results group, click Run.

Add criteria to this query to return only the records where the value in the Credits field is less than 90 and the value in the Classification field is Jr or Sr (without punctuation). Run the query to view the results.

Type <90 in the Criteria row in the Credits column. Type <90 in the or row in the Credits column. Type Jr in the Criteria row in the Classification column. Type Sr in the or row in the Classification column. On the Design tab, in the Results group, click Run.