1

Start Access. Download and open the file named Access_Ch06_Prepare_Golf1_Part_A.accdb. Grader has automatically added your last name to the beginning of the filename. Enable the content, if necessary.

0

2

Wildcards with the Like function in queries allow you to return results where a word or set of characters are listed in a particular place in the field. For example, if you wanted all members whose last name begins with a Ki, a criteria under last name of Like “Ki*” will return only those who begin with Ki. The * wildcard represents any character and any number of characters including zero. In other words, if the last name was just “Ki” it would be included in the results along with other names like Kinzer and Kitner.

Create a query based on the tblEmployee table that will display FirstName, LastName, Salary, HireDate, and Position in that order. In the Position field, use a wildcard and the Like function to include employees whose position ends with the word Caddy. Run the query, and then save as qryCaddy. Close the query.

12

3

Using square brackets with a wildcard and like function will allow you to look for a range of letters. For example, Like “[G-K]*” under the LastName would return last names beginning with letters G through K.

Create a query based on the tblMember table that will display FirstName, LastName, Address, City, State, and ZipCode in that order. In the LastName field, use a wildcard and the Like function to include customers whose last names begin with letters A through M.

Sort the results in Ascending order by LastName. Run the query, and then save as qryMemberAM. Close the query.

13

4

On the design tab in the query setup group, the Return Top Values allows you to return the top or bottom values by percentage or number.

Create a query based on the tblMemberLessons table that will display ScheduledDate and Fee in that order. The results should only show lessons that are the top 25% of a fees. Sort the result in Descending order by Fee. Run the query, and then save as qryTop25Percent. Close the query.

12

5

Entering text in between square brackets, [ ] specifies that it is either an existing field name or a prompt for the user to enter the value. If the words in between the [ ] do not match and existing field name, Access will prompt the user for the value and display the words in between the [ ].

Create a parameter query based on tblEmployee, tblMember, and tblMemberLessons so that when an employee’s last name is entered, it will determine what members have lessons scheduled with the employee. The query results should display FirstName and LastName from tblEmployee, FirstName and LastName from tblMember, and ScheduledDate and Fee from tblMemberLessons, in that order. The prompt should read [Enter the Employee’s Last Name]. Run the query, enter a Schilling when prompted, and then save as qryParameter. Close the query.

12

6

Setting a datatype for the parameter query under Query Parameters will make it so the user will get an error if they enter text when it should be a number, for example.

Create a parameter query based on tblMember, and tblMemberLessons so that when a fee amount is entered, it will determine what members have lessons scheduled at that fee amount. The query results should display FirstName, LastName, ScheduledDate and Fee in that order. The prompt should read [Enter Fee Amount]. Set the query parameter of [Enter Fee Amount] to a data type of Currency. Run the query, enter a 250 when prompted, and then save as qryParameter2. Close the query.

13

7

Sometimes, you may want to list several fields of data in one field. You can connect text together using the & symbol. For example, [LastName]&”, “&[FirstName] would return the employee last name, then a comma space, and then the employee first name, for example Kesner, Megan.

Create a query based on the tblEmployee and tblPosition tables that will concatenate the employee’s first and last names (separated by a space) and concatenate the employee’s position type and position field (separated by a space) in that order. Name the concatenated field with the employee’s first and last name EmployeeName. Name the concatenated field with the position type and position field JobTitle. Run the query, and then save as qryConcatenate. Close the query.

13

8

The Between … And operator will allow you to specify a range that is inclusive. For example, a criteria of 1 and 3 would return any value between 1 and 3 including exactly 1 and 3.

Create a query based on the tblEmployee table that will determine employees with salaries between 50000 and 60000. Display FirstName, LastName, and Salary in that order. Run the query, and then save as qryBetween. Close the query.

12

9

The In function allows you to specify a list of field values that should be returned. If the value is not on the list, then it will not be included in the results. For example, In(“Blue”, “Red”, “Green”) would return any fields where the value is Blue, Red, or Green. However, a value of Yellow would be excluded.

Create a query based on the tblEmployee table that will list all employees who are a Pro Shop Attendant or Pro Shop Manager using the In function. Display FirstName, LastName, and Position in that order. Run the query, and then save as qryIn. Close the query.

13

10

Close all database objects. Close the database and then exit Access. Submit the database as directed.

0

1

Open the downloaded Access file named a04ch07_grader_pc_pt1_SpaDecisions.accdb. Grader has automatically added your last name to the beginning of the filename. If necessary, enable the content.

0

2

Create a query that will calculate the total revenue for the spa. Use the tblPurchase and tblProduct tables. Add the following fields to the query design grid: ProductDescription, Price, and Quantity. In the first blank column, create a new calculated field named Revenue that multiplies the Price and Quantity fields.

5

3

Turn on the Totals row and select Sum for the Price, Quantity, and Revenue fields. Rename the Quantity field as SalesVolume.

Save the query as qryRevAndVol. Run the query to view the results, adjust the column width so that all data are visible, and then save and close the query.

2

4

Create a query that lists all customers who have made purchases at the spa. Use the tblCustomer and tblPurchase tables. Add the following fields to the query design grid: CustFirstName, CustLastName, CustAddress, CustCity, and CustState. Turn on the Totals row and ensure that all fields in the Total row are set to Group By.

Save the query as qryCustomersWithPurchases. Run the query to view the results, adjust the column widths so that all data are visible, and then close the query.

3

5

Create a query that will calculate the revenue generated from the sale of services involving waxing. Use the tblSchedule and tblService tables. Add the following fields to the query design grid, in this order: Service, Fee, and Type. Turn on the Totals row and modify the Total row according to the following:

Service: Group By
Fee: Sum
Type: Where

3.5

6

Enter a criterion in the Criteria row of the Type field so that records will only be selected if the word Wax appears anywhere in the field. Rename the Fee field as Revenue.

Save the query as qryRevenueForWaxServices. Run the query to view the results, adjust the column widths so that all data are visible, and then close the query.

3

7

Create a query that calculates the net revenue from specific spa services. Use the tblSchedule and tblService tables. In the first blank column, create a new calculated field named Net Revenue that subtracts 38% from the fee. Format the field as Currency. Add the following fields to the query design grid, in this order: Fee and Type. Rename the Fee field as Gross Revenue.

5

8

Turn on the Totals row and modify the Total row according to the following:

Net Revenue: Sum
Gross Revenue: Sum
Type: Where

2

9

Enter a criterion in the Criteria row of the Type field so that records will only be selected if the word Body Massage appears in the field.

Save the query as qryNetRevForBodyMassages. Run the query to view the results, adjust the column widths so that all data are visible, and then close the query.

3

10

Calculate the daily gross revenue by creating a query and subquery. Use the tblPurchase and tblProduct tables. Add the following fields to the query design grid, in this order: PurchaseType, PurchaseDate, Quantity, ProductDescription, and Price. Turn on the Totals row and modify the Total row according to the following:

PurchaseType: Group By
PurchaseDate: Group By
Quantity: Sum
ProductDescription: Group By
Price: Group By

8.5

11

Rename the Quantity field as SalesVolume.

Save the query as qrySalesVolumeByDate. Run the query to view the results, adjust the column widths so that all data are visible, and then close the query.

2

12

Create a query that uses the qrySalesVolumeByDate query. Add the following field to the query design grid: PurchaseDate. In the first blank column, create a new calculated field named Gross Revenue that sales volume by price. Format the field as Currency.

4

13

Turn on the Totals row and modify the Total row according to the following:

PurchaseDate: Group By
GrossRevenue: Sum

Save the query as qryGrossRevByDate. Run the query to view the results, adjust the column widths so that all data are visible, and then close the query.

4

14

Create a query that will calculate the total revenue generated for each of the product categories. Use the tblPurchase and tblProduct tables. In the first blank column, create a new calculated field named TotalRevenue that multiplies the Price and Quantity fields. Turn on the Totals row and select Sum for the TotalRevenue field.

Save the query as qryTotalRevenue. Run the query to view the results, adjust the column width so that all data are visible, and then save and close the query.

6

15

Create a query that will calculate the revenue generated for each of the product categories. Use the tblPurchase and tblProduct tables. Add the Category field to the query design grid. In the first blank column, create a new calculated field named CategoryRev that multiplies the Price and Quantity fields.

4

16

Turn on the Totals row and modify the Total row according to the following:

Category: Group By
CategoryRev: Sum

Save the query as qryCategoryRev. Run the query to view the results, adjust the column widths so that all data are visible, and then save and close the query.

3

17

Create a query that will calculate the percentage of total revenue generated by each product category. Use the qryTotalRevenue and qryCategoryRev queries. Add the following fields to the query design grid, in this order: Category and CategoryRev.

3

18

In the first blank column, create a new calculated field named PctOfGrossRev that divides the CategoryRev field by the TotalRevenue field. Format the field as Percent.

Save the query as qryPctOfGrossRevenue. Run the query to view the results and adjust the column widths so that all data are visible.

4

19

In Datasheet view of qryPctOfGrossRevenue, add the Totals row and select Sum for the PctOfGrossRev field. Save and close the query.

2

20

Create two queries that, together, will calculate the percentage of scheduled services that each spa service has contributed to the overall number of scheduled services. Use the tblSchedule table. Add the following field to the query design grid: ScheduleID. Rename the ScheduleID field as ServicesVol.

4

21

Turn on the Totals row and modify the Total row according to the following:

ServicesVol: Count

Save the query as qryTotalServices. Run the query to view the results and adjust the column width so that all data are visible. Close the query.

2

22

Create the next query. Use the tblSchedule and tblService tables. Add the following fields to the query design grid: Type and ScheduleID. Rename the ScheduleID field as ServicesVolByType. Turn on the Totals row and modify the Total row according to the following:

Type: Group By
ServicesVolByType: Count

4

23

Save the query as qryServicesVolumeByType. Run the query to view the results and adjust the column widths so that all data are visible. Close the query.

3

24

Create the query that will calculate the percentage of scheduled services that each spa service has contributed to the overall number of scheduled services. Use the qryTotalServices and qryServicesVolumeByType queries. Add the following field to the query design grid: Type. In the first blank column, create a new calculated field named PctOfServicesVol that divides the ServicesVolByType field by the ServicesVol field. Format the field as Percent.

Save the query as qryPctOfServicesVolume. Run the query to view the results and adjust the column widths so that all data are visible.

5

25

Create a query that calculates the physical volume of shipping bars of soap using cubic inches. Use the tblProduct table. Add the following fields to the query design grid, in this order: ProductDescription and Size. In the first blank column, create a new calculated field named PhysicalVolume that multiplies the Width, Length, and Depth.

5

26

Create another calculated field in the first blank column named TotalPkgVolume that divides 136 (the size of the spa’s smallest carton in cubic inches) by the PhysicalVolume field.

3

27

Enter a criterion in the Criteria row of the ProductDescription field so that records will only be selected if the word soap appears anywhere in the field.

Save the query as qryPhysicalVolumeSoaps. Run the query to view the results, adjust the column widths so that all data are visible, and then save and close the query.

2

28

Create a subquery to calculate the percentage of physical volume a bar of soap takes up in the smallest shipping carton. Use the qryPhysicalVolumeSoaps query. Add the following fields to the query design grid, in this order: ProductDescription and Size. In the first blank column, create a new calculated field named PctOfPhysicalVolume that divides physical volume by 136. Format the field as Percent. Save the query as qryPctOfPhysicalVolumeSoaps. Run the query to view the results, adjust the column widths so that all data are visible, and then close the query.

5

29

Exit Access, and then submit your file as directed by your instructor.

0