Table of Contents

Acknowledgments     xvii
Introduction     xix
Get Started
Get Acquainted with Access 2007     3
Start Access and Open a Database     4
Take a Tour of the Access Window     6
Open a Database     7
Tour the Navigation Pane and the Object Window     12
Look at the Ribbon     16
Use Shortcut Menus     18
Open a Table     18
Take a Tour of the Datasheet View     19
Check Out the Subdatasheet     22
Get Help When You Need It     24
Use Microsoft Access Help Window     24
Ask What's This?     25
Get Help with What You're Doing     26
Create a Database     27
Design an Efficient Database     28
Determine the Goals of the Database     30
Distribute the Data Among the Tables     30
Identify the Data Fields     31
Specify Key Fields     31
Define Table Relationships     33
Complete the Database     34
Create a Database with a Template     36
Run the New Application     41
Start with a Blank Database     42
Create and Modify Tables     45
Create a New Table from a Template     46
Create a New Table in Datasheet View     46
Add Fields to the New Table     47
Use a Field Template     49
Add Fields from an Existing Table     50
Save the New Table     51
Create a Table from Scratch in Design View     52
Tour the Table Design View     52
Add Fields     53
Choose a Primary Key     63
Create Other Indexes     64
Save the Table Design     66
Modify the Table Design     67
Switch Table Views     67
Add or Delete Fields     67
Change the Field Order     68
Change a Field Name or Type     69
Change a Field Size     69
Modify or Delete the Primary Key     70
Ensure Data Validity     71
Define Field Validation Rules     72
Define a Record Validation Rule     73
Require an Entry and Prevent Duplicates     74
Handle Blank Fields     74
Assign a Default Value     76
Copy an Existing Table Structure     77
Relate Tables     79
Define a Relationship     80
Use the Relationships Window     80
Use the Field List Pane     91
View and Edit Relationships     93
Hide or Delete a Table     93
Modify or Delete a Relationship     93
Change a Table Design from the Relationships Window     94
Print the Relationships     94
Enter and Edit Data     97
Enter New Data     98
Copy and Move Data     98
Insert Pictures     103
Insert Hyperlinks     105
Attaching Files to a Field     108
Customize Data Entry     110
Add Custom Input Masks     110
Create Lookup Fields     113
Change the Datasheet Appearance     119
Move and Resize Columns and Rows     119
Freeze and Hide Columns     121
Change the Font     122
Change Gridlines and Cells     123
Set Datasheet Default Options     124
Change Table Definition     125
Insert/Delete a Column     125
Change Field Names     126
Insert/Delete a Subdatasheet in Design View     126
Find and Edit Record Data     127
Locate Records     127
Find and Replace Data      131
Delete Data     132
Retrieve and Present Information
Sort, Filter, and Print Records     135
Sort Records     136
Sort on a Single Field     136
Sort by Two or More Fields     137
Save the Sort Order     137
Filter Records     137
Filter by Context     139
Use the Filter Command     142
Filter By Selection     143
Filter By Form     146
Filter with Advanced Filter/Sort     152
Save a Filter     153
Remove and Clear Filters     155
Preview and Print Sorted or Filtered Table Data     155
Extract Information with Queries     157
Create a Select Query     159
Use the Simple Query Wizard     160
Tour the Query Design Window     162
Without the Wizard     164
Relate Multiple Tables in a Query     165
Add/Remove Fields     168
Run and Save the Query     171
Hide and Show Fields     172
Specify the Record Order     173
Show Highest or Lowest Values     174
Add Selection Criteria     175
Use Wildcards and Operators      176
Use a Single Criterion     176
Use Multiple Criteria     177
Get Help from the Expression Builder     180
Set Query Properties     182
Modify a Query     183
Insert a Field and Change the Field Order     183
Change Field Properties     183
Perform Calculations in a Query     185
Add a Calculated Field     185
Summarize with the Wizard     188
Summarize with Aggregate Functions     188
Summarizing in Datasheet View     192
Create Special Queries with the Query Wizard     193
Create a Find Duplicates Query     193
Create a Find Unmatched Query     194
Create a Crosstab Query     195
Create Advanced Queries     199
Create Special Purpose Queries     200
Parameter Queries     200
AutoLookup Queries     204
Design Action Queries     206
Update Query     206
Make-Table Query     210
Append Query     211
Delete Query     213
Look at Structured Query Language (SQL)     215
Review SQL Statements     215
Create a Subquery      217
Define a Criterion     217
Define a New Field     218
Understand Form Report Design Basics     221
Use Simple Form and Simple Report Tools     222
Common Form and Report Design Elements     224
Understand Controls     224
Work in the Design Window     225
Start a New Design     225
Tour the Design Window     228
Add Controls     232
Starting a New Form in Layout View     238
Modify Controls     238
Select Controls and Other Objects     239
Move and Resize Controls     243
Align and Space Controls     245
Use Property Sheets     246
Use the Font Group     250
Format Conditionally     251
Change a Control Type     253
Delete Controls     254
Modify Form or Report Properties     254
Change the Record Source     254
Apply Filters and Sort Orders     256
Use AutoFormat     257
Create Custom Forms and Subforms     259
Create a New Form Design     260
Use the Form Wizard     260
Create a Form Without the Wizard     263
Modify the Form Design     264
Add Form Header and Footer Sections     264
Place and Customize Data-Related Controls     266
Add Yes/No Controls     275
Add User-Interactive Controls     278
Use the Form for Data Entry     282
Navigate in the Form     282
Change the Tab Order     283
Locate Records     284
Sort and Filter Data in a Form     284
View Multiple Records     285
Create a Multiple-Page Form     285
Add a Page Break     285
Add a Tab Control     287
Customize a Tab Control     288
Add Special Controls     288
Add Calculated Controls     288
Add an AutoDialer Control     289
Create a Hierarchical Form     290
Use the Form Wizard     291
Use the Subform Wizard     293
Use the Hierarchical Form     296
Modify a Subform     296
Add Custom User Guidance     297
Add Data Validation     298
Validate with Properties     298
Validate with Events     299
Create and Customize Reports and Subreports     301
Start a New Report      302
Use the Report Tool     303
Use the Report Wizard     304
Preview and Print the Report     308
Work in the Print Preview Window     309
Print the Report     311
Modify the Report Design     313
Examine the Report Sections     314
Set Report and Section Properties     314
Change the Report Style     316
Add Page Numbers and Date/Time Controls     316
Save the Report Design     317
Filter, Sort, and Group Records in Layout View     318
Filter Records in Layout View     319
Change the Sort Order     320
Add Group Sections     322
Modify and Add Groups     326
Create a Summary Report with the Report Wizard     329
Print an Alphabetic Index     332
Add a Subreport     335
Create a Subreport with the Report Wizard     335
Create a Subreport Control     336
Insert an Existing Subreport     339
Link the Report and Subreport     339
Modify a Subreport Control     340
Design a Multiple-Column Report     340
Print Mailing Labels     342
Use the Label Wizard      343
Create Charts and Graphs     347
Choose a Chart Type     348
Create a New Chart with the Chart Wizard     348
Select the Data for the Chart     348
Use the Microsoft Chart Wizard     352
Save the Chart     354
Link the Chart to Record Data     355
Add an Existing Chart to a Form or Report     356
Modify the Chart     358
Modify with Access     358
Edit with Microsoft Graph     364
Improve the Access 2007 Workplace
Customize the Workplace     375
Personalize the Workplace     376
Work with Objects in the Navigation Pane     376
Using the Ribbon     380
Create a Shortcut     380
Set Access Options     381
Popular Options     382
Set Options for the Current Database     384
Set Datasheet Options     388
Set Object Designers Options     388
Set Proofing Options     393
Set Advanced Options     395
Customize the Toolbar     401
View and Manage Add-Ins     402
Choose Trust Center Options     404
Search Additional Resources     405
Customize the Status Bar     406
Speed Up Your Database     407
Optimize a Database     408
Use the Analyzer Wizards     408
Optimize Tables and Queries     414
Optimize Filter By Form     416
Optimize Forms and Reports     416
Optimize Controls     418
Back Up and Restore a Database     418
Compact and Repair a Database     419
Automate with Macros     423
Create a Simple Macro     424
Choose Macro Actions     426
Set Action Arguments     427
Test and Debug a Macro     429
Start the Macro     429
Step Through a Macro     430
Modify a Macro     431
Assign a Macro to an Event Property     432
Decide Which Event to Use     433
Add Conditions to a Macro     434
Create a Macro to Display a Warning     435
Create Other Commonly Used Macros     437
Set Control Values and Properties     438
Change the Flow of Operations     441
Filter Records     443
Create an AutoExec Macro     444
Create a Macro Group     445
Customize the User Interface      449
Work with the Ribbon     450
Resize the Ribbon     450
Hide and Restore the Ribbon     451
Use Keyboard Shortcuts for Ribbon Commands     451
Customize the Quick Access Toolbar     452
Move the Quick Access Toolbar     452
Add Commands to the Toolbar     452
Remove Commands from the Toolbar     457
Use Existing Customization     458
Show Startup Switchboard     458
Use Custom menus and toolbars     458
Create a Custom Dialog Box     460
Design the Form     461
Create and Attach the Macros     464
Create a Dialog Box for User Input     469
Set the Input Form Properties     469
Create the Macros     470
Modify the Query     470
Customize the Navigation Pane and Create Switchboards     473
View Objects in the Navigation Pane     474
Change Categories and Groups     474
Hide/Restore Groups and Objects     480
Search for an Object     482
Customize the Navigation Pane     484
Plan the Custom Groups     484
Hide/Restore Custom Groups and Objects     489
Create Switchboards     490
Use the Switchboard Manager to Create Switchboards     491
Modify the Switchboard     498
Exchange Data with Others
Exchange Database Objects and Text     505
Copy Objects among Access Databases     506
Copy and Paste     506
Drag and Drop     507
Import or Link Access Data     508
Import Objects     509
Set Import Options     512
Link Access Tables     513
Import from or Link to Other Data Sources     515
Use Data from dBASE or Paradox     516
Work with Linked or Imported Tables     517
Rename a Linked Table in Access     517
Change Linked Table Properties     517
Update Links with the Linked Table Manager     518
Unlink Tables     520
Import and Link Text Files     520
Use Delimited Text Files     520
Use Fixed-Width Text Files     526
Change Import Specifications     527
Export to an Existing Access Database     529
Export to Another Database Format     530
Export to Text Files     531
Exchange Data with Outside Sources     537
Copy or Move Records      538
Copy or Move Data from a Word Processor     538
Copy or Move Data from a Spreadsheet     540
Copy or Move Records from Access to Another Application     541
Save Access Output as an External File     542
Work with Word     545
Save in Rich Text Format     545
Use Merge It with Microsoft Word     546
Work with Excel     548
Import from and Link to Excel Spreadsheets     549
Export a Table or Query to Excel     554
Mailing Access Objects     555
Share with Multiple Users     557
Share a Database on a Network     558
Share an Entire Database     558
Split the Database     559
Prevent Exclusive Access     563
Manage the Database in a Multiuser Environment     564
Control Data Editing     565
Update Records with Refresh and Requery     567
Edit Shared Database Objects     568
Secure a Database     571
New Security Measures     572
Enable/Disable Database Content     572
Encrypt the Database     573
Encrypt with a Password     574
Use the Trust Center      575
Create a Trusted Environment     575
Trust Macros     578
Trust Add-Ins     580
Security with Earlier Version Databases     581
Create a Certificate     582
Code-sign the Database     583
Convert to Access 2007     585
Decide on a Conversion Strategy     586
Convert a Database to Access 2007     586
Convert a Workgroup Information File (MDW)     588
Convert a Secured Database     588
Convert a Replicated Database     588
Convert to an Earlier Version     589
Open an Earlier Database     589
Share a Database Across Several Access Versions     590
Index     591