1

Start Access. Download and open the file named Access_Ch04_PS1_CreativeGlass.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

A split form is created from one table and displays each record individually at the top of the window and then again as part of the whole table datasheet in the bottom of the window. This type of form gives you the advantage of seeing each record and the whole table in one place.

Create a Split Form using the tblContact table. In Design view, add a label to the top-left corner of the Form footer area 2.5 inches wide. Type Created by c Smith in the label. Save the form as frmContactInput

10

3

Apply the Organic theme to the frmContactInput form and all other objects in the database. Change the form title to Contact Input.

Use the form to add a new contact, using the following:

Address2

Save and close the form.

15

4

There may be times when you want to create a form using two tables. Before you can use two tables in a form, you must make sure there is a one-to-many relationship between the tables. Access will automatically use the common field between the tables to create the form.

Use the Form Wizard to create a form that will display, in the main form, all fields from tblEvent and, in the sub-form, Firstname, Lastname, Address1, Address2, City, State, Zip, and EmailAddress from tblContact. Keep all other default options. Name the main form frmEvent and name the subform frmEventAttendees subform

15

5

Make the following changes to the frmEvent form.

Remove the subform label. Move the subform to line up with the Event Location label. AutoFit the columns in the subform so that all the contact information fits. Resize the subform so that all data are visible without having to scroll horizontally.

Edit the form title to be Event Details and adjust the width of the label so that the title fits on one line. Add a label in the top-left corner of the Form Footer, 2.5 inches wide, and enter Created by c Smith as the label text. Save and close the form.

10

6

A report can be created by using one table, multiple tables, or a query. A single-table report is a report created from one table. Any or all of the fields can be selected. Gail would like to have a report that summarizes the donations received for each of the campaigns.

Use the Report Wizard to create a report to summarize the donations received. Include the following fields from tblDonation: Campaign, DonationType, DonationDate, and DonationAmount.

Add a grouping by Campaign and sort the records in ascending order by DonationDate. Add a summary option to Sum the DonationAmount field.
Organize the report with a Stepped layout and a Portrait orientation.
Name the report rptDonationSummary

10

7

Make the following changes to the rptDonationSummary report.

Add a label in the Report Footer, just under the Grand Total label, 2.5 inches wide, and enter Created by c Smith as the label text. Edit the report title to be Donation Summary Details Delete the Summary for ‘Campaign’ text box control.

Adjust the width of the DonationType text box control to be the same width as the DonationType label control. Move the DonationType label and text box controls to the right to create space from the DonationDate field.

Apply a Bold style to the Grand Total text box control. Apply conditional formatting to the DonationAmount field so that donations more than $150.00 appear in Bold, Green font (1st Row 7th Column). Save and close the report.

15

8

Use the Report Wizard to create a report to summarize each event. Include all fields from tblEvent and ContactID, Firstname, and Lastname from tblContact.
View the data by tblEvent, add a grouping by EventDate and change the grouping intervals to Day.

Sort the records in the report in ascending order by Lastname and then Firstname. Organize the report with a Stepped layout and a Landscape orientation. Name the report rptEventSummary

10

9

Make the following changes to the rptEventSummary report.

Increase the size of the EventDate by Day label, EventDate by Day text box, EventID text box, and EvenLocation text box so that all data are visible. Change the text alignment of the EventDate label and text box control to Align Left and resize the text box control, if necessary so that all data are visible.

Edit the report title to be Event Summary Details Add a label in the top-left corner of the Report Footer, 2.5 inches wide, and enter Created by c Smith as the label text. Save and closed the report.

15

10

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

0

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