Microsoft Access (database) assignment

Step

Instructions

Points Possible

1

Start Access. Download and open the file named Access_Ch03_Prepare_Spa_A.accdb. Grader has automatically added your last name to the beginning of the filename. Save the file to the location where you are storing your files.

0

2

Open tblCustomer. Add a record at the end of the table using your first name, last name, address, city, state, phone, and email address.

4

3

Using Find and Replace, replace the last name of Erica Rocha with Muer.

4

4

Filters can be used to easily locate records that meet specific criterira in datasheet view of a table. Using the Text Filters tool, display only customer addresses that begin with P. Save the table with the filter applied and close the table.

4

5

Changing the appearance of a table in datasheet view can make it easier to view the data. Modify tblInvoice to have a font size of 14. Autofit all fields. Change to an Alternate Row Color to Blue, Accent 5, Lighter 60%. AutoFit the column widths so that all data are visible. Save and close the table.

4

6

The spa receptionist wants to prevent multiple mailings to the same address. Using the Find Duplicates Query Wizard, create a query that will show duplicate customer addresses. Look for duplicate values in the CustAddress field. Include all the available fields in the query. Name the query qryDuplicateAddresses. Save and close the query.

6

7

The Unmatched Query Wizard makes it easy to see records that exist in one table with no related records in a second table. For example, prospective customers who have never made a purchase. Using the Find Unmatched Query Wizard, create a query that will show anyone who is listed as a Customer in tblCustomer but does not have an appointment scheduled. Use the CustomerID field from tblCustomer and the Customer field in tblSchedule to match the records. Include all fields in the query. Name the query qryCustomersWithoutAppointments. Save and close the query.

6

8

The spa manager wants a price list for all products. Using Query Design, create a query that selects Size, ProductDescription, and Price from tblProduct (in that order). Name the query qryProductPriceList. Save and close the query.

6

9

All tables added to a query should be connected by relationships and have a common field. The staff would like to see all services scheduled for each employee. Using Query Design, create a query that returns the EmpFirstName and EmpLastName from tblEmployee, and Service, DateOfAppt, and Customer from tblSchedule (in that order). Autofit the column widths. Name the query qryEmployeeSchedule. Save and close the query.

10

10

The multiplier effect occurs when tables are added to a query with no relationship to each other, resulting in erroneous results. Open qryCustomerPhone in Design view and remove tblProduct to fix the multiplier effect issue. Save and close the query.

5

11

Sorting records in a table may make it easier to find a particular record. Open tblProduct and sort the table alphabetically by Category. Save and close the table.

3

12

Sorting data with queries provides you with more control and allows you to easily sort by multiple fields. Using Query Design, create a query that returns EmplLastName, EmpFirstName, and EmpPhone from tblEmployee and DateOfAppt, Service, and Customer from tblSchedule (in that order). Sort the results in ascending order by the EmpLastName, EmpFirstName, and Service fields and then in descending order by DateOfAppt. Autofit the column widths. Name the query qryEmployeeAppts. Save and close the query.

10

13

The manager of the spa wants to see all products that are less than $15 so she can plan an upcoming special on the spa's lower-priced products. Using Query Design, create a query that returns ProductID, ProductDescription, Size, Category, QtyInStock, and Price (in that order). Limit the results to only those products with a price less than $15.00. Autofit the column widths. Name the query qryLowPriceProducts. Save and close the query.

12

14

The manager is happy with the results of the low-price products query you created above, but she would like to post a list of the products without the prices so she can advertise the list as all under $15.00. Modify the qryLowPriceProducts query, hide the Price field from the query results. Use Save As to save the query as an object named qryProductsUnder15. Close the query.

6

15

The manager liked the employee schedule query you created, but she would like it to show the employee's first name first in the query results. If you put first name first in the grid and then last name, Access will sort by first name first. Using Query Design, create a query that returns EmpFirstName and EmpLastName from tblEmployee, DateOfAppt, Service, and Customer from tblSchedule (in that order). Sort the results in ascending order by EmpLastName and then EmpFirstName (in that order). Autofit the column widths. Name the query qryEmployeeSort. Save and close the query.

10

16

One of the spa workers has created a purchase record without specifying that the order was placed in person. You can select any records that have no value in a field using the Is Null criterion. Null is the absence of any value and is different from blank or zero. Using Query Design, create a query using tblPurchase and tblCustomer. Add PurchaseType and PurchaseDate from tblPurchase, as well as CustFirstName and CustLastName from tblCustomer (in that order). Add the appropriate criterion so that only records where the PurchaseType is blank are returned. The Query should return 1 record. With the query open, change the PurchaseType to In Person. This changes the result in the underlying tblPurchase to be In Person. Save the query with the name, qryNullPurchaseType. Save and close the query. Since you changed the blank PurchaseType to In Person, this query will return 0 results until a record is created/modified with a blank PurchaseType.

10

17

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

0

Total Points

100