Microsoft Access 2002 Bible BK+CD

Cary N. Prague, Wiley

ISBN:076453596X, Edition: , 2001-07-15

Price: $49.99

Table of Contents

Preface ~ ix

Acknowledgments ~ xv

Part I: First Things First 1

Chapter 1: What Is Access 2002? ~ 3

Access Is ~ 3
What Access Offers ~ 7
True relational database management ~ 7
Context-sensitive Help and the Office Assistant ~ 8
Ease-of-use Wizards ~ 9
Importing, exporting, and linking external table files ~ 9
WYSIWYG forms and reports ~ 10
Multiple-table queries and relationships ~ 11
Business graphs and charts ~ 13
DDE and OLE capabilities ~ 14
The Internet is also accessible ~ 14
True Client/Server for Everyone ~ 15
Built-in functions ~ 15
Macros: Programming without programming ~ 16
Modules: Visual Basic for Applications-database programming ~ 16
Information for Database Users ~ 17
The Windows environment ~ 18
GUI environment ~ 18
Event-driven environment ~ 19
Programming by exception ~ 19
Information for Spreadsheet Users ~ 20
Database manipulation ~ 21

Chapter 2: Installing and Running Access 2002 ~ 23

Determining What You Need ~ 23
Hardware requirements ~ 24
Software requirements ~ 24
Upgrading to Access 2002 from Access 2.0, 95, 97, or 2000 ~ 25
Installing Access 2002 or Office XP ~ 25
Activating Your Product ~ 35
Activating by using the Internet ~ 36
Activating by using the telephone ~ 38
Converting Access 1.x, 2.0, 95, 97, and 2000 Files ~ 39
Troubleshooting ~ 42
Getting Started with Access 2002 ~ 42
Starting from the Windows Start menu ~ 42
Starting from an Access shortcut icon ~ 43
Starting Access from the Start menu ~ 43
Starting from an Access icon created in the Office folder ~ 44
Starting from Windows Explorer ~ 45
Options for starting Access ~ 46
Exiting Access ~ 48
Getting Help ~ 49
Office Assistant ~ 49
Using the new Menu bar Help ~ 51
Standard Help ~ 52
Screen Tips (What’s This?) ~ 53
Web-based resources on the Microsoft Web site ~ 53
Sample databases ~ 54

Chapter 3: A Hands-on Tour of Access 2002 ~ 55

Touring the Access Window ~ 55
Using the mouse and the keyboard ~ 55
The Access window ~ 55
The Database window ~ 58
Design windows ~ 60
A Simple Access Session ~ 62
Opening a database ~ 64
Opening a table ~ 65
Displaying and moving around in a datasheet ~ 66
Viewing a table design ~ 70
Displaying a form ~ 72
Creating a query ~ 74
Displaying a report ~ 78
Ready for More? ~ 80

Chapter 4: Database Concepts and Design ~ 81

The Database Terminology of Access ~ 82
What Is a Database? ~ 82
Databases, tables, records, fields, and values ~ 83
Using More Than One Table ~ 85
Working with multiple tables ~ 85
Why You Should Create Multiple Tables ~ 86
Access Database Objects and Views ~ 87
Datasheets ~ 87
Queries and dynasets ~ 87
Data-entry and display forms ~ 88
Reports ~ 89
Designing the Systems Objects ~ 89
The Seven-Step Design Method ~ 89
Step 1: The Overall Design-From Concept to Reality ~ 90
Conceptual design ~ 91
Interviewing the user ~ 91
The Process of prototyping ~ 92
Step 2: Report Design-Placing Your Fields ~ 93
Laying out fields in the report ~ 93
The Pets and Owners Directory ~ 94
The Monthly Invoice Report ~ 95
Step 3: Data Design-What Fields Do You Have? ~ 97
Determining customer information ~ 98
Determining pet information ~ 98
Determining visit information ~ 99
Combining the data ~ 100
Step 4: Table Design and Relationships ~ 103
Database Normalization ~ 103
The four primary tables of the system ~ 106
Relating the four primary tables of the system to each other ~ 107
Step 5: Field Design Data-Entry Rules and Validation ~ 108
Designing field names, types, and sizes ~ 108
Designing data-entry rules ~ 109
Designing lookup tables ~ 109
Creating test data ~ 111
Step 6: Form Design-Input ~ 111
Designing data-entry screens ~ 111
The Customer form ~ 112
The Pets form ~ 113
The General Visits form ~ 114
The Visit Details form ~ 114
Step 7: Automation Design-Menus ~ 115

Part II: Creating the Objects 117

Chapter 5: Creating Database Tables ~ 119

Creating the Pets Table ~ 119
Creating a Database ~ 120
Templates dialog box ~ 121
Blank database ~ 123
The Database Window ~ 125
Objects menu bar ~ 126
Groups menu bar ~ 127
The Database window toolbar buttons ~ 128
The Access window toolbar ~ 128
Creating a New Table ~ 129
The table design process ~ 130
The New Table dialog box ~ 130
Creating a new table with a Datasheet view ~ 132
The Table Design Window ~ 135
Using the Table Design window toolbar ~ 135
Working with fields ~ 135
Completing the Pets Table ~ 139
Changing a Table Design ~ 141
Inserting a new field ~ 141
Deleting a field ~ 141
Changing a field location ~ 142
Changing a field name ~ 142
Changing a field size ~ 142
Changing a field data type ~ 143
Understanding Field Properties ~ 144
Entering field-size properties ~ 146
Using formats ~ 147
Entering formats ~ 151
Entering input masks ~ 152
The Input Mask Wizard ~ 154
Entering decimal places ~ 154
Creating a caption ~ 155
Setting a default value ~ 155
Working with validation ~ 155
Understanding the Lookup Property window ~ 157
Determining the Primary Key ~ 158
Creating a unique key ~ 158
Creating the primary key ~ 159
The Indexes window ~ 160
The Table Properties window ~ 160
Printing a Table Design ~ 161
Saving the Completed Table ~ 162
Manipulating Tables in a Database Window ~ 162
Renaming tables ~ 162
Deleting tables ~ 163
Copying tables in a database ~ 163
Copying a table to another database ~ 164

Chapter 6: Setting Relationships Between Tables ~ 165

Tables Used in the Mountain Animal Hospital Database ~ 165
Understanding Keys ~ 168
Deciding on a primary key ~ 169
Benefits of a primary key ~ 170
Creating a primary key ~ 172
Understanding foreign keys ~ 173
Understanding Relations Between Tables ~ 174
A review of relationships ~ 174
Understanding the four types of table relationships ~ 174
Understanding Referential Integrity ~ 177
Creating Relationships ~ 177
Using the Relationship window ~ 178
Creating relationships between tables ~ 180
Specifying relationship options in the Edit Relationships dialog box ~ 181
Saving the relationships between tables ~ 184
Adding another relationship ~ 184
Deleting an existing relationship ~ 184
Join lines in the Relationships window ~ 184
Creating the relationships for the Mountain Animal Hospital system ~ 185
Using the Access Table Analyzer ~ 186
Starting the Table Analyzer ~ 187
Selecting a table to analyze ~ 189
Analyzing the table ~ 189
Changing the table and field definitions ~ 192
Changing the key fields ~ 194
Searching for typos and duplicate key data ~ 196
Completing Table Analyzer ~ 197
Using the Lookup Wizard in the Table Designer ~ 199
Using Subdatasheets ~ 205
Setting up subdatasheets ~ 207

Chapter 7: Working with Information in Tables ~ 211

Understanding Datasheets ~ 211
The Datasheet Window ~ 213
Moving within a datasheet ~ 214
The Navigation buttons ~ 214
The Datasheet toolbar ~ 215
Opening a Datasheet ~ 218
Entering New Data ~ 218
Saving the record ~ 220
Understanding automatic data-type validation ~ 220
Understanding how properties affect data entry ~ 221
Navigating Records in a Datasheet ~ 224
Moving between records ~ 225
Finding a specific value ~ 225
Changing Values in a Datasheet ~ 228
Replacing an existing value manually ~ 228
Changing an existing value ~ 229
Fields that you can’t edit ~ 230
Using the Undo Feature ~ 230
Copying and Pasting Values ~ 231
Replacing Values ~ 231
Adding New Records ~ 232
Deleting Records ~ 233
Adding, Changing, and Deleting Columns ~ 234
Deleting a column from a datasheet ~ 234
Adding a column to a datasheet ~ 234
Changing a field name (column header) ~ 234
Displaying Records ~ 235
Changing the field order ~ 235
Changing the field display width ~ 236
Changing the record display height ~ 237
Displaying cell gridlines ~ 238
Changing display fonts ~ 239
Hiding and unhiding columns ~ 241
Freezing columns ~ 241
Saving the changed layout ~ 241
Saving a record ~ 242
Sorting and Filtering Records in a Datasheet ~ 242
Using the QuickSort feature ~ 242
Using Filter by Selection ~ 243
Using Filter by Form ~ 244
Printing Records ~ 245
Printing the datasheet ~ 246
Using the Print Preview window ~ 246

Chapter 8: Creating and Using Simple Data-Entry Forms ~ 249

Understanding Data-Entry Forms ~ 249
What are the basic types of forms? ~ 250
How do forms differ from datasheets? ~ 252
Creating a form with AutoForm ~ 252
Creating a Form with Form Wizards ~ 254
Creating a new form ~ 254
Selecting the New Form type and data source ~ 255
Choosing the fields ~ 255
Choosing the form layout ~ 257
Choosing the style of the form ~ 258
Creating a form title ~ 259
Completing the form ~ 259
Changing the Design ~ 260
Using the Form Window ~ 262
The Form toolbar ~ 262
Navigating between fields ~ 262
Moving between records in a form ~ 263
Displaying Your Data with a Form ~ 263
Working with pictures and OLE objects ~ 264
Memo field data entry ~ 265
Switching to a datasheet ~ 265
Saving a Record and the Form ~ 266
Printing a Form ~ 266
Using the Print Preview Window ~ 266

Chapter 9: Understanding and Creating Simple Queries ~ 269

Understanding Queries ~ 269
What is a query? ~ 270
Types of queries ~ 271
Query capabilities ~ 272
How dynasets work ~ 273
Creating a Query ~ 274
Selecting a table ~ 275
Using the Query window ~ 275
Navigating the Query Design window ~ 276
Using the Query Design toolbar ~ 277
Using the QBE pane of the Query Design window ~ 278
Selecting Fields ~ 278
Adding a single field ~ 278
Adding multiple fields ~ 280
Adding all table fields ~ 281
Displaying the Dynaset ~ 282
Working with the datasheet ~ 283
Changing data in the query datasheet ~ 283
Returning to the query design ~ 283
Working with Fields ~ 283
Selecting a field ~ 283
Changing field order ~ 284
Resizing columns in design mode ~ 285
Removing a field ~ 286
Inserting a field ~ 286
Changing the field display name ~ 286
Showing table names ~ 287
Showing a field ~ 287
Changing the Sort Order ~ 288
Specifying a sort ~ 288
Sorting on more than one field ~ 289
Displaying Only Selected Records ~ 290
Understanding record criteria ~ 290
Entering simple character criteria ~ 291
Entering other simple criteria ~ 292
Printing a Query Dynaset ~ 293
Saving a Query ~ 294

Chapter 10: Creating and Working with Simple Reports ~ 295

Understanding Reports ~ 295
What types of reports can you create? ~ 295
The difference between reports and forms ~ 298
The process of creating a report ~ 298
Creating a Report with Report Wizards ~ 301
Creating a new report ~ 302
Choosing the data source ~ 303
Choosing the fields ~ 303
Selecting the grouping levels ~ 304
Defining the group data ~ 305
Selecting the sort order ~ 305
Selecting summary options ~ 306
Selecting the layout ~ 306
Choosing the style ~ 308
Opening the report design ~ 308
Using the Print Preview window ~ 309
Viewing the Report Design window ~ 310
Printing a Report ~ 311
Saving the Report ~ 312
Creating a Report with AutoReport ~ 312

Part III: Using Access in Your Work 315

Chapter 11: Using Operators, Functions, and Expressions ~ 317

What Are Operators? ~ 317
Types of operators ~ 317
When are operators used? ~ 318
Mathematical operators ~ 318
Relational operators ~ 321
String operators ~ 323
Boolean (logical) operators ~ 326
Miscellaneous operators ~ 329
Operator precedence ~ 330
What Are Functions? ~ 331
Using functions in Access ~ 331
Types of functions ~ 333
What Are Expressions? ~ 336
The parts of an expression ~ 337
Creating an expression ~ 338
Special identifier operators and expressions ~ 339

Chapter 12: Creating Relations and Joins in Queries ~ 343

Adding More than One Table to a Query ~ 343
Working with the Table/Query Pane ~ 345
The join line ~ 345
Manipulating the Field List window ~ 346
Resizing the Table/Query pane ~ 347
Moving a table ~ 348
Removing a table ~ 349
Adding more tables ~ 349
Resizing a Field List window ~ 349
Creating a database diagram ~ 350
Adding Fields from More than One Table ~ 351
Adding a single field ~ 351
Viewing the table names ~ 352
Adding multiple fields at the same time ~ 353
Adding all table fields ~ 353
Understanding the Limitations of Multiple-Table Queries ~ 354
Updating limitations ~ 354
Overcoming query limitations ~ 356
Creating and Working with Query Joins ~ 357
Joining tables ~ 358
Deleting joins ~ 360
Understanding Types of Table Joins ~ 360
Inner joins (Equi-joins) ~ 361
Changing join properties ~ 362
Inner and outer joins ~ 363
Creating a Cartesian product ~ 367

Chapter 13: Working with Select Queries ~ 369

Moving Beyond Simple Queries ~ 369
Using query comparison operators ~ 370
Understanding complex criteria selection ~ 371
Using functions in select queries ~ 375
Referencing fields in select queries ~ 375
Entering Single-Value Field Criteria ~ 375
Entering character (Text or Memo) criteria ~ 376
The Like operator and wildcards ~ 377
Specifying non-matching values ~ 380
Entering numeric (Number, Currency, or Counter) criteria ~ 381
Entering Yes/No (logic) criteria ~ 382
Entering a criterion for an OLE object ~ 382
Entering Multiple Criteria in One Field ~ 383
Understanding an Or operation ~ 383
Specifying multiple values for a field using the Or operator ~ 383
Using the Or: cell of the QBE pane ~ 385
Using a list of values with the In operator ~ 385
Understanding an And query ~ 386
Specifying a range using the And operator ~ 386
Using the Between ~ And operator ~ 387
Searching for Null data ~ 388
Entering Criteria in Multiple Fields ~ 389
Using And and Or across fields in a query ~ 389
Specifying And criteria across fields of a query ~ 390
Specifying Or criteria across fields of a query ~ 391
Using And and Or together in different fields ~ 391
A complex query on different lines ~ 392
A complex query on one line ~ 393
Creating a New Calculated Field in a Query ~ 393

Chapter 14: Understanding Form Controls ~ 395

What Is a Control? ~ 396
The different control types ~ 396
Understanding bound, unbound, and calculated controls ~ 399
Standards for Using Controls ~ 400
Label controls ~ 401
Text box controls ~ 402
Toggle buttons, option buttons, and check boxes ~ 402
Option groups ~ 404
Buttons in rectangles ~ 405
List boxes ~ 406
Combo boxes ~ 407
Tab controls ~ 407
Creating New Controls ~ 408
The two ways to add a control ~ 409
Dragging a field name from the Field List window ~ 410
Creating unbound controls with the toolbox ~ 412
Selecting Controls ~ 413
Deselecting selected controls ~ 413
Selecting a single control ~ 414
Selecting multiple controls ~ 414
Manipulating Controls ~ 415
Resizing a control ~ 415
Moving a control ~ 415
Aligning controls ~ 417
Sizing controls ~ 419
Grouping controls ~ 419
Deleting a control ~ 420
Attaching a label to a control ~ 421
Copying a control ~ 421

Chapter 15: Creating and Customizing Data-Entry Forms ~ 423

Creating a Standard Data-Entry Form ~ 423
Assembling the data ~ 424
Creating a new blank form and binding it to a query ~ 426
Defining the form display size ~ 427
Understanding Properties ~ 429
Working with control properties ~ 430
Working with form properties ~ 434
Placing Bound Fields on the Form ~ 444
Displaying the field list ~ 444
Selecting the fields for your form ~ 444
Dragging fields onto your form ~ 445
Working with Label Controls and Text Box Controls ~ 447
Creating unattached labels ~ 447
Modifying the text in a label or text control ~ 447
Modifying the format of text in a control ~ 448
Sizing a text box control or label control ~ 449
Moving label and text controls ~ 450
Modifying the appearance of multiple controls ~ 451
Changing the control type ~ 452
Setting the tab order ~ 452
Adding multiple-line text box controls for Memo fields ~ 453
Adding a bound object frame to the form ~ 455
Using Fields from Multiple Tables in a Form ~ 457
Adding fields from a second table ~ 457
Working with attached label and text controls ~ 459
Creating a calculated field ~ 460
Changing the updatability of a multiple-table form ~ 462
Creating a Multiple-Page Form ~ 463
Why use multiple-page forms? ~ 464
Adding a page break ~ 465
Using Form and Page Headers and Footers ~ 467
The different types of headers and footers ~ 467
Creating a form header and footer ~ 468
Printing a Form ~ 469
Converting a Form to a Report ~ 470

Chapter 16: Creating Great Looking Forms ~ 471

Making a Good Form Look Great ~ 471
Understanding WYSIWYG ~ 472
Using the formatting windows and toolbar ~ 472
Creating special effects ~ 474
Changing the forms background color ~ 476
Enhancing Text-Based Controls ~ 477
Enhancing label and text box controls ~ 477
Creating a text shadow ~ 477
Changing text to a reverse video display ~ 479
Displaying label or text box control properties ~ 479
Displaying Images in Forms ~ 483
Working with Lines and Rectangles ~ 485
Emphasizing Areas of the Form ~ 486
Adding a shadow to a control ~ 486
Changing the header dividing line ~ 487
Adding a Background Bitmap ~ 487
Using AutoFormat ~ 489
Customizing and adding new AutoFormats ~ 491
Copying individual formats between controls ~ 491

Chapter 17: Adding Data-Validation Controls to Forms ~ 493

Creating Data-Validation Expressions ~ 493
Table-level validation ~ 494
Form-level validation ~ 495
Entering a validation expression ~ 496
Creating Choices with Option Buttons ~ 496
Creating option groups ~ 497
Creating an option group box ~ 497
Creating Yes/No Options ~ 501
Creating check boxes ~ 502
Creating visual selections with toggle buttons ~ 504
Adding a bitmapped image to the toggle button ~ 504
Working with List Boxes and Combo Boxes ~ 505
The differences between list boxes and combo boxes ~ 505
Settling real-estate issues ~ 506
Creating a single-column list box ~ 506
Understanding list box properties ~ 508
Creating a multiple-column list box ~ 510
Hiding a column in a list box ~ 512
Creating multi-selection list boxes ~ 512
Creating and Using Combo Boxes ~ 512
Creating a single-column combo box ~ 513
Creating a multiple-column combo box from a query ~ 515

Chapter 18: Using OLE Objects, Graphs, Pivot Tables/Charts, and ActiveX Controls ~ 519

Understanding Objects ~ 519
Types of objects ~ 520
Using bound and unbound objects ~ 520
Linking and embedding ~ 521
Embedding Objects ~ 523
Embedding an unbound object ~ 523
Embedding bound objects ~ 528
Adding a bound OLE object ~ 528
Adding a picture to a bound object frame ~ 529
Editing an embedded object ~ 530
Linking Objects ~ 531
Linking a bound object ~ 531
Creating a Graph or Chart ~ 534
The different ways to create a graph ~ 534
Customizing the Toolbox ~ 535
Embedding a Graph in a Form ~ 535
Assembling the data ~ 536
Adding the graph to the form ~ 536
Customizing a Graph ~ 543
Understanding the Graph window ~ 543
Working with attached text ~ 544
Changing the graph type ~ 548
Changing axis labels ~ 549
Changing a bar color, pattern, and shape ~ 550
Modifying gridlines ~ 551
Manipulating three-dimensional graphs ~ 552
Integration with Microsoft Office ~ 554
Checking the spelling of one or more fields and records ~ 555
Correcting your typing automatically when entering data ~ 556
Using OLE automation with Office 2002 ~ 557
Creating an Excel type Pivot Table ~ 557
Creating a Pivot Chart ~ 564
Using the Calendar ActiveX Control ~ 566

Chapter 19: Creating and Customizing Reports ~ 571

Starting with a Blank Form ~ 571
The Design Window Toolbar ~ 573
Banded Report Writer Concepts ~ 574
How sections process data ~ 575
The Report Writer sections ~ 577
Creating a New Report ~ 581
Eleven tasks to creating a great report ~ 583
Designing the report ~ 583
Assembling the data ~ 584
Creating a new report and binding it to a query ~ 586
Defining the report page size and layout ~ 587
Placing fields on the report ~ 589
Resizing a section ~ 592
Working with unattached label controls and text ~ 593
Working with text boxes and their attached label controls ~ 595
Changing label and text box control properties ~ 607
Formatting the display of text controls ~ 609
Growing and shrinking text box controls ~ 610
Sorting and grouping data ~ 611
Adding page breaks ~ 617
Saving your report ~ 621

Chapter 20: Presentation-Quality Reports and Printing ~ 623

Making Reports Presentation Quality ~ 623
Understanding WYSIWYG Printing ~ 626
Enhancing Text-Based Controls ~ 628
Enhancing label controls ~ 628
Working with multiple-line text box controls ~ 632
Adding New Controls ~ 633
Displaying values with option groups and option buttons ~ 634
Creating the option group ~ 634
Creating an option group with a calculated control ~ 636
Displaying Yes/No values with check boxes ~ 640
Displaying values as toggle buttons ~ 641
Displaying bound OLE objects in reports ~ 643
Displaying an image in a report ~ 643
Working with Lines and Rectangles ~ 644
Emphasizing Areas of the Report ~ 646
Adding background shading ~ 646
Sinking controls ~ 646
Etched controls ~ 647
Creating a shadow on a rectangle ~ 647
Changing text to a reverse video display ~ 647
Seeing Your Output in Different Ways ~ 648
Using the Print Preview window ~ 648
Using layout previews ~ 651
Printing a report ~ 651

Chapter 21: Creating Calculations and Summaries in Reports ~ 655

Designing a Multilevel Grouping Report with Totals ~ 655
Designing the Invoice Report ~ 657
Designing and creating the query for the report ~ 659
Designing test data ~ 662
Creating a Multilevel Grouping Report with Totals ~ 663
Creating the sorting orders ~ 664
Creating the detail section ~ 666
Creating the detail section controls ~ 666
Testing the detail section ~ 668
Creating the Pet ID header and footer sections ~ 669
Creating the Pet ID header controls ~ 670
Creating the Pet ID footer controls ~ 671
Creating the Customer Number header and footer sections ~ 675
Creating the Customer Number header controls ~ 676
Creating the Customer Number footer controls ~ 678
Creating the Visit Date header ~ 681
Creating the page header controls ~ 682
Creating the page footer controls ~ 684
Calculating percentages using totals ~ 685
Calculating running sums ~ 686
Creating a title page in a report header ~ 687
Using the report footer ~ 688

Part IV: Advanced Access Database Topics 689

Chapter 22: Working with External Data ~ 691

Access and External Data ~ 691
Types of external data ~ 691
Methods of working with external data ~ 692
Should you link to or import data? ~ 693
Linking External Data ~ 696
Types of database management systems ~ 696
Linking to other Access database tables ~ 699
Linking to dBASE and FoxPro databases (tables) ~ 700
Linking to Paradox tables ~ 703
Linking to non-database tables ~ 705
Splitting an Access database into two linked databases ~ 709
Working with Linked Tables ~ 712
Setting view properties ~ 712
Setting relationships ~ 713
Setting links between external tables ~ 713
Using external tables in queries ~ 714
Renaming tables ~ 716
Optimizing linked tables ~ 716
Deleting a linked table reference ~ 717
Viewing or changing information for linked tables ~ 717
Importing External Data ~ 718
Importing other Access objects ~ 719
Importing non-Access PC-based database tables ~ 720
Importing spreadsheet data ~ 722
Importing from word-processing files ~ 725
Importing text file data ~ 725
Importing HTML tables ~ 736
Modifying imported table elements ~ 736
Troubleshooting import errors ~ 736
Exporting to External Formats ~ 738
Exporting objects to other Access databases ~ 738
Excel, HTML, or text files ~ 739

Chapter 23: Working with Advanced Select Queries ~ 741

Creating Queries That Calculate Totals ~ 742
Showing and hiding the Total: row in the QBE pane ~ 742
The Total: row options ~ 743
Performing totals on all records ~ 746
Performing totals on groups of records ~ 748
Specifying criteria for a total query ~ 751
Creating expressions for totals ~ 754
Creating Crosstab Queries ~ 757
Understanding the crosstab query ~ 757
Creating the crosstab query ~ 758
Entering multiple-field row headings ~ 760
Specifying criteria for a crosstab query ~ 761
Specifying fixed column headings ~ 765
The Crosstab Query Wizard ~ 766
Creating a Parameter Query ~ 767
Understanding the parameter query ~ 768
Creating a single-parameter query ~ 768
Running a parameter query ~ 768
Creating a multiple-parameter query ~ 770
Specifying parameter order ~ 771

Chapter 24: Working with Action Queries ~ 773

About Action Queries ~ 773
Types of action queries ~ 773
Uses of action queries ~ 774
The Process of action queries ~ 775
Viewing the results of an action query ~ 775
Reversing action queries ~ 776
Creating Action Queries ~ 777
Creating an Update Action Query to Change Values ~ 777
Creating a new table using a make-table query ~ 781
Creating a Query to Append Records ~ 785
Creating a query to delete records ~ 790
Creating other queries using the Query Wizards ~ 794
Saving an action query ~ 798
Running an action query ~ 799
Troubleshooting Action Queries ~ 799
Data-type errors in appending and updating ~ 799
Key violations in action queries ~ 799
Record-locked fields in multi-user environments ~ 800
Text fields ~ 800

Chapter 25: Advanced Query Topics ~ 801

Using Lookup Tables and Joins ~ 801
Using Calculated Fields ~ 805
Finding the Number of Records in a Table or Query ~ 809
Finding the Top (n) Records in a Query ~ 811
SQL-Specific Queries ~ 813
Creating union queries ~ 813
Creating pass-through queries ~ 815
Creating data definition queries ~ 816
Creating SQL subqueries in an Access query ~ 817
How Queries Save Field Selections ~ 817
Hiding (not showing) fields ~ 818
Renaming fields in queries ~ 819
Hiding and unhiding columns in the QBE pane ~ 820
Query Design Options ~ 821
Setting Query Properties ~ 823
Understanding SQL ~ 826
Viewing SQL statements in queries ~ 826
An SQL primer ~ 827

Chapter 26: Working with Subforms ~ 833

What Is a Subform? ~ 833
Creating Subforms with the Form Wizard ~ 835
Creating the form and selecting the Form Wizard ~ 835
Choosing the fields for the main form ~ 835
Selecting the table or query that will be the subform ~ 836
Choosing the fields for the subform ~ 837
Selecting the form data layout ~ 838
Selecting the subform layout ~ 839
Selecting the form style ~ 839
Selecting the form title ~ 840
Displaying the form ~ 840
Displaying the main form design ~ 842
Displaying the subform design ~ 843
Creating a Simple Subform Without Wizards ~ 845
Creating a form for a subform ~ 846
Adding the subform to the main form ~ 849
Linking the form and subform ~ 851
Adding lookup tables to the subform fields ~ 852
Creating totals in subforms ~ 859

Chapter 27: Creating Mail Merge and Label Reports ~ 863

Creating Mailing Labels Using the Label Wizard ~ 863
Selecting the label size ~ 864
Selecting the font and color ~ 865
Creating the mailing-label text and fields ~ 866
Sorting the mailing labels ~ 867
Displaying the labels in the Print Preview window ~ 869
Modifying the label design in the Report Design window ~ 870
Printing labels ~ 872
Creating Snaked Column Reports ~ 873
Creating the report ~ 874
Defining the page setup ~ 875
Printing the snaked column report ~ 876
Creating Mail Merge Reports ~ 877
Assembling data for a mail merge report ~ 879
Creating a mail merge report ~ 880
Creating the page header area ~ 880
Working with embedded fields in text ~ 882
Printing the mail merge report ~ 884
Using the Access Mail Merge Wizard for Microsoft Word ~ 884

Part V: Access Application Tools 891

Chapter 28: Working with Macros and Events ~ 893

Understanding Macros ~ 893
What is a macro? ~ 894
When to use a macro ~ 894
The Macro Window ~ 895
The Action pane ~ 896
The Argument pane ~ 896
Creating a Macro ~ 897
Entering actions and arguments ~ 898
Adding multiple actions to a macro ~ 901
Rearranging macro actions ~ 902
Deleting macro actions ~ 903
Editing existing macros ~ 903
Copying entire macros ~ 904
Renaming macros ~ 904
Running Macros ~ 904
Running a macro from the Macro window ~ 904
Running a macro from the Database window ~ 905
Running a macro from any window in the database ~ 905
Running a macro from another macro ~ 905
Running a macro automatically when you open a database ~ 905
Macro Groups ~ 906
Creating macro groups ~ 907
Running a macro in a macro group ~ 908
Supplying Conditions for Actions ~ 909
What is a condition? ~ 909
Activating the Condition column in a macro ~ 909
Specifying a condition for a single action ~ 910
Specifying a condition for multiple actions ~ 912
Controlling the flow of actions ~ 913
Troubleshooting Macros ~ 913
Single-step mode ~ 913
The Action Failed dialog box ~ 914
Understanding Events ~ 915
What is an event? ~ 915
How do events trigger actions? ~ 915
Where to trigger macros ~ 916

Chapter 29: Using Macros in Forms and Reports ~ 917

Types of Macros ~ 917
Macros for forms ~ 919
Macros for reports ~ 921
Macros for importing and exporting ~ 921
Macros for Keyboard accelerators ~ 921
Form-Level Event Macros ~ 922
Attaching macros to forms ~ 922
Opening a form with a macro ~ 925
Attaching a macro to a form ~ 926
Synchronizing two forms with On Current ~ 927
Running a macro when closing a form ~ 930
Confirming a delete with On Delete ~ 932
Control Event Macros ~ 936
Attaching macros to controls ~ 936
Working with Macros on Forms ~ 938
Validating data ~ 938
Setting values ~ 939
Navigating in forms and records ~ 941
Filtering records ~ 942
Finding records ~ 944
Report Event Macros ~ 947
Opening a report with a macro ~ 948
Deactivating a section of a report with a macro ~ 948
Creating a Report Snapshot macro ~ 949
Report Section Macros ~ 951
Using On Format ~ 952
Using On Print ~ 952
Using On Retreat ~ 952
Report Properties ~ 953
Using Format Count ~ 953
Using Print Count ~ 953
Working with macros in reports ~ 954
Underlining data in a report with a macro ~ 954
Hiding data in a report with a macro ~ 957
Filtering records for a report with a macro ~ 957
Macros for Importing and Exporting ~ 957
Using command buttons to import or export ~ 957
Creating Keyboard Accelerators (Hot Keys) ~ 958
Creating a hot-key combination ~ 959

Chapter 30: Working with Visual Basic in Access 2002 ~ 961

Instant Applications Using the Access 2002 Database Wizard ~ 961
Getting started ~ 962
Working with tables and fields ~ 964
Selecting AutoFormat styles ~ 965
Customizing by selection ~ 966
Using the switchboard system ~ 967
Customizing the switchboards ~ 968
Creating Programs in Visual Basic for Applications ~ 970
Understanding events and event procedures ~ 970
Creating a new module ~ 972
Migrating from Macros to Visual Basic ~ 973
When to use macros and when to use Visual Basic procedures ~ 973
Converting existing macros to Visual Basic ~ 975
Using the Command Button Wizard to create Visual Basic code ~ 989

Chapter 31: Using Visual Basic in Forms and Reports ~ 993

Understanding Modules ~ 993
Form and report modules ~ 994
Standard modules ~ 995
Using Variables ~ 996
Naming variables ~ 997
Declaring variables ~ 997
Working with Data Types ~ 1001
Understanding Visual Basic Logical Constructs ~ 1003
Conditional processing ~ 1003
Repetitive looping ~ 1009
Planning for Runtime Errors ~ 1014
Adding error-handling code to a procedure ~ 1014
Displaying meaningful runtime error messages ~ 1017
Trapping for specific error codes ~ 1018
Filtering Records in Procedures ~ 1018
Displaying a dialog box for selecting a record ~ 1019
Selecting a set of records to print ~ 1023
Printing records by using selection criteria ~ 1025
Working with Combo-Box and List-Box Controls ~ 1027
Handling a combo box entry that is not in the list ~ 1028
Handling MultiSelect list boxes ~ 1031
Creating Programs to Update a Table ~ 1035
Updating fields in a record using ADO and DAO ~ 1036
Updating a total field for a record ~ 1041
Adding a new record ~ 1044
Deleting a record ~ 1046

Chapter 32: Creating Switchboards, Command Bars, Menus, Toolbars, and Dialog Boxes ~ 1049

Switchboards and Command Buttons ~ 1049
Using a switchboard ~ 1050
Creating the basic form for a switchboard ~ 1051
Working with command buttons ~ 1051
Creating command buttons ~ 1053
Linking a command button to a macro ~ 1058
Adding a picture to a command button ~ 1062
Creating Custom Menu Bars, Toolbars, and Shortcut Menus ~ 1065
Understanding command bars ~ 1066
Creating custom menu bars with command bars ~ 1066
Changing existing menus and toolbars ~ 1068
Creating a new menu bar ~ 1069
Attaching the menu bar to a form ~ 1076
Creating shortcut menus ~ 1078
Creating and using custom toolbars ~ 1082
Attaching the toolbar to a form ~ 1084
Adding control tips to any form control ~ 1085
Running a macro automatically when you start Access ~ 1086
Controlling options when starting Access ~ 1086
Creating a Print Report Dialog Box Form and Macros ~ 1087
Creating a form for a macro ~ 1088
Creating the option group ~ 1089
Creating command buttons ~ 1090
Creating a list box on the print report form ~ 1091
Creating the print macros ~ 1093
Creating the Print macro group ~ 1093
Using the Access 2002 Tab Control ~ 1103
Creating a new form with the Access 2002 tab control ~ 1103
Access 2002 Tab control properties ~ 1106

Part VI: Access and the External World 1111

Chapter 33: Moving from Jet to the SQL Server 2000 Desktop Engine ~ 1113

Understanding SQL Server 2000 Desktop Engine ~ 1113
Comparing SQL Server 2000 Desktop Engine and Jet ~ 1114
Choosing the right database engine ~ 1115
Installing SQL Server 2000 Desktop Engine ~ 1117
Hardware requirements ~ 1117
Software requirements ~ 1117
Running the SQL Server 2000 Desktop Engine Installation Program ~ 1117
Customizing the installation of SQL Server 2000 Desktop Engine ~ 1118
Starting the SQL Server 2000 Desktop Engine ~ 1119
Creating a SQL Server 2000 Desktop Engine Database ~ 1121
Creating a project ~ 1121
Understanding projects ~ 1126
Project objects ~ 1127
Creating a New Table ~ 1128
Working with fields in the Table Design window ~ 1128
The Table Properties window ~ 1131
Working with Database Diagrams ~ 1138
Using the Upsizing Wizard ~ 1142
Before upsizing an application ~ 1143
Starting the Upsizing Wizard ~ 1143

Chapter 34: Working with Access Projects ~ 1151

Upsizing to an Access Project ~ 1152
Starting the Upsizing Wizard ~ 1152
Using the Upsizing Wizard to create a client/server application ~ 1154
Understanding Project Queries ~ 1158
Creating views ~ 1158
Creating stored procedures ~ 1161
Creating user-defined functions ~ 1165
Using Unbound Forms ~ 1166
Working with unbound forms ~ 1167
Creating an unbound form ~ 1168
Displaying data on the form ~ 1170
Updating data ~ 1173
Finding a record ~ 1176

Chapter 35: Using and Creating Access Objects for Intranets and the Internet ~ 1179

Using the Web Toolbar with Access 2002 ~ 1181
The Web toolbar ~ 1182
Types of Web Pages That Access Can Create ~ 1184
Data access pages ~ 1184
Working with dynamic and static views of Web-based data ~ 1185
Exporting Tables, Queries, Forms, and Reports to Web Pages ~ 1189
Exporting an Access table to static HTML format ~ 1190
Exporting an Access query datasheet to static HTML format ~ 1192
Exporting an Access form datasheet to static HTML format ~ 1194
Changing Page Setup properties for datasheets ~ 1198
Exporting a datasheet to dynamic HTML format ~ 1198
Exporting a form to dynamic HTML format ~ 1200
Processing an IDC/HTX file on the Web server ~ 1201
Processing ASP files on the Web server ~ 1202
Exporting a report to static HTML format ~ 1202
HTML template files ~ 1205
Importing and Linking (Read-Only) HTML Tables and Lists ~ 1208
Importing an HTML table ~ 1208
Linking to an HTML table ~ 1210
Using Hyperlinks to Connect Your Application to the Internet ~ 1210
Using the Hyperlink data type ~ 1211
Adding a hyperlink to a form, report, or datasheet ~ 1212
Creating a label using the Insert Hyperlink button ~ 1214
Browsing Web Pages with the Web Browser Control ~ 1215

Chapter 36: Building Web Applications, Data Access Pages, and XML ~ 1217

Working with the Data Access Pages ~ 1217
What is a data access page? ~ 1218
Creating a single table data access page ~ 1221
Working with multiple tables and grouped pages ~ 1230
Changing some key properties on data access pages ~ 1244
Saving other Access objects as data access pages ~ 1255
XML Data and Access ~ 1265
Understanding XML ~ 1266
Exporting to XML ~ 1267
Importing XML data ~ 1270

Appendix A: Access 2002 Specifications ~ 1273
Appendix B: Mountain Animal Hospital Tables ~ 1279
Appendix C: What’s on the CD-ROM ~ 1287
Appendix D: Chapter Cross-Reference Guide ~ 1297

Index ~ 1305

Coupons ~ 1343
Hungry Minds Inc. End-User License Agreement ~ 1348
Installation Instructions ~ 1350