How to Do Everything with Microsoft Office Access 2003

Cover......Page 1
Contents......Page 10
Acknowledgments......Page 20
Introduction......Page 22
PART I Get Started......Page 26
1 Get Acquainted with Access 2003......Page 28
Start Access and Open a Database......Page 29
Take a Tour of the Access Window......Page 30
Open a Database......Page 31
Open the Sample Database......Page 34
Tour the Database Window......Page 36
Look at Menu Options and Toolbar Buttons......Page 37
Use Shortcut Menus......Page 38
Open a Table......Page 39
Take a Tour of the Datasheet View......Page 40
Check Out the Subdatasheet......Page 42
Get Help When You Need It......Page 43
Ask a Question......Page 44
Use the Microsoft Access Help Task Pane......Page 46
Get Help with What You’re Doing......Page 48
2 Create a Database......Page 50
Design an Efficient Database......Page 51
Distribute the Data Among the Tables......Page 53
Identify the Data Fields......Page 54
Specify Key Fields......Page 55
Define Table Relationships......Page 56
Complete the Database......Page 57
Preview the Database Templates......Page 58
Work with the Wizard......Page 59
Run the New Application......Page 63
Start with a Blank Database......Page 64
3 Create and Modify Tables......Page 66
Choose a Table and Add Fields......Page 67
Set the Primary Key......Page 69
Relate to Existing Tables......Page 70
Tour the Table Design View......Page 72
Add Fields......Page 73
Choose a Primary Key......Page 82
Create Other Indexes......Page 83
Create a Table in Datasheet View......Page 85
Save the Table Design......Page 86
Add or Delete Fields......Page 87
Change a Field Name or Type......Page 88
Change a Field Size......Page 89
Ensure Data Validity......Page 90
Define Field Validation Rules......Page 91
Define a Record Validation Rule......Page 92
Handle Blank Fields......Page 94
Copy an Existing Table Structure......Page 95
4 Relate Tables......Page 98
Use Ready-Made Relationships......Page 99
Use the Relationships Window......Page 100
Modify or Delete a Relationship......Page 110
Print the Relationships......Page 111
5 Enter and Edit Data......Page 114
Copy and Move Data......Page 115
Insert Pictures......Page 118
Insert Hyperlinks......Page 121
Add Custom Input Masks......Page 124
Create Lookup Fields......Page 128
Move and Resize Columns and Rows......Page 131
Freeze and Hide Columns......Page 133
Change the Font......Page 134
Change Grid Lines and Cell Appearance......Page 135
Set Datasheet Default Options......Page 136
Insert/Delete a Subdatasheet......Page 137
Edit Record Data......Page 138
Locate Records......Page 139
Delete Data......Page 143
PART II Retrieve and Present Information......Page 146
6 Sort, Filter, and Print Records......Page 148
Sort on a Single Field......Page 149
Filter Records......Page 150
Filter By Selection......Page 151
Filter By Form......Page 153
Filter with Advanced Filter/Sort......Page 158
Save a Filter......Page 160
Preview and Print Sorted or Filtered Table Data......Page 161
7 Extract Information with Queries......Page 164
Create a Select Query......Page 166
Use the Simple Query Wizard......Page 167
Tour the Query Design Window......Page 168
Without the Wizard......Page 170
Relate Multiple Tables in a Query......Page 171
Add/Remove Fields......Page 173
Run and Save the Query......Page 174
Hide and Show Fields......Page 175
Specify the Record Order......Page 176
Show Highest or Lowest Values......Page 177
Use Wildcards and Operators......Page 178
Use Multiple Criteria......Page 179
Get Help from the Expression Builder......Page 181
Set Query Properties......Page 183
Insert a Field and Change the Field Order......Page 184
Change Field Properties......Page 185
Add a Calculated Field......Page 186
Summarize with the Wizard......Page 188
Summarize in the Query Design......Page 189
Create a Find Duplicates Query......Page 191
Create a Find Unmatched Query......Page 192
Create a Crosstab Query......Page 194
8 Create Advanced Queries......Page 198
Parameter Queries......Page 199
AutoLookup Queries......Page 202
Update Query......Page 203
Append Query......Page 206
Delete Query......Page 207
Make-Table Query......Page 208
Review SQL Statements......Page 210
Define a Criterion......Page 212
Define a New Field......Page 213
9 Understand Form and Report Design Basics......Page 214
Use AutoForm and AutoReport Features......Page 215
Common Form and Report Design Elements......Page 216
Understand Controls......Page 217
Start a New Design......Page 218
Tour the Design Window......Page 219
Add Controls......Page 222
Select Controls and Other Objects......Page 227
Move and Resize Controls......Page 229
Use Property Sheets......Page 231
Format Conditionally......Page 234
Delete Controls......Page 237
Change the Record Source......Page 238
Use AutoFormat......Page 239
Add a Background Picture......Page 240
10 Create Custom Forms and Subforms......Page 242
Create a New Form Design......Page 243
Use the Form Wizard......Page 245
Create a Form Without the Wizard......Page 246
Modify the Form Design......Page 247
Place and Customize Data-Related Controls......Page 248
Add Yes/No Controls......Page 256
Add User-Interactive Controls......Page 259
Use the Form for Data Entry......Page 262
Change the Tab Order......Page 263
Sort and Filter Data in a Form......Page 264
View Multiple Records......Page 265
Create a Multiple-Page Form......Page 266
Add a Tab Control......Page 267
Customize a Tab Control......Page 269
Add an AutoDial Control......Page 270
Use the Form Wizard......Page 271
Use the Subform Wizard......Page 273
Modify a Subform......Page 276
Add Data Validation......Page 277
Validate with Events......Page 278
11 Create and Customize Reports and Subreports......Page 280
Start a New Report......Page 281
Use the Report Wizard......Page 282
Work in the Print Preview Window......Page 287
Print the Report......Page 289
Examine the Report Sections......Page 291
Set Report and Section Properties......Page 292
Add Page Numbers and Date/Time Controls......Page 293
Sort and Group Records in a Report......Page 295
Add Group Sections......Page 296
Customize Group Headers and Footers......Page 298
Add Summaries and Running Totals......Page 299
Modify and Add Groups......Page 300
Create a Summary Report with the Report Wizard......Page 303
Print an Alphabetic Index......Page 305
Add a Subreport......Page 306
Create a Subreport with the Report Wizard......Page 307
Create a Subreport Control......Page 308
Insert an Existing Subreport......Page 311
Link the Report and Subreport......Page 312
Modify a Subreport Control......Page 313
Design a Multiple-Column Report......Page 314
Print Mailing Labels......Page 315
Use the Label Wizard......Page 316
12 Create Charts and Graphs......Page 320
Select the Data for the Chart......Page 321
Use the Access Chart Wizard......Page 324
Link the Chart to Record Data......Page 327
Add an Existing Chart to a Form or Report......Page 328
Modify the Chart......Page 329
Modify with Access......Page 330
Edit with Microsoft Graph......Page 333
PART III Improve the Access 2003 Workplace......Page 342
13 Customize the Workplace......Page 344
Rearrange Icons......Page 345
Create a Shortcut......Page 346
Set Workplace Options......Page 347
Create Custom Groups......Page 358
Change the Way Access Starts......Page 359
Set Office Assistant Options......Page 362
Choose a Different Assistant......Page 363
14 Speed Up Your Database......Page 366
Use the Analyzer Wizards......Page 367
Optimize Tables and Queries......Page 373
Optimize Forms and Reports......Page 375
Optimize Controls......Page 376
Back Up and Restore a Database......Page 377
Compact and Repair a Database......Page 378
15 Automate with Macros......Page 382
Create a Simple Macro......Page 383
Choose Macro Actions......Page 384
Set Action Arguments......Page 385
Start the Macro......Page 386
Step Through a Macro......Page 387
Assign a Macro to an Event Property......Page 388
Decide Which Event to Use......Page 389
Add Conditions to a Macro......Page 390
Create a Macro to Display a Warning......Page 391
Set Control Values and Properties......Page 393
Change the Flow of Operations......Page 395
Filter Records......Page 397
Create a Macro Group......Page 398
Assign AutoKeys......Page 400
16 Customize Menus and Toolbars......Page 402
Use Access Command Bars......Page 403
Show and Hide Built-in Toolbars......Page 404
Change Menu and Toolbar Options......Page 406
Create Custom Toolbars and Menu Bars......Page 408
Specify Global Command Bars......Page 416
Delete a Custom Command Bar......Page 417
Modify Command Bars......Page 418
Move Controls......Page 419
Edit Buttons and Menu Commands......Page 420
Restore Built-in Command Bars......Page 422
17 Create Custom Switchboards and Dialog Boxes......Page 424
Create Switchboards......Page 425
Use the Switchboard Manager to Create Switchboards......Page 426
Modify the Switchboard......Page 433
Design the Form......Page 437
Create and Attach the Macros......Page 439
Set Form Properties and Style......Page 441
Set the Input Form Properties......Page 444
Create the Macros......Page 445
Modify the Query......Page 446
PART IV Exchange Data with Others......Page 448
18 Exchange Database Objects and Text......Page 450
Copy and Paste......Page 451
Drag and Drop......Page 452
Import Objects......Page 453
Set Import Options......Page 455
Link Access Tables......Page 456
Import from or Link to Other Data Sources......Page 457
Use Data from dBASE or Paradox......Page 458
Rename a Linked Table in Access......Page 459
Update Links with the Linked Table Manager......Page 460
Use Delimited Text Files......Page 461
Use Fixed-Width Text Files......Page 465
Change Import Specifications......Page 466
Export to an Existing Access Database......Page 467
Export to Text Files......Page 469
19 Exchange Data with Outside Sources......Page 472
Copy or Move Data from a Word Processor......Page 473
Copy or Move Records from Access to Another Application......Page 475
Save Access Output as an External File......Page 476
Work with Word......Page 477
Save in Rich Text Format......Page 478
Publish with Word......Page 479
Use Merge It with Word......Page 480
Import from and Link to Excel Spreadsheets......Page 482
Use Analyze It with Microsoft Excel......Page 487
20 Share with Multiple Users......Page 490
Share an Entire Database......Page 491
Split the Database......Page 492
Replicate a Database......Page 496
Manage the Database in a Multiuser Environment......Page 497
Control Data Editing......Page 498
Edit Shared Database Objects......Page 501
21 Secure a Database......Page 502
Secure a Database with a Password......Page 503
Understand the User-Level Security Model......Page 504
Secure a Database with the User-Level Security Wizard......Page 506
Create a Workgroup Without the Wizard......Page 511
Assign or Change Permissions and Ownerships......Page 518
Remove User-Level Security......Page 522
Hide Database Objects......Page 523
APPENDIX Convert to Access 2003......Page 526
Convert a Database......Page 527
Convert a Replicated Database......Page 529
Enable a Database......Page 530
Share a Database Across Several Access Versions......Page 531
Convert from Access 2002-2003 to Access 2000......Page 532
Index......Page 534