Access 2007: The Missing Manual : The Missing Manual

Compared to industrial-strength database products such as Microsoft's SQL Server, Access is a breeze to use. It runs on PCs rather than servers and is ideal for small- to mid-sized businesses and households. But Access is still intimidating to learn. It doesn't help that each new version crammed in yet another set of features; so many, in fact, that even the pros don't know where to find them all. Access 2007 breaks this pattern with some of the most dramatic changes users have seen since Office 95. Most obvious is the thoroughly redesigned user interface, with its tabbed toolbar (or "Ribbon") that makes features easy to locate and use. The features list also includes several long-awaited changes. One thing that hasn't improved is Microsoft's documentation. To learn the ins and outs of all the features in Access 2007, Microsoft merely offers online help. Access 2007: The Missing Manual was written from the ground up for this redesigned application. You will learn how to design complete databases, maintain them, search for valuable nuggets of information, and build attractive forms for quick-and-easy data entry. You'll even delve into the black art of Access programming (including macros and Visual Basic), and pick up valuable tricks and techniques to automate common tasks -- even if you've never touched a line of code before. You will also learn all about the new prebuilt databases you can customize to fit your needs, and how the new complex data feature will simplify your life. With plenty of downloadable examples, this objective and witty book will turn an Access neophyte into a true master. Access 2007: The Missing Manual 1 Table of Contents 2 Copyright 6 The Missing Credits 7 Introduction 10 Part I: Storing Information in Tables 31 Chapter 1. Creating Your First Database 32 Section 1.1. Understanding Access Databases 33 Section 1.2. Getting Started 34 Section 1.3. Saving and Opening Access Databases 53 Section 1.4. The Navigation Pane 63 Chapter 2. Building Smarter Tables 68 Section 2.1. Understanding Data Types 69 Section 2.2. Design View 71 Section 2.3. Access Data Types 77 Section 2.4. The Primary Key 104 Section 2.5. Six Principles of Database Design 107 Chapter 3. Mastering the Datasheet: Sorting, Searching, Filtering, and More 113 Section 3.1. Datasheet Customization 114 Section 3.2. Datasheet Navigation 122 Section 3.3. Advanced Editing 134 Section 3.4. Printing the Datasheet 143 Chapter 4. Blocking Bad Data 147 Section 4.1. Data Integrity Basics 148 Section 4.2. Input Masks 158 Section 4.3. Validation Rules 168 Section 4.4. Lookups 179 Chapter 5. Linking Tables with Relationships 185 Section 5.1. Relationship Basics 186 Section 5.2. Using a Relationship 193 Section 5.3. More Exotic Relationships 211 Section 5.4. Relationship Practice 220 Part II: Manipulating Data with Queries 229 Chapter 6. Queries That Select Records 230 Section 6.1. Query Basics 231 Section 6.2. Creating Queries 232 Section 6.3. Queries and Related Tables 255 Chapter 7. Essential Query Tricks 268 Section 7.1. Calculated Fields 269 Section 7.2. Query Functions 278 Section 7.3. Summarizing Data 298 Section 7.4. Query Parameters 308 Chapter 8. Queries That Update Records 311 Section 8.1. Understanding Action Queries 312 Section 8.2. Update Queries 314 Section 8.3. Append Queries 322 Section 8.4. Delete Queries 329 Section 8.5. Tutorial: Flagging Out-of-Stock Orders 332 Chapter 9. Analyzing Data with Crosstab Queries and Pivot Tables 337 Section 9.1. Understanding Crosstab Queries 338 Section 9.2. Creating Crosstab Queries 342 Section 9.3. Pivot Tables 351 Section 9.4. Pivot Charts 368 Part III: Printing Reports 374 Chapter 10. Creating Reports 375 Section 10.1. Report Basics 378 Section 10.2. Printing, Previewing, and Exporting a Report 390 Section 10.3. Formatting a Report 397 Section 10.4. Filtering and Sorting a Report 398 Chapter 11. Designing Advanced Reports 401 Section 11.1. Improving Reports in Design View 402 Section 11.2. The Report Wizard 416 Section 11.3. The Label Wizard 420 Section 11.4. Fine-Tuning Reports with Properties 426 Section 11.5. Expressions 430 Section 11.6. Grouping 432 Part IV: Building a User Interface with Forms 446 Chapter 12. Creating Simple Forms 447 Section 12.1. Form Basics 448 Section 12.2. Sorting and Filtering in a Form 463 Section 12.3. Creating Better Layouts 469 Section 12.4. The Form Wizard 484 Chapter 13. Designing Advanced Forms 487 Section 13.1. Customizing Forms in Design View 488 Section 13.2. Taking Control of Controls 510 Section 13.3. Forms and Linked Tables 523 Chapter 14. Building a Navigation System 528 Section 14.1. Mastering the Navigation Pane 529 Section 14.2. Building Forms with Navigation Smarts 541 Section 14.3. Linking to Related Data 556 Part V: Programming Access 566 Chapter 15. Automating Tasks with Macros 567 Section 15.1. Macro Essentials 568 Section 15.2. Macros and Security 578 Section 15.3. Three Macro Recipes 587 Section 15.4. Managing Macros 593 Section 15.5. Connecting Macros to Forms 598 Section 15.6. Conditional Macros 606 Chapter 16. Automating Tasks with Visual Basic 614 Section 16.1. The Visual Basic Editor 615 Section 16.2. Putting Code in a Form 621 Section 16.3. Understanding Objects 629 Section 16.4. Using Objects 638 Chapter 17. Writing Smarter Code 649 Section 17.1. Exploring the VB Language 650 Section 17.2. Dealing with Trouble 664 Section 17.3. Deeper into Objects 671 Section 17.4. Using VB to Run a Better Business 678 Part VI: Sharing Access with the Rest of the World 697 Chapter 18. Sharing a Database with Multiple Users 698 Section 18.1. Opening Up Your Database to the World 699 Section 18.2. Preparing Your Database 702 Section 18.3. Playing Well with Others 719 Section 18.4. Data Corruption 727 Section 18.5. Securing Your Database 729 Chapter 19. Importing and Exporting Data 736 Section 19.1. Case for Importing and Exporting 737 Section 19.2. Using the Clipboard 740 Section 19.3. Import and Export Operations 745 Section 19.4. Access and XML 760 Section 19.5. Collecting Info by Email 771 Chapter 20. Connecting Access to SQL Server 783 Section 20.1. Should You Switch to SQL Server? 784 Section 20.2. Getting Started: SQL Server 2005 Express 789 Section 20.3. Creating a SQL Server Database 796 Section 20.4. Adding Objects to a SQL Server Database 806 Chapter 21. Connecting Access to SharePoint 820 Section 21.1. Understanding SharePoint 821 Section 21.2. Setting Up SharePoint 826 Section 21.3. SharePoint and Access 835 Part VII: Appendix 856 Appendix A. Customizing the Quick Access Toolbar 857 Section A.1. The Quick Access Toolbar 858 Colophon 864 Index 865 SYMBOL 866 A 868 B 872 C 874 D 879 E 884 F 887 G 892 H 894 I 895 J 897 K 898 L 899 M 901 N 904 O 906 P 908 Q 911 R 913 S 917 T 921 U 924 V 925 W 927 X 929 Y 930 Z 931 A comprehensive guide to Access 2007 helps users become comfortable with the new user interface and tabbed toolbar, as well as learn how to design complete databases, maintain them, write queries, search for data, and build attractive forms for quick-and-easy data entry.