Integrated Business Projects

2nd Edition

Olinzock, Arney, & Skean
Part 3 Operations

Supplementary Activity 5

Create a Database with Tables, Forms, Queries, and Reports

In the projects for Part 3, you report to Maria Inez, manager of the Operations Department. However, you will also complete projects for Tommy Newland, the owner of Star River Adventures. As part of your job duties, you are responsible for designing and maintaining databases. Although the jobs involve extensive database applications, they also require integration with various other software applications, such as word processing, presentations, and spreadsheets.
Objectives
  • Create a new database
  • Create database tables, forms, queries, and reports
  • Enter data using datasheets and forms
  • Modify database reports
  • Printdatabase reports
  • Merge letters in Word with an Access data source
Summary of Microsoft Office Skills
  • Create Access databases
  • Open database objects in multiple views
  • Move among records
  • Format datasheets
  • Create and modify Select queries
  • Create and display forms
  • Sort records
  • Create and format reports
  • Preview and print reports
  • Correct Sspelling and Ggrammar usage
  • Preview and Pprint documents and envelopes
  • Merge Wordletters with anAccess data source

Copyright 2005 Thomson/South-Western

Supplementary Activity 5

Create a Database with Tables, Forms, Queries, and Reports

Skills Applied
  • Creating Access databases
  • Opening Access objects in the appropriate views
  • Using navigation controls to move among records
  • Formatting a table or query datasheet for display
  • Creating forms using the form Wizard
  • Creating queries
  • Creating and printing reports
  • Completing a mail merge process for form letters
  • Printing documents in Word

Task 1 Create Luncheon Invitation


Your instructor may want you to name database tables, queries, and reports by adding your name to the name given in this activity. This naming scheme can be helpful in identifying each student’s work when several students print tables or reports. / Redford Travel Agency is a company that often sends us information about people who indicate they may want to take a raft trip within the next few months. Star River Adventures pays a small commission to the travel agency if someone purchases a Star River Adventures trip as a result of a referral by the travel agency.
Tommy Newland, Owner, has asked you to record data about prospective clients that the company received from Redford Travel Agency.
1.Start Access. Create a new blank database. Name the database file SA5 Clients. Save the file in the folder where you save other work for this course.
2.Create a new database table in Design View. Include the fields listed on the following page. Set Client ID as the primary key. Save the table using the name Clients.

Table
Create a table / Field Name / Type / Size
Client ID
(primary key) / Text / Default
Title / Text / 10
First Name / Text / Default
Last Name / Text / Default
Address / Text / Default
City / Text / Default
State / Text / 2
ZIP / Text / 10
Trip Length / Text / Default
Trip Type / Text / Default
Rafting Experience / Text / Default
Request Mailing / Yes/No
Referral By / Text / Default
3.Add the following descriptions to selected fields. Save the table again using the same name.
Trip Length / Length of trip client is interested in taking
Trip Type / Type of trip client is interested in taking
Rafting Experience / Level of experience the client has with rafting
Request Mailing / Indicates whether the clients wants to receive information by mail
Referral By / Name of travel agency referring the client
4.Switch to Datasheet view. Enter the data for the two prospective clientsas shown below. Widen the datasheet columns to display all of the data. Save and close the table.
Client ID / SA051 / SA052
Title / Mr. / Mr.
First Name / Troy / Samuel
Last Name / Nordstrom / Horton
Address / PO Box 32 / 116 Ford Drive
City / Monticello / Somerset
State / KY / KY
ZIP / 42633-1121 / 42501-1160
Trip Length / one-day trip / two-day trip
Trip Type / family / adventure
Rafting Experience / novice / experienced
Request Mailing / Yes / Yes
Referral By / Redford Travel Agency / Redford Travel Agency
5.Close the database file or continue to Task 2.

Task 2 Create Form and Enter Data

Entering data for clients using the datasheet is awkward because all of the fields for one client do not fit on the screen. You will create a form to making entering the data easier.
1.Open the database file SA5 Clients that you created earlier.
2.Create a form to use when entering data for prospective clients. Use the form Wizard. Base the form on the Clients table. Include all fields in the form.
3.Choose a columnar layout. Choose Standard for the style. Name the form Clients Form. Open the form to enter data.
4.Enter data for the remaining prospective clients using the Clients Form. Data for the remaining clients are shown in a table at the end of this activity. Enter Redford Travel Agency in the Referral By field for all clients
5.After all records are entered, close the Clients Form. Close the database file or continue to Task 3.

Task 3 Create Queries and Reports


Query
Create a select or crosstab query / Most of the prospective clients indicated that they would like to receive information by mail. You will create a query to list these clients and related information.
1.Open the database file SA5 Clients that you created earlier.
2.Create a query based on the Clients table. Name the query Mailing.
3.The query results table should include only clients who indicated that they would like to receive information by mail. The results should be sorted by the Last Name field. The query results should list the following fields: Title, First Name, Last Name, Address, City, State, ZIP, Trip Length, Trip Type, and Rafting Experience.
4.Run the query. Print the query results in landscape orientation.
5.Use the Lables Wizard to create mailing labels for the prospective clients. Base the report on the Mailing query. Choose Avery C2160 for the labels. (Choose a similarsize, 1 1/2" x 2 1/2", three across, if C2160 is not listed for your system.) Use the appropriate fields for mailing labels. Name the report Labels Mailing.
6.View the Labels Mailing report. Print the labels. Use plain paper if mailing labels are not available
7.Mr. Newland plans to visit Monticello, Kentucky, next month. He is considering organizing a meeting for the prospective clients from that city.
  • Create a query named MonticelloClientsQuerybased on the Clients table.
  • The query results should list only records with Monticello in the City field.
  • Include these fields in the query results: Title, First Name, Last Name, Trip Length, Trip Type, and Rafting Experience.
  • Sort the data by the Last Name field.
8.Create a report named Monticello Clients based on the Monticello Client Query. Include all fields in the report. Use your judgment regarding formatting and style for the report.
9.Print the Monticello Clients report. Close the report and the database file.

Task 4 Merge Letters


Review appropriate formatting for business letters in Appendix A Reference Guide in your textbook. / Star River Adventures has brochures that are sent to prospective clients to provide information about rafting trips. You will create a letter to send with the brochures. You will merge the letter with the Access query table Mailing that you created earlier.
1.Open a new blank document in Word.
2.Key the letter shown on the following page. Merge codes that should be inserted in the document are shown in brackets. Leave the places where merge codes will be inserted blank for now. You will add the merge codes in a later step. Use the current date and your reference initials.
Current Date
[Title] [First Name] [Last Name]
[Address]
[City], [State] [ZIP]
Dear [Title] [Last Name]
Thank you for your interest in rafting on the beautiful streams of “wet and wild” West Virginia. Star River Adventures is happy to provide the information you requested about rafting trips in West Virginia.
Your information, provided by Redford Travel Agency, indicates that you are an [Rafting Experience] rafter and are interested in [Trip Type] trips. Star River Adventures offers one-day, two-day, and three-day trips of this type. Whether you want to raft gently down a rippling stream or conquer explosive rapids, we have a trip that is just right for you.
Although Star River Adventures is located in Sutton, West Virginia, we plan and conduct events throughout the state. You choose your event, and we will make it come true. Our most popular trips are described in the enclosed brochure. Call (304) 555-0110 today and let our staff help you plan your entire adventure.
Sincerely
Tommy Newland, Owner
xx
Enclosure

Merge
Create and print form letters / 3.Save the letter as SA5 Clients Letter. Check the spelling and grammar. Preview the letter. Proofread carefully and correct all errors.
4.Use the Mail Merge feature to merge the letter with the names and addresses of the prospective clients. Use the Mailing query table in the Access file SA5 Clients as the data source file. Select all the records in the Mail Merge Recipients list.
5.Add the appropriate merge codes to the document as indicated in the letter on the previous page.
6.Complete the merge process, merging to a new document. In the merged document, find and replace all occurrences ofan novice with a novice.
7.Save the merged and edited letters as SA5Clients Letter Merged. Preview the letters. Make changes if needed.
8.Pretend that the letters will be printed on letterhead paper. Print the merged letters (on your plain paper).

Supplementary Activity 5Page 1Integrated Business Projects

Client ID / Title / First Name / Last Name / Address / City / State / ZIP / Trip Length / Trip Type / Rafting Experience / Request Mailing
SA051 / Mr. / Tory / Nordstrom / PO Box 32 / Monticello / KY / 42633-1121 / one-day trip / family / novice / Yes
SA052 / Mr. / Samuel / Horton / 116 Ford Drive / Somerset / KY / 42501-1160 / two-day trip / adventure / experience / Yes
SA053 / Miss / Cynthia / Lincoln / 234 N. Brown Street / Monticello / KY / 42633-4589 / one-day trip / family / novice / Yes
SA054 / Mr. / Charles / Chuang / 76 Burkesville Road / Albany / KY / 42602-1258 / one-day trip / family / experienced / Yes
SA055 / Mr. / Jeffery / Reagan / 234 Maple Street / Somerset / KY / 42503-1234 / two-day trip / adventure / experienced / Yes
SA056 / Mr. / Dallas / Giles / 606 Poplar Street / Albany / KY / 42602-1606 / one-day trip / adventure / expert / Yes
SA057 / Mrs. / Doritha / Busch / 23 Gregory Road / Monticello / KY / 42633-1123 / one-day trip / family / novice / Yes
SA058 / Ms. / Kristin / Jung / 301 Beech Valley Road / Monticello / KY / 42633-1301 / three-day trip / adventure / novice / Yes
SA059 / Mrs. / Linda / Cortez / Rt. 5 Box 823 / Monticello / KY / 42633-1823 / two-day trip / family / experienced / Yes
SA510 / Mr. / Jack / Olson / 109 Cynthia Drive / Monticello / KY / 42633-1109 / two-day trip / adventure / experienced / Yes
SA511 / Miss / Lisa / Gardiner / 406 Elm Street / Monticello / KY / 42633-1406 / one-day trip / family / novice / Yes
SA512 / Mr. / Kevin / Hayes / 35 Robin Lane / Monticello / KY / 42633-1035 / one-day trip / family / novice / No
SA513 / Mrs. / Wanda / Curtis / 87 Bushy Fork Road / Monticello / KY / 42633-1087 / one-day trip / adventure / novice / Yes
SA514 / Mr. / Brandon / Roberts / 202 Oak Street / Somerset / KY / 42503-1202 / three-day trip / family / experienced / Yes
SA515 / Mr. / Louie / Bell / 2900 Burkesville Road / Albany / KY / 42602-2900 / one-day trip / family / novice / No
SA516 / Ms. / Angela / Bennet / 931 Rainbow Drive / Albany / KY / 42602-1931 / two-day trip / family / experienced / Yes
SA517 / Mr. / Joe / Black / 401 Tennessee Road / Albany / KY / 42602-1401 / one-day trip / adventure / experienced / Yes
SA518 / Mrs. / Camille / Nelson / 103 Mount Vernon Street / Somerset / KY / 42501-1103 / two-day trip / family / novice / Yes
SA519 / Mr. / Patrick / Felton / 211 Sallee Street / Somerset / KY / 42501-1211 / two-day trip / adventure / expert / Yes
SA520 / Miss / Jenni / Baker / 274 Monticello Road / Somerset / KY / 42503-1274 / one-day trip / adventure / expert / Yes
SA521 / Ms. / Mary / Perez / 595 Bourne Avenue / Somerset / KY / 42501-5951 / three-day trip / family / experienced / Yes
SA522 / Mr. / Kim / Park / 401 Monticello Street / Somerset / KY / 42501-4011 / one-day trip / family / novice / Yes
SA523 / Mr. / Joe / Patel / 710 Jarvis Avenue / Somerset / KY / 42501-1710 / three-day trip / family / experienced / Yes

Enter Redford Travel Agency in the Referral By field for all records.

Supplementary Activity 5Page 1Integrated Business Projects