Database Concepts Capstone - Access

Capstone Project 3: Chapters 8 & 9 Page 1 of 4

Capstone Project # 3: Chapters 8 & 9 For this project, you enhance the functionality of a database that tracks daily price information for a collection of stocks. You export the data from a report to HTML and use mail merge to create labels. Next, you analyze the table structure and performance of the database. You back up and then split the database and lock the front-end database. Yo will conclude the project by customizing your working environment within Access.

[Student Learning Outcomes: 8.4, 8.6, 9.1, 9.3, 9.4, 9.5].

Files Needed: StockTracking-06.accdb Completed Project File Names: [your initials] Capstone3.accdb, [your initials] StockPriceSummary.html, [your initials] StockPriceSummaryPage2.html, [your initials] StockPriceSummaryPage3.html, [your initials] StockLabelsMerge.docx, [your initials] StockLabels.docx, [your initials] Capstone3_Backup.accdb, [your initials] Capstone3_be.accdb and [your initials] Capstone3.accde

Skills Covered in this Project • Export to HTML. • Use mail merge to create labels. • Save settings in a mail merge document. • Use the Table Analyzer tool. • Back up a database. • Use the Performance Analyzer tool. • Use the Database Splitter tool. • Lock a database. • Reset customizations to the Ribbon and the Quick

Access toolbar.

• Add commands to the Quick Access toolbar.

• Remove tabs from the Ribbon. • Change application title in Access. • Customize Office account settings.

1. Open the StockTracking-06.accdb database file from your student data files and save it as [your initials] Capstone3.accdb.

2. Enable content in the database. 3. Export the StockPriceSummary report to HTML. Since this report is designed with a tabular layout,

the exported result should display correctly. a. Start the export by selecting the StockPriceSummary report and selecting HTML Document as

the export option. Use [your initials] StockPriceSummary.html as the file name. b. Choose to Open the destination file after the export operation is complete. The Export data

with formatting and layout option should already be selected and dimmed. Click OK. c. Click OK to accept the choices in the HTML Output Options dialog box. The file opens in your

default browser (Figure 1).

CITC 1303: Database Concepts Section C01, Summer 2021

Capstone Project 3: Chapters 8 & 9 Page 2 of 4

d. Scroll down to the bottom of the browser page and click the Next link.

e. The browser updates to display the next page of the report.

f. Close the browser tab. g. Close the wizard without saving the

export steps. 4. Navigate to the folder on your computer

where you saved the HTML file. Note that the export process created three separate HTML files, since the report in Access contained three pages. The links you saw in step 3d were automtically added into the file.

5. Use mail merge to create mailing labels. a. Select the Stocks table and click Word

Merge. b. Choose to Create a new document and then link the data to it. c. Select Labels in the Mail Merge pane in the Word document. You are creating identification

labels to put on file folders. Advance to the next step. d. Choose Label options. Select Avery US Letter as the Vendor and 2224 Print or Write Multi-Use

Labels as the Product number. Click OK. e. Choose to view gridlines around the table cells. Advance to the next step. f. Verify that the Stocks table displays as the data source. Advance to the next step. g. Position the insertion point in the upper left of the first label. Click the Paragraph launcher

[Home tab, Paragraph group]. Change the Before Spacing to 4 pt. Click OK. h. Type Symbol: . Be sure to enter a space after the colon. i. Click the More items link to open the Insert Merge Field dialog box, select the StockSymbol

field, click Insert, and click Close. The merge field is inserted into the document. j. Apply bold formatting to the Symbol: text and merge field. k. Click to the right of the StockSymbol merge field and press Ctrl+Tab. l. Click the Paragraph launcher. Click the Tabs button to open the Tabs dialog box. m. Enter 3.8” in the Tab stop position and select Right in the Alignment area. Click OK. n. Type Exchange: (including a space after the colon) and insert the Exchange field from the

More items option. Apply bold formatting. o. Continue adding the text and merge fields so that

your label matches the one shown in Figure 2. Note that the StockName merge field has a Center alignment and a 12 pt font size.

Figure 1, StockPriceSummary report exported to HTML, first page

Figure 2, Text and merge fields in label

Capstone Project 3: Chapters 8 & 9 Page 3 of 4

p. Update all labels and advance to the next step. The document updates to show how the merged data appears in a label (Figure 3).

q. Complete the merge. r. Choose to Edit individual labels and merge all the

records. s. Save the completed merged labels as [your

initials] StockLabelsMerge.docx and close the document.

t. Save a copy of the main document as [your initials] StockLabels.docx to preserve the merge settings. Close the document.

6. Analyze the Stocks table. a. Click the Analyze Table button to open the Table

Analyzer Wizard. b. Advance past the information windows. c. Select the Stocks table and the Yes, let the wizard decide

radio button when prompted. d. Enlarge the tables in the

recommendation so you can read all of the content (Figure 4).

e. The wizard proposes moving the Sector field into a separate table and moving the Exchange field into a separate table (Table 2 and Table 3). This proposed solution will not reduce redundancy since the lookup field will simply replace the Sector or Exchange field. Do not continue with this recommendation and click the Back button two times to return to the wizard screen where you can select a table.

7. Analyze the HistoricalPrices table. a. Select the HistoricalPrices table and the Yes, let the wizard decide radio button when

prompted. A dialog box displays informing you that the wizard doesn’t recommend any splitting of the table.

b. Click the Cancel button to exit the wizard. 8. Use the Save As command to back up the database before analyzing the performance. Save the file

as [your initials]Capstone3_Backup.accdb. 9. Analyze the performance of the database.

a. Click the Analyze Performance button to open the Performance Analyzer. b. Analyze all of the objects in the database. c. Review the one Idea displayed in the Analysis Results window.

Figure 4, Table Analyzer Wizard recommendations for Stocks table

Figure 3, Preview of merged labels

Capstone Project 3: Chapters 8 & 9 Page 4 of 4

d. Don’t implement the idea. e. Close the Analyzer.

10. Split the database. Accept the default file name of [your initials] Capstone3_be.accdb. Since you are not password protecting the back-end database, you do not need to update links in the front- end database.

11. Lock the front-end database. a. Click the File tab to open the Backstage view and click the Save As button. b. Click the Make ACCDE button in the Advanced grouping of the Save Database As section and

click the Save As button. The Save As dialog box displays. c. Select the location to store the file, accept the suggested file name, and click the Save button.

The .accde file is created. d. Close the database.

12. Open the locked database. a. Open the [your initials] Capstone3.accde database file. If a Microsoft Access Security Notice

displays, click Open. b. Click the Create tab. Notice that the Forms and Reports groups are dimmed.

13. Reset and customize the Ribbon and the Quick Access toolbar. a. Reset all customizations to the Ribbon and the Quick Access toolbar. b. Add Open and Quick Print to the Quick Access toolbar for this database only. c. Remove the External Data tab so it doesn’t display on the Ribbon.

14. Change the application title for the current database to read [Your Initials] Stock Tracking.

15. Customize Office account settings. a. Open the account information area on the Backstage view. b. Select an Office Background and Office Theme of your choice.

16. Close the database and exit Access.