Advanced Guide to Microsoft Access 2007 © 2007 Comma Productions 9090 Chilson Road Brighton, MI 48116 ISBN: 978-0-9800656-9-5 Trademark and Copyright Microsoft Word ® , Microsoft Excel ®, Microsoft Access ®, Microsoft Outlook ®, Microsoft PowerPoint ®, Microsoft Windows ® are trademarks or registered trademark of Microsoft Corporation. Adobe Photoshop® is a trademark or register trademark of Adobe Corporation. Limit of Liability/Disclaimer of Warranty: The Publisher and Author make no representation or warranties with respect to the accuracy or completeness of the contents of this work and specifically disclaim all warranties including without limitations warranties of fitness for a particular purpose. The advice and strategies contained herein may not be suitable for every situation. The fact that an organization is refereed to in this work as a citation and/or potential source of further information does not mean that the authors or publisher endorses the information that the organization or website may provide or recommendations it may make. Readers should be aware that Internet websites listed in this work may have changed or disappeared between when this work was written and when it is read. Neither Comma Productions nor Author shall be liable for any loss of profit or any other commercial damages including but not limited to special, incidental, consequential or other damages. The Comma Project About this course The Advanced Guide to Microsoft Access 2007 Exam 77-605: Using Microsoft® Office Access 2007 Description: The Advanced Guide to Microsoft Access demonstrates how to link information and automate data entry, create a Search Form that uses a Combo Box to filter the records, use Microsoft Access and Word together in a Mail Merge, as well as publish an Access report to Word, optimize a database for multiple users. Who will benefit from this course? Students who want to learn how to design a robust database that support many uses will appreciate The Advanced Guide to Microsoft Access 2007 and the sample databases. Successful students will be able to create tables, forms, Comma Project, LLC. queries and reports that show many-to-many relationships. Books Available in this Series: Audience Description: Who should take this course? Beginning Guide to Microsoft® The certification training is available to adult learners online in partnership with major colleges Access 2007 and universities. The audience for this course includes: • Office workers, managers, entrepreneurs, teachers, and military personnel who want to Intermediate Guide to Microsoft® start using advanced skills immediately Access 2007 • Job training and professional development (WIA), as well as retired and unemployed people looking to expand their job possibilities Advanced Guide to Microsoft® Access 2007 Course Prerequisites: Students who enroll in The Advanced Guide to Microsoft® Access 2007 course should have the equivalent of six months of hands-on computer experience. These students are proficient at file management: save, update, and backup. It is recommended that students complete The Intermediate Guide to Microsoft Access 2007 © 2009 Comma Productions Page 3 of 185 The Comma Project: Courseware Office Specialist Access Excel Outlook PowerPoint Word Vista Online Course Requirements M icrosoft Certified Application Specialist (MCAS) certification training You will need the following Microsoft products already installed on your computer in order to take this course online: Windows Vista Business edition, or Windows XP, For the Microsoft Office 2007 certification course, you MUST have the following software: Word 2007, Excel 2007, Access 2007; Outlook 2007and PowerPoint 2007. Microsoft Office 2007 is NOT the same as Microsoft Office 97-2003. Hardware requirements for Vista Business: · IBM-compatible (PC) computer running   · Processor: 1 GHz 32-bit (x86) or 64-bit (x64)   · RAM: 1 GB of system memory (needs more)   · Hard Drive: 40 GB with at least 15 GB of available space   · Video: Support for DirectX 9 graphics with WDDM Driver   · 128 MB of graphics memory (needs more)   · Pixel Shader 2.0 in hardware   · 32 bits per pixel   · DVD-ROM drive   · Audio Output   Adobe Acrobat Reader (free version) and a Flash Player. An online course requires reliable, effect Internet access. If your internet service provider uses only dial-up, a minimum of 56K connection rate is recommended; however, high speed access (Cable or DSL) is preferred. This course cannot be taken with a Macintosh computer Page 4 of 185 Online Course Requirements Office Specialist Access Excel Outlook PowerPoint Word Vista Microsoft Certified Application Specialist What is the Microsoft Business Certification Program? The Microsoft Business Certification Program enables candidates to show that they have something exceptional to offer – proven expertise in Microsoft Office programs. The two certification tracks allow candidates to choose how they want to exhibit their skills, either through validating skills within a specific Microsoft product or taking their knowledge to the next level and combining Microsoft programs to show that they can apply multiple skill sets to complete more complex office tasks. Recognized by businesses and schools around the world, over 3 million certifications have been obtained in over 100 different countries. The Microsoft Business Certification Program is the only Microsoft-approved certification program of its kind. What is the Microsoft Certified Application Specialist Certification? The Microsoft Certified Application Specialist Certification exams focus on validating specific skill sets within each of the Microsoft® Office system programs. The candidate can choose which For more information: exam(s) they want to take according to which skills they want to validate. The available Application Microsoft Business Specialist exams include: Certification Using Microsoft ®Windows Vista™ Using Microsoft® Office Word 2007 Using Microsoft® Office Excel® 2007 Using Microsoft® Office PowerPoint® 2007 Using Microsoft® Office Access 2007 Using Microsoft® Office Outlook® 2007 Please Note: Comma Project, LLC. is independent from Microsoft Corporation, and not affiliated with Microsoft in any manner. While the Complete Computer Guides may be used in assisting individuals to prepare for a Microsoft Business Certification exam, Microsoft, its designated program administrator, and Comma Project, LLC. do not warrant that use of this Complete Computer Guides will ensure passing a Microsoft Business Certification exam. Page 5 of 185 MOS Lessons Office Specialist Access Excel Outlook PowerPoint Word Vista Exam 77-605: Using Microsoft® Office Access 2007 M icrosoft Certified Application Specialist (MCAS) reference topics Description: The Microsoft Certified Application Specialist program is the Microsoft Certified only comprehensive, performance-based certification program approved Application Specialist by Microsoft to validate business computer skills using Microsoft Windows (MCAS) objectives for Vista® and Microsoft Office® 2007 productivity software: Excel, Word, Access 2007 Power Point, Access, and Outlook. Study Guides Objectives: At the end of The Beginning Guide to Microsoft Access 2007, Beginning Access the student will be able to: Use and design tables, forms, and queries in Intermediate Access Microsoft Access, create Queries to find and select records by criteria, Advanced Access write and test update queries. At the end of The Intermediate Guide to Microsoft Access 2007, the student will be able to: Create forms with subforms to represent one-to- many relationships, use criteria, parameters, concatenation, and operators in Query designs: calculate with DateDif(),Left(), and IIF(), improve report designs with Sorting and Grouping. At the end of The Advanced Guide to Microsoft Access 2007, the student will be able to: link information and automate data entry, create a Search Form that uses a Combo Box to filter the records, use Microsoft Access and Word together in a Mail Merge, as well as publish an Access report to Word, optimize a database for multiple users. Page 6 of 185 Exam 77-605: Using Microsoft® Office Access 2007 MS Office Certification Access Beginning Access Intermediate Access Advanced I Access Advanced II Microsoft Access 2007 Study Guide Mic rosoft Certified Application Spe c ialist (MCAS): Microsoft Access 20 0 7 Exam 77-605 Guide topics 1. Structuring a Database 2. Formatting Database Elements 3. Entering and Modifying Data Multi-value fields, 137 Create blank databases, 159 Data: Find using criteria, 139 Link or import data from external Create tables by copying the structure of Attachments: Attach multiple files, 92 sources, 113 other tables, 134 Attachments: Attach files to records, 92 Define table relationships, 122 Modify table properties, 136 Attachments: Detach files, 92 Create relationships, 123 Tables: Delete, 112 Attachments: Export files, 92 One to many, 124 Tables: Add a Total row, 39 Attachments: Navigate, 91 Modify relationships, 48 Fields and field properties, 45 Import Microsoft Excel data, 160 Print table relationships, 125 Fields: Yes/No, 135 Import CSV data, 162 Split databases, 106 Fields: Memo, 7 Link to Excel spreadsheets, 163 To overcome size restrictions, 119 Field properties: Allow zero length, 77 Import data: Save and run import Field properties: Set Memo field as For multi-user applications, 118 specifications, 161 append. only, 136 MultiValue fields: possible issues, 137 Fields: Attachment fields, 89 Fields: Attachment fields (multiple), 91 Fields: Attachment field design, 90 Forms: Datasheet View, 22 Forms: Subforms, 44 Forms: Pivot Table, 85 Reports: Use the Wizard, 74 Reports: Use Design View, 75 Reports: Define Group Headers, 76 Reports: Create aggregate fields, 99 Report Print Layout options, 82 Report Print Layout- Keep Together, 82 Reports: Label Wizard, 93 Page 7 of 185 Microsoft Access Certification Requirements MS Office Certification Access Beginning Access Intermediate Access Advanced I Access Advanced II Microsoft Access 2007 Study Guide Mic rosoft Certified Application Spe c ialist (MCAS): Microsoft Access 20 0 7 Exam 77-605 Guide topics 4. Creating and Modifying Queries 5. Presenting and Sharing Data 6. Maintaining Databases Queries: Add all fields to queries, 143 Sort data within tables, 80 Back up databases, 108 Query: Find Duplicates, 147 Sort data within reports, 80 Object dependencies, 126 Queries: Based on more than one Filter data within queries, 37 table, 37 Filter data within queries, 101 Queries: Determine which table, 58 Filter data within forms, 52 Query: Find Unmatched, 142 Charts: Create charts, 85 Query: MAKE TABLE, 141 Charts: Format charts, 86 Query: APPEND, 140 Charts: Change chart types, 86 Query: DELETE, 149 Export data from tables, 150 Query: Subquery, 56 Export data from queries, 151 Filters: Save queries as filters, 55 Export data: Save and run export Query design: Add a second copy of a specifications, 151 table, 148 Print database objects, 126 Query design: Criteria, 39 Print all, 127 Query: Hard-coded criteria, 56 Print selections, 129 Query parameters, 41 Query joins: INNER, 146 Query joins: LEFT, 20 Query joins: RIGHT, 146 Query design: Use arithmetic, 59 Page 8 of 185 Microsoft Access Certification Requirements Microsoft Office Certification Beginning Access Intermediate Access Advanced Access Table of Contents Advanced Guide to Microsoft® Access 2007 Independent Investigation Search and Research Take Control: The Producers Create a Search Form Page 11 Synchronized Forms Page 35 Office Automation Page 63 Overview Page 12 Design Considerations Page 36 Think Outside Box Page 64 Create a Search Table Page 14 Synchronized Combos Page 37 Mail Merge Main Document Page 68 Recycle Existing Form Page 15 Query Totals/Group By Page 39 Mail Merge Data Source Page 69 Use a query for a List Form Page 19 Link a Query to A Form Page 40 Edit the Merge Fields Page 70 Modify the Query Joins Page 20 The Expression Builder Page 41 Mail Merge Results Page 73 Make a List Form Page 22 Enter Parameter Value Page 42 Access Report Options Page 74 Use the Datasheet View Page 23 Create the Rating Combo Page 43 Build an Address Block Page 75 Change the Record Source Page 24 Link Form and Subform Page 44 Use the Report Headers Page 76 Link the Form and Subform Page 27 AfterUpdate Event Page 45 Grow/Shrink Property Page 77 Refresh the Record Page 29 Double Click Event Page 47 Add a Group Footer Page 80 AfterUpdate() Event Page 31 Filter by Form Page 52 Force a New Page Page 82 Save a Filter as a Query Page 55 Export to Microsoft Word Page 83 Create a Subquery Page 56 Create a PivotChart Page 84 Calculate the Discount Page 59 Attaching files to a record Page 89 The Label Wizard Page 93 Page 9 of 185 Advanced Microsoft Access Microsoft Office Certification Beginning Access Intermediate Access Advanced Access Table of Contents, continued Advanced Guide to Microsoft® Access 2007 2. Downloads 2007 Level 17 Database Sample Tech Talk: The Administrator: Rave Reviews 2007 Level 17 Database The Performance Network Links to the Internet Page 130 Complete Linked Tables Page 103 Act I, Scene I Page 131 2007 Mail Merge Sample Overview Page 104 Strategies for archiving data Page 133 Network Considerations Page 105 Copy a table Page 134 3. Assessment Performance Page 106 Memo and Multi-value fields Page 135 Split the database Page 107 Create an Update Query Page 139 Practice Activities Get External Data Page 113 Create a Make Table Query Page 141 Complete the Online Quiz for Linked Tables Page 115 Find Unmatched Query Page 142 Advanced Microsoft Access Linked Table Manager Page 116 Find Duplicates Query Page 144 Split the Database Page 119 Create a Delete Query Page 148 Optional Skill Test: Download Define Relationships Page 122 Export to Excel Page 150 the instructions. You can use this Document the Database Page 125 Office Integration Page 152 practice to assess your progress. Print Database Objects Page 127 Import from Excel Page 162 Try it: Download these files. 1. Right-Click on the link. The SandBox: From the Director 2. Click SAVE TARGET AS, What a Long, Strange Trip It's Been 3. Browse to your folder Page 168 4. Click on SAVE Sample Access Databases These samples are Zipped (compressed) to improve your download. However, you will need to Unzip them before you can begin programming. Page 10 of 185 Advanced Microsoft Access