Unformatted Attachment Preview
project
9
McMullan & Donovan Realty:
Integrating Access, Word,
PowerPoint, and Excel
OBJECTIVES
Update company database
with imported data
Create Access queries
Create relationships between
Access tables
Prepare effective documents
integrating data from Word,
PowerPoint, Excel, and
Access
Work with limited supervision
SETTING
In Project 9, you continue your work for McMullan & Donovan Realty. You
will expand the database you created in Project 8 by importing data from text and
Excel files. More queries are needed as you create Word documents and PowerPoint
presentations. Access reports and forms will also be required.
JOBS
1. Mail merge letters using Access query
2. Memo with Access query
3. Access data exported to Word and Excel
4. PowerPoint presentation with Excel and Access data
5. Report created in Word
STANDARD OPERATING PROCEDURES
Use these procedures for all documents you prepare.
1. Use the Civic document theme for all documents and Access forms and reports.
2. Use the m&d letterhead and mod memo templates from the data files to prepare all
letters and memos. Save as Word documents and not as templates.
3. Format tables imported from a query with the Light Shading - Accent 1 table style.
Center tables horizontally.
4. Set up a folder named AC-WD-PP-EX Project 9 to store project files. Name all
solution files p9-j (plus the job number, jl, j2, etc.) unless directed otherwise.
agents from
1
7 Letters and Form
Is Using Access
1-DATA
LETTERHEAD
TIPLE LISTINGS
ENTS
Ms. McMullan has asked you to prepare form letters inviting a small
group
of
other agencies to tour M&D's two newest listings in San Antonio. You will also prepare
labels for mailing the letters using the Access Labels report.
1. Open the Access database p9-j1-data and save as p9-jla. Import the multiple listings
agents text file as a table. Save the table as Multiple Listings Agents.
2. Filter the Current Listings query to identify current listings in San Antonio. Copy
two newest listings in the filtered records.
3. Open the Word data file m&d letterhead and save as p9-j1b main. Use the Multiple
Listings Agents table from the Access database as your recipient list, and sort ascendi
by ZIP. Key the letter shown on the next page as the mail merge main document.
Insert the fields as indicated. Merge the letters and save as p9-lb.
OJECT 9 MCMULLAN & DONOVAN REALTY: Integrating Access, Word, PowerPoint, and Excel ACCESS
Job 1,
continued
August 20, 2012
«Title> «Last_Name>
«Agency»
«Address
«City», «State» «ZIP»
Remove extra space
after paragraphs
«GreetingLine»
Agents at McMullan & Donovan Realty are pleased to invite you to a special
preview showing on Tuesday, August 28, of our two newest San Antonio
listings. The table shown below outlines our tour with time and listing
addresses. Please arrive promptly at the first listing at 11 a.m. Our second
visit begins at 12:30 with a light lunch catered by JD Forde Eatery.
[Paste the two newest listings from the filtered Current Listings query result here, merging
formatting. Then create the following table. Copy the street addresses from the query
to the table, then delete the query results from the Word document. Format with the
appropriate table style, changing font color to black.]
Tour Schedule
* TIP
Time
Street Address
To remove the extra spacing
after the table, click No Spacing
from the Quick Styles.
11:00 a.m.
12:30 p.m.
Please confirm your attendance of this special tour by Wednesday, August 22.
Simply browse to www.mcdonrealty.com/confirm and complete the tour
confirmation form.
We look forward to this preview showing and will welcome your recommenda-
tions in selling these fine listings.
Sincerely
4. Prepare mailing labels (Avery 5160) for the letters using the Access Labels report. Sort
in the same order as the merged letters.
Job 2
Memo
M&D MEMO
You learn today (August 20) that it is your responsibility to determine which listings have
a renewal date set to expire on or before 9/30/2012. Prepare a memo to Ms. McMullan
listing properties with expiring listing agreements.
1. Save the p9-jla database as p9-j2a.
2. Create a query that uses data from the Agents and Property Listings tables.
a. Include the First Name and Last Name from the Agents table and the Property
Number, Street Address, City, Date Listed, Listing Renewal Date, Listing Price,
and Sales Date from the Property Listings table. Do not show the Date Listed ar
Sales Date fields.
b. In the Sales Date field, key Is Null as the criteria to display unsold properties.
In the Listing Renewal Date, key «Last_Name>
«Agency
«Address
«City», «State» «ZIP»
Remove extra space
after paragraphs
«GreetingLine
Agents at McMullan & Donovan Realty are pleased to invite you to a special
preview showing on Tuesday, August 28, of our two newest San Antonio
listings. The table shown below outlines our tour with time and listing
addresses. Please arrive promptly at the first listing at 11 a.m. Our second
visit begins at 12:30 with a light lunch catered by JD Forde Eatery.
[Paste the two newest listings from the filtered Current Listings query result here, merging
formatting. Then create the following table. Copy the street addresses from the query
to the table, then delete the query results from the Word document. Format with the
appropriate table style, changing font color to black.]
Tour Schedule
* TIP
Time
Street Address
To remove the extra spacing
after the table, click No Spacing
from the Quick Styles.
11:00 a.m.
12:30 p.m.
Please confirm your attendance of this special tour by Wednesday, August 22.
Simply browse to www.mcdonrealty.com/confirm and complete the tour
confirmation form.
We look forward to this preview showing and will welcome your recommenda-
tions in selling these fine listings.
Sincerely
4. Prepare mailing labels (Avery 5160) for the letters using the Access Labels report. Sort
in the same order as the merged letters.
Job 2
Memo
M&D MEMO
You learn today (August 20) that it is your responsibility to determine which listings have
a renewal date set to expire on or before 9/30/2012. Prepare a memo to Ms. McMullan
listing properties with expiring listing agreements.
1. Save the p9-jla database as p9-j2a.
2. Create a query that uses data from the Agents and Property Listings tables.
a. Include the First Name and Last Name from the Agents table and the Property
Number, Street Address, City, Date Listed, Listing Renewal Date, Listing Price,
and Sales Date from the Property Listings table. Do not show the Date Listed and
Sales Date fields.
b. In the Sales Date field, key Is Null as the criteria to display unsold
properties.
In the Listing Renewal Date, key