21,99 €
Mehr erfahren.
- Herausgeber: John Wiley & Sons
- Kategorie: Wissenschaft und neue Technologien
- Sprache: Englisch
Join the millions of people already using Microsoft Access and become a database power-user in no time!
In the newly revised edition of Microsoft Access For Dummies, professional database developer and Access extraordinaire Laurie Ulrich-Fuller walks you through the ins-and-outs of one of the world's most popular database platforms. This is the perfect beginner's guide to Microsoft Access, showing you how to create databases, extract data, create reports, and more. The author demonstrates a ton of tips, tricks, and best practices you can use immediately to create, maintain, and improve your databases. You'll also find:
- Updates outlining edge browser controls in forms
- Step-by-step guides explaining how to import, export, and edit data
- Easy-to-follow query-writing tutorials to help you find the exact data you're looking for when you need it
Whether you're a database novice or a data science whiz, Microsoft Access For Dummies has the info you need to supercharge your database skills. It's the perfect, how-to guide to get you up-to-speed on everything you need to know to get started with Microsoft's world-famous database app.
Sie lesen das E-Book in den Legimi-Apps auf:
Seitenzahl: 532
Veröffentlichungsjahr: 2025
Ähnliche
Microsoft 365® Access™ For Dummies®
To view this book's Cheat Sheet, simply go to www.dummies.com and search for “Microsoft 365 Access For Dummies Cheat Sheet” in the Search box.
Table of Contents
Cover
Title Page
Copyright
Introduction
About This Book
Foolish Assumptions
Icons Used in This Book
Where to Go from Here
Part 1: Basic Training
Chapter 1: Access Basics
What Is Access Good For, Anyway?
Reach Out with SharePoint
How Access Works and How You Work with It
OK, Now What?
Chapter 2: Finding Your Way in the Access Workspace
Diving Right In
Working with Onscreen Tools in Access
Customizing the Access Workspace
Mousing Around
Navigating Access with the Alt Key
Chapter 3: Database Basics
Database Lingo
Field Types and Uses
Choosing Between Flat and Relational Databases
Building a Database
Adding and Removing Tables
Part 2: Getting It All on the Table
Chapter 4: Sounds Like a Plan
Planning Your Database Tables
Building Tables in Design View
Chapter 5: Table Tune-Ups
The Primary Key to Success
Making Tables Play Nice
Building Table Relationships
Indexing for Faster Queries
Chapter 6: Remodeling Your Data
Opening a Table for Editing
Inserting Records and Fields
Modifying Field Content
Name-Calling
Turn Uh-Oh! into Yee-Hah!
Chapter 7: Types, Masks, and Triggers
Access Table Settings
Field Data Formats
Gaining Control of Data Entry
Give Your Fingers a Mini Vacation by Default
Part 3: Data Management Mania
Chapter 8: A Form for All Reasons
Generating Forms
Customizing Form Parts
Managing Data in Form View
Chapter 9: Importing and Exporting Data
Retrieving Data from Other Sources
Hit the Road, Data
Chapter 10: Automatically Editing Data
Please Read This First!
Creating Consistent Corrections
Using Queries to Automate the Editing Process
Chapter 11: Access and the Web
How Access Works with the Web
Understanding Microsoft 365
Using the Access Web Browser Control
Part 4: The Power of Questions
Chapter 12: Finding, Filtering, and Sorting Your Data — Fast
Using the Find Command
Sorting Alphabetically and Numerically
Fast and Furious Filtering
Chapter 13: I Was Just Asking … for Answers
Simple (Yet Potent) Filter and Sort Tools
Select Queries
Getting Your Feet Wet with Ad Hoc Queries
Chapter 14: I Want These AND Those OR Them
Working with AND and/or OR
Combining AND with OR and OR with AND
Chapter 15: Number Crunching with the Total Row
Say Hello to the Total Row
Adding the Total Row to Your Queries
Giving the Total Row a Workout
Creating Your Own Top-Ten List
Choosing the Right Field for the Summary Instruction
Chapter 16: Express Yourself with Formulas
A Simple Calculation
Complex Calculations
And Now … the Expression Builder!
Chapter 17: Take Charge with Action Queries
Easy Update
Add Records in a Flash
Quick Cleanup
Part 5: Simple and Snazzy Reporting
Chapter 18: Fast and Furious Automatic Reporting
Quick and Not-So-Dirty Automatic Reporting
Previewing Your Report
Beauty Is Only Skin (Report) Deep
Chapter 19: Professionally Designed Reports Made Easy
Reports Reimagined
Report Organization
Formatting Stuff
Adding More Design Elements
Chapter 20: Groups and Page Breaks, Headers and Footers
A Place for Everything and Everything in Its Place
Customizing Properties
Part 6: The Part of Tens
Chapter 21: Ten Common Problems
That’s Just Not Normal
You Type 73.725, but It Changes to 74
Where’s That Word I Just Typed?
I Swear It Was There — But Now It’s Gone
You Run a Query, but the Results Aren’t What You Expect
What the Heck Is This Parameter Dialog Box?
Your Database Is Painfully Slow
Your Database File Is Humongous!
Chaos Ensues after Importing Your Spreadsheet
We’re Sorry; Your Database File Is Corrupt
Chapter 22: Ten Uncommon Tips
Document Everything as if Your Life Depended on It
Keep Your Fields as Small as Possible
Use Number Fields for Real Numbers
Validate Your Data
Use Understandable Names to Keep Things Simple
Delete with Great Caution
Backup, Backup, Backup
Go Ahead and Overthink
Get Organized and Keep It Simple
There’s No Shame in Asking for Help
Appendix A: Getting Help
Index
About the Author
Advertisement Page
Connect with Dummies
End User License Agreement
List of Tables
Chapter 3
TABLE 3-1 Common Fields for Everyday Tables
TABLE 3-2 Prohibited Symbols
Chapter 4
TABLE 4-1 Number Field Sizes
Chapter 7
TABLE 7-1 Formatting Codes for Text Fields
TABLE 7-2 Codes for Input Masks
TABLE 7-3 Common Number-Field Validations
TABLE 7-4 Common Date-Field Validations
Chapter 9
TABLE 9-1 Files
TABLE 9-2 Compatible Database File Formats
TABLE 9-3 Online Services
TABLE 9-4 Other Sources
Chapter 11
TABLE 11-1 Types of Hyperlink Protocol Codes in Access
Chapter 13
TABLE 13-1 Basic Comparison Operators
Chapter 15
TABLE 15-1 Total Row Functions
Chapter 18
TABLE 18-1 Print Preview Tools
List of Illustrations
Chapter 1
FIGURE 1-1: Datasheet view can be an easy environment for data entry. Or not.
FIGURE 1-2: Here’s a simple form for entering new records or reviewing existing...
FIGURE 1-3: Ah, simplicity. A quick report is just one click away.
FIGURE 1-4: The Report Wizard creates more elaborate (but simple) reports, like...
FIGURE 1-5: Design view might look a little intimidating, but to really customi...
FIGURE 1-6: The Save As command offers choices for … you guessed it … saving yo...
FIGURE 1-7: Double-click an Access database file, and Access opens right up.
FIGURE 1-8: Pick a recently used database from the Recent list on the right.
FIGURE 1-9: An existing table, ready for more records.
FIGURE 1-10: Click the Blank Database button in the New group.
FIGURE 1-11: Name your database something that replaces the generic Database
X
.a...
FIGURE 1-12: View the database templates for your chosen category.
Chapter 2
FIGURE 2-1: When it comes to the interface, Access users will find a comforting...
FIGURE 2-2: Open Access
and
your existing database in one fell swoop.
FIGURE 2-3: You can build a database from nothing or from something — in the fo...
FIGURE 2-4: Open an existing or recently used database.
FIGURE 2-5: The main Ribbon tabs appear when you open a database.
FIGURE 2-6: The buttons relevant to what’s open and active in your database are...
FIGURE 2-7: You can easily tell the active tab (Create) from the inactive ones.
FIGURE 2-8: Click the arrow to the right of the button and make a choice.
FIGURE 2-9: Menu buttons display a — surprise! — menu when clicked.
FIGURE 2-10: Craving the File tab’s tools? Click the File tab to choose from a ...
FIGURE 2-11: Reporting-related tools appear precisely when they’re needed.
FIGURE 2-12: Like to rearrange things? Display your Quick Access Toolbar option...
FIGURE 2-13: Pick a command category and a command to add to the Quick Access T...
FIGURE 2-14: Add as many buttons as you want — the toolbar will expand horizont...
FIGURE 2-15: Reconsidering that added button? No problem. Say bye-bye with a si...
FIGURE 2-16: The Ribbon, minimized.
FIGURE 2-17: The File tab’s panel of commands gives you an Options command, whi...
FIGURE 2-18: The settings for just about everything you can see and use in Acce...
FIGURE 2-19: Mousing over the Table button on the Create tab tells you more abo...
FIGURE 2-20: Rather press a letter or number than click a tab or button with yo...
FIGURE 2-21: Each button on a tab has its own keyboard shortcut.
Chapter 3
FIGURE 3-1: New blank databases need names. Give yours one here.
FIGURE 3-2: Select a home for your new database.
FIGURE 3-3: New table, new database.
FIGURE 3-4: Create new fields by pressing Enter after naming each one.
FIGURE 3-5: The Create tab is the logical place to go when you want to create a...
FIGURE 3-6: When you close the table, you will be prompted to save the table.
FIGURE 3-7: Each table has its own button, emblazoned with the name you gave th...
FIGURE 3-8: Choose Delete to get rid of the unwanted table.
Chapter 4
FIGURE 4-1: Tables, fields, data types, and sizes.
FIGURE 4-2: The sadly abnormal Events table.
FIGURE 4-3: Getting Tyson’s events with
abnormal
table design.
FIGURE 4-4: Tyson’s events with
abnormal
table design.
FIGURE 4-5: Getting Tyson’s events with
normalized
table design.
FIGURE 4-6: Tyson’s events with
normalized
table design.
FIGURE 4-7: A new table is born.
FIGURE 4-8: Fields entered in Table Design view.
FIGURE 4-9: Choosing a data type and field size.
Chapter 5
FIGURE 5-1: The completed primary key.
FIGURE 5-2: The Relationships button on the Database Tools tab.
FIGURE 5-3: Use the Add Tables pane to add tables to the Relationships window.
FIGURE 5-4: The Edit Relationships dialog box details how Access connects two t...
FIGURE 5-5: A one-to-many relationship between two tables.
FIGURE 5-6: The Indexes window with the Indexes button above it on the Ribbon.
Chapter 6
FIGURE 6-1: The Access workspace makes opening a database easy.
FIGURE 6-2: Use the Open view to choose from recently used databases or pursue ...
FIGURE 6-3: Open your database through the This PC option.
FIGURE 6-4: The database file opens, and its objects are listed on the left.
FIGURE 6-5: A new record awaits its data.
FIGURE 6-6: Right there in the table is a new field, awaiting creation.
FIGURE 6-7: Not sure what field type to choose? Short Text is a good choice, be...
FIGURE 6-8: Name that new field, and make it feel at home in your table.
FIGURE 6-9: Drag-and-drop your field to reposition it among the other fields in...
FIGURE 6-10: The Data Type drop-down menu gives you formatting options.
FIGURE 6-11: Bid your field a fond adieu with just two clicks of the mouse.
FIGURE 6-12: A selected field name is ripe for editing.
FIGURE 6-13: Rename your field in the Enter Field Properties dialog box.
FIGURE 6-14: To rename a table, you have to be able to see its name listed in t...
Chapter 7
FIGURE 7-1: You can see and edit a table’s structure, including its field prope...
FIGURE 7-2: Working on the
EventDate
field.
FIGURE 7-3: The number format list.
FIGURE 7-4: The ever-popular date/time format list.
FIGURE 7-5: Not much to talk about with Yes/No formatting.
FIGURE 7-6: The Input Mask Wizard for a date/time field.
FIGURE 7-7: The Input Mask Wizard completes its maskterpiece!
FIGURE 7-8: Manually adding a capitalization mask.
FIGURE 7-9: I have violated the input mask.
FIGURE 7-10: The Required property is set to Yes.
FIGURE 7-11: The Default Value property set to return the current date with the...
Chapter 8
FIGURE 8-1: The Create tab of the Ribbon holds the Forms buttons.
FIGURE 8-2: Form based on the Volunteers table using the Split Form button.
FIGURE 8-3: Select the data source and fields you want to see on the form.
FIGURE 8-4: Looks good! A form created by the Form Wizard.
FIGURE 8-5: The Themes group on the Ribbon.
FIGURE 8-6: The Controls group on the Ribbon.
FIGURE 8-7: If the wizards aren’t coming out to play, turn them on!
FIGURE 8-8: A selected control on the Volunteers form.
FIGURE 8-9: Navigation 101.
FIGURE 8-10: Record selectors and the ever-popular Records group on the Home Ri...
Chapter 9
FIGURE 9-1: Make sure that you choose the correct button for your file format.
FIGURE 9-2: The Get External Data – Excel Spreadsheet dialog box.
FIGURE 9-3: Saving the Volunteers import steps.
FIGURE 9-4: The Saved Imports tab of the Manage Data Tasks dialog box.
FIGURE 9-5: The Export button group.
FIGURE 9-6: The Export – Excel Spreadsheet dialog box completed.
FIGURE 9-7: The exported Volunteers table in Excel, ready for anything.
FIGURE 9-8: Exporting the Events by Type report to PDF.
Chapter 10
FIGURE 10-1: Make a back-up copy of that table for safekeeping.
FIGURE 10-2: Choose Paste to create the back-up version of the table.
FIGURE 10-3: The Paste Table As dialog box.
FIGURE 10-4: The Find and Replace dialog box.
FIGURE 10-5: The Query Wizard is here to help you.
FIGURE 10-6: The Find Duplicates Query Wizard takes you through duplicate-findi...
FIGURE 10-7: Pick the fields that might have unwanted duplicate entries.
FIGURE 10-8: Pick the fields that will help you choose which duplicates to keep...
FIGURE 10-9: Name your query and finish it so you can see your duplicates onscr...
FIGURE 10-10: All these records have duplicate entries in one or more fields.
Chapter 11
FIGURE 11-1: The Web Browser Control tool.
FIGURE 11-2: The Insert Hyperlink dialog box in front of the web browser contro...
FIGURE 11-3: Connecting the web browser control to a table field.
FIGURE 11-4: Wow! Form displaying a website using the web browser control.
FIGURE 11-5: In Design view, choose Hyperlink from the list of data types.
FIGURE 11-6: Right-click a stored hyperlink within the table and access tools f...
FIGURE 11-7: The Insert Hyperlink dialog box.
FIGURE 11-8: Choose to export your table as an HTML document.
FIGURE 11-9: The Export – HTML Document dialog box allows you to name your web ...
FIGURE 11-10: Choose an HTML template or click OK to accept the defaults.
FIGURE 11-11: Congratulations! You’ve just created an HTML document!
Chapter 12
FIGURE 12-1: The Find and Replace dialog box.
FIGURE 12-2: To search the entire table, change Look In.
FIGURE 12-3: Using the Match option.
FIGURE 12-4: Filtering a single field based on that field’s entries.
FIGURE 12-5: Access shows only those records matching the Filter by Selection c...
FIGURE 12-6: Filter by Form offers a grid and drop-down lists to set criteria f...
FIGURE 12-7: The drop-down list shows all unique values in a field.
FIGURE 12-8: Click the Or tab to further define the criteria.
FIGURE 12-9: Access finds all the Active volunteers who live in Landisville.
FIGURE 12-10: With one click, Access hides all volunteers whose
City
field valu...
Chapter 13
FIGURE 13-1: Query Design View window allows you to choose the field(s) on whic...
FIGURE 13-2: Click the Advanced button to choose Advanced Filter/Sort.
FIGURE 13-3: Two fields are queried in this example —
Status
and
StartDate
.
FIGURE 13-4: Voilà! Your filtered data appears; no abracadabra needed.
FIGURE 13-5: The Simple Query Wizard starts and asks which table(s) you want to...
FIGURE 13-6: The Tables/Queries drop-down list.
FIGURE 13-7: The Query Wizard may give you the chance to summarize your data.
FIGURE 13-8: Access offers different ways of summarizing the data.
FIGURE 13-9: The results of a query built with the Query Wizard.
FIGURE 13-10: Use this view to begin building your own queries from scratch.
FIGURE 13-11: The Volunteers, Events, and EventVolunteers tables are added to t...
FIGURE 13-12: Access knows how the Volunteers, EventVolunteers, and Events tabl...
FIGURE 13-13: See the resulting events in Ascending date order.
FIGURE 13-14: With the criteria set for the
EventType
field, only events of tha...
FIGURE 13-15: Click the big red exclamation point that says “Run” underneath it...
Chapter 14
FIGURE 14-1: Find data that falls within a range of dates by using
AND
.
FIGURE 14-2: The
BETWEEN
operator is the ultimate range finder.
FIGURE 14-3: Multiple criteria whittle down the resulting data to just those re...
FIGURE 14-4:
OR
allows more (yet very specific) records to meet your criteria.
FIGURE 14-5: You can use the
OR
operator to set criteria for data from differen...
FIGURE 14-6: Any criteria placed on separate lines are seen as
OR
statements.
Chapter 15
FIGURE 15-1: The Total row appears between the Table and the Sort rows.
FIGURE 15-2: This query counts the donations for every combination of data in t...
FIGURE 15-3: Make Access sort your results the way you want with a quick click ...
FIGURE 15-4: Put the Sum instruction in a numeric field so it has something to ...
FIGURE 15-5: Use Count on a single field to easily count the records in a table...
FIGURE 15-6: This query counts the number of volunteers in each state.
FIGURE 15-7: The StateCount column heading is added to the count of the
State
f...
FIGURE 15-8: The Where instruction limits records in the results to Lancaster o...
FIGURE 15-9: Setting the Top Values property to 10.
FIGURE 15-10: The
Zip
and
VolunteerID
fields counted.
Chapter 16
FIGURE 16-1: The
AmountOwed
calculation.
FIGURE 16-2: The results of the amount owed calculation in Datasheet view.
FIGURE 16-3: The AmountOwed Calculated column is referred to in the NextDonatio...
FIGURE 16-4: The results of the AmountOwed and NextDonation columns.
FIGURE 16-5: The next donation percentage parameter in the
NextDonation
Calcula...
FIGURE 16-6: Access asks for a next donation percentage.
FIGURE 16-7: Turning two names into a single Calculated field.
FIGURE 16-8: First and last names are combined to the single field,
FullName
.
FIGURE 16-9: The Builder button launches Expression Builder.
FIGURE 16-10: Expression Builder to the rescue?
FIGURE 16-11: The Donation Report form with the Volunteer combo box.
FIGURE 16-12: Expression Builder is used to select the Volunteer combo box from...
FIGURE 16-13: The expression as it appears in the Criteria row of the Donor Tot...
Chapter 17
FIGURE 17-1: Click the Query Design button on the Ribbon.
FIGURE 17-2: The Volunteers table added to the query.
FIGURE 17-3: The
Status
and
State
fields are added to the query.
FIGURE 17-4: Select the Update button on the Ribbon, and the Update To row appe...
FIGURE 17-5: This query will update the
Status
field to Active for volunteers f...
FIGURE 17-6: Click Yes to update your data.
FIGURE 17-7: The
Status
field
before
running the Update query.
FIGURE 17-8: The
Status
field
after
running the Update query.
FIGURE 17-9: Fields from the source table to be added to the destination table.
FIGURE 17-10: Say hello to the Append dialog box.
FIGURE 17-11: This query appends all records that equal “Prospective” in the
St
...
FIGURE 17-12: The Delete query in Design view.
FIGURE 17-13: This query deletes all records where status is Inactive.
Chapter 18
FIGURE 18-1: You can find the Report button on the Create tab.
FIGURE 18-2: Save your report to make it a permanent part of the database.
FIGURE 18-3: Change your report layout with a quick click of the Stacked or Tab...
FIGURE 18-4: Rearranging the original quick Volunteers report is easy — after s...
FIGURE 18-5: Click and drag to widen or narrow your report’s columns, one at a ...
FIGURE 18-6: The Report Wizard starts by offering you tables and the fields wit...
FIGURE 18-7: Add fields by double-clicking them or by using the buttons between...
FIGURE 18-8: Your list of volunteers will be grouped by their Status field valu...
FIGURE 18-9: Sort by the most important field in the table, or the one that wil...
FIGURE 18-10: Choose your report’s layout in terms of field structure and orien...
FIGURE 18-11: Name your report, or accept the table-name-based moniker that Acc...
FIGURE 18-12: Even a simple report with few fields can look important.
FIGURE 18-13: Preview up to 12 pages of your report at a time.
FIGURE 18-14: Choose the view you want to use or how many pages you want to see...
FIGURE 18-15: Adjusting margins to specify how much white space surrounds your ...
FIGURE 18-16: Use the Page tab to choose a printer, page size, and more.
FIGURE 18-17: The Column Layout area of the Columns tab enables you to format a...
Chapter 19
FIGURE 19-1: Design view is best for adding new design elements to your report.
FIGURE 19-2: Layout view is best for modifying existing report elements.
FIGURE 19-3: Common report sections.
FIGURE 19-4: The Page Break control selected.
FIGURE 19-5: The Format tab on the Ribbon.
FIGURE 19-6: The report title is selected.
FIGURE 19-7: The Position group in the Ribbon’s Arrange tab.
FIGURE 19-8: The gallery of Shape Outline colors.
FIGURE 19-9: The seven Line Thickness options.
FIGURE 19-10: The eight Line Type options.
FIGURE 19-11: The three alignment buttons.
Chapter 20
FIGURE 20-1: In a Columnar report, labels appear to the left of the fields and ...
FIGURE 20-2: In a Tabular report, labels become column headings.
FIGURE 20-3: Grouping volunteer records by Status.
FIGURE 20-4: The Group, Sort, and Total panel.
FIGURE 20-5: Choose a new field by which to group or sort your records.
FIGURE 20-6: Double-click any piece of the report to see that item’s properties...
FIGURE 20-7: The Format tab’s Properties list varies depending on which report ...
FIGURE 20-8: Use the Property Sheet drop-down list to display PageHeaderSection...
FIGURE 20-9: Control your report’s page numbers.
FIGURE 20-10: Choose dates and times here.
Guide
Cover
Table of Contents
Title Page
Copyright
Begin Reading
Index
About the Author
Pages
iii
iv
1
2
3
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
281
282
283
284
285
286
287
288
289
290
291
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
Microsoft 365® Access™ For Dummies®, 2nd Edition
Published by: John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030-5774, www.wiley.com
Copyright © 2025 by John Wiley & Sons, Inc. All rights reserved, including rights for text and data mining and training of artificial technologies or similar technologies.
Media and software compilation copyright © 2025 by John Wiley & Sons, Inc. All rights reserved, including rights for text and data mining and training of artificial technologies or similar technologies.
Published simultaneously in Canada
No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without the prior written permission of the Publisher. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, (201) 748-6011, fax (201) 748-6008, or online at http://www.wiley.com/go/permissions.
Trademarks: Wiley, For Dummies, the Dummies Man logo, Dummies.com, Making Everything Easier, and related trade dress are trademarks or registered trademarks of John Wiley & Sons, Inc. and may not be used without written permission. Microsoft 365 and Access are trademarks or registered trademarks of Microsoft Corporation. All other trademarks are the property of their respective owners. John Wiley & Sons, Inc. is not associated with any product or vendor mentioned in this book. Access For Dummies®, 2nd Edition is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation.
LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: THE PUBLISHER AND THE AUTHOR MAKE NO REPRESENTATIONS OR WARRANTIES WITH RESPECT TO THE ACCURACY OR COMPLETENESS OF THE CONTENTS OF THIS WORK AND SPECIFICALLY DISCLAIM ALL WARRANTIES, INCLUDING WITHOUT LIMITATION WARRANTIES OF FITNESS FOR A PARTICULAR PURPOSE. NO WARRANTY MAY BE CREATED OR EXTENDED BY SALES OR PROMOTIONAL MATERIALS. THE ADVICE AND STRATEGIES CONTAINED HEREIN MAY NOT BE SUITABLE FOR EVERY SITUATION. THIS WORK IS SOLD WITH THE UNDERSTANDING THAT THE PUBLISHER IS NOT ENGAGED IN RENDERING LEGAL, ACCOUNTING, OR OTHER PROFESSIONAL SERVICES. IF PROFESSIONAL ASSISTANCE IS REQUIRED, THE SERVICES OF A COMPETENT PROFESSIONAL PERSON SHOULD BE SOUGHT. NEITHER THE PUBLISHER NOR THE AUTHOR SHALL BE LIABLE FOR DAMAGES ARISING HEREFROM. THE FACT THAT AN ORGANIZATION OR WEBSITE IS REFERRED TO IN THIS WORK AS A CITATION AND/OR A POTENTIAL SOURCE OF FURTHER INFORMATION DOES NOT MEAN THAT THE AUTHOR OR THE PUBLISHER ENDORSES THE INFORMATION THE ORGANIZATION OR WEBSITE MAY PROVIDE OR RECOMMENDATIONS IT MAY MAKE. FURTHER, 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.
For general information on our other products and services, please contact our Customer Care Department within the U.S. at 877-762-2974, outside the U.S. at 317-572-3993, or fax 317-572-4002. For technical support, please visit https://hub.wiley.com/community/support/dummies.
Wiley publishes in a variety of print and electronic formats and by print-on-demand. Some material included with standard print versions of this book may not be included in e-books or in print-on-demand. If this book refers to media such as a CD or DVD that is not included in the version you purchased, you may download this material at http://booksupport.wiley.com. For more information about Wiley products, visit www.wiley.com.
Library of Congress Control Number is available from the publisher.
ISBN 978-1-394-29565-4 (pbk); ISBN 978-1-394-29566-1 (ebk); ISBN 978-1-394-29567-8 (ePDF)
Introduction
Welcome! Thank you for selecting this book. We assume you’ve done so because you’re hoping it will explain how to use Microsoft Access, and of course, as the authors, we believe it will — that’s why we wrote it! We, the authors, have both been teaching and using Access for a very long time, and we know how to share what we know with our students.
So what was it that made you seek out a book on Access? It might be that you’ve been asked to use it at work, or perhaps you run your own business or are managing a nonprofit organization. If any of these is the case — or if you’re just a regular human with a lot of personal contacts and irons in the fire, you need Access to organize your data. You need it so you can find a name or a transaction in seconds after a few keystrokes, not after minutes spent scanning your spreadsheets or swiping apps this way and that on your smartphone. You need it so you can produce reports that make you look like the genius you are. You need it so you can create cool forms that will help your staff enter all the data you’ve got stacked on their desks — and in a way that lets you know the data was entered properly so that it’s accurate and useful. You need Access so you can find little bits of data out of the huge pool of information you need to store. So that’s it. You just need it.
About This Book
With all the power that Access has (and that it therefore gives you), there comes a small price: complexity. Access isn’t one of those applications where you can just sit down and use “right out of the box.” It’s not scarily difficult or anything, but there’s a lot going on — and you need some guidance, some help, and some direction to really use it and make it bend to your will. And that’s where this book — a “reference for the rest of us” — comes in.
So you’ve picked this book. You’ve done a smart thing (if we don’t say so ourselves) and whether you begin with Chapter 1 or whether you dive in and start with a particular feature or area of interest that’s been giving you fits, just read, and then go put Access to work for you.
Foolish Assumptions
You need to know only a few things about your computer and Windows to get the most out of Access For Dummies. In the following pages, we presume that you:
Know the basics of the current version of Windows — how to open programs, save your files, create folders, find your files after you’ve saved them, print, and do basic stuff like that.
Have some goals that Access will help you reach. You
want to build your own databases
and/or
want to work with databases that other people have created
Want to use and create queries, reports, and an occasional form
Have a computer capable of running the latest version of Windows and the Access application
If your computer uses a version of Windows prior to Windows 10, you can’t run recent versions of Access.
Icons Used in This Book
When something in this book is particularly valuable, we go out of our way to make sure that it stands out. We use these cool icons to mark text that (for one reason or another) really needs your attention. Here’s a quick preview of the ones waiting for you in this book and what they mean.
Tips are incredibly helpful words of wisdom that promise to save you time, energy, and the embarrassment of being caught swearing out loud while you think you’re alone. Whenever you see a Tip, take a second to check it out.
Some things are too important to forget, so the Remember icon points them out. These items are critical steps in a process — points that you don’t want to miss.
Sometimes we give in to the techno-geek lurking inside us and slip some technical babble into the book. The Technical Stuff icon protects you from obscure details by making them easy to avoid. On the other hand, you may find them interesting. (Your inner techno-geek will rejoice.)
The Warning icon says it all: Skipping this information may be hazardous to your data’s health. Pay attention to these icons and follow their instructions to keep your databases happy and intact.
In addition to the content in this book, you’ll find some extra content available by visiting www.dummies.com and typing Access into the search box, including
The Cheat Sheet for this book
Updates to this book, if any
Where to Go from Here
Now nothing’s left to hold you back from the thrills, chills, and power of Access. Not sure where to start? See if you spot yourself in these scenarios:
If you’re brand new to the program and don’t know which way to turn, start with the general overview in
Chapter 1
.
If you’re about to design a database, we salute you — and recommend flipping through
Chapter 4
for some helpful design and development tips.
Looking for something specific? Try the Table of Contents or the index.
Part 1
Basic Training
IN THIS PART …
Discover what Access is and does and what’s new in the current version of Access.
Learn about the objects that make up an effective database, and get started building your first table.
Master database lingo so you can speak the language and understand the terminology.
Chapter 1
Access Basics
IN THIS CHAPTER
Deciding when to use Access
Discovering what’s new in Access
Unlocking the basics of working with Access
Figuring out how to get started
Access, the one, true database application within Microsoft Office, has always been a powerful program. As a result, you’re probably reading this book because all that power makes Access an application that’s not so easy to learn on your own. You’re not alone in that feeling, because Access is also unique within Microsoft Office in that most people can’t just fire it up and start using it the way you might have done with your first Word document. So having this book by your side is a good choice.
That said, with the foundation components of Access, and the key functionality that you’ll discover in this book, you’ll be able to put Access through many of its most important paces. You’ll be working with wizards and other onscreen tools that keep you at a comfortable arm’s distance from the software’s inner workings, the things that programmers and serious developers play with, but you’ll be harnessing real power. Hope you’re feeling better now!
Please don’t panic after reading that reference to “real power.” You don’t have to use every feature and tool and push the edges of the Access envelope to build a really solid database. In fact, you can use very little of everything Access has to offer and still create quite a significant solution to your needs for storing and accessing data — all because Access can really “do it all” — enabling you to set up a database quickly, build records into that database, and then use that data in several useful ways. Later on, who knows? You may become an Access guru, if that’s your desire. And this book can be a great start in that process, too.
In this chapter, you’ll discover what Access does best (and when you might want to use another tool instead), and you’ll see how it does what it does, and hopefully you’ll begin to understand and absorb some basic terminology.
Of course, nobody’s expecting you to memorize tons of complex vocabulary or anything scary like that. We would never do that to you. Rather, the goal here (and in the next two chapters) with regard to terms is to introduce you to some basic words and general concepts intended to help you make better use of Access — as well as better understand later chapters in this book, if you choose to follow us all the way to its stunning, life-altering conclusion.
What Is Access Good For, Anyway?
What is Access good for? That’s a good question. And, happily, the list of what you can do with it is a lot longer than the list of what you can’t do with it. When it comes to data organization, storage, and retrieval, Access is at the head of the class, no matter what Excel aficionados will tell you — and even if you’ve been using Excel to sort and filter your lists and thought that was all you need.
Building big databases
Okay, what do I mean by big database? Any database with a lot of records — and by a lot, I mean hundreds. At least. And certainly if you have thousands of records, you need a tool like Access to manage them. Although you can use Microsoft Excel to store lists of records, it limits how many you can store (no more than the number of rows in a single worksheet, which is just over 1 million, but Excel will run poorly if you get anywhere near that number), and it wasn’t designed to create a true database. Overall, Excel wasn’t designed to create the kind of tools — things like forms and reports — that Access can build. So anything with a lot of records and complex data is best done in Access.
Below are some reasons why Access handles big databases so well.
Typically, a big database has big data-entry needs.
Access doesn’t just offer forms, but form-building features that enable you to create a quick form through which someone can enter many records, quickly and easily. This creates efficiency and ensures accuracy. (Check out
Chapter 8
for more about building forms.)
When you have lots and lots of records, you also have lots of opportunities for errors to creep in.
This includes duplicate records, records with misspellings, and records with missing information — and that’s just for openers. So, you need an application like Access to ferret out those errors and fix them. (
Chapter 10
lays out how you can use Access to find and replace errors and search for duplicate entries.)
Big databases mean big needs for accurate, insightful reporting.
Access has powerful reporting tools you can use to create printed and onscreen reports — and those can include as few or as many pieces of your data as you need, drawn from more than one table if need be. You can tailor your reports to your audience, from what’s shown on the reports’ pages to the colors and fonts used.
Big databases are hard to wade through when you want to find something.
Access provides several tools for sorting, searching, and creating your own specialized tools (known as
queries
) for quickly finding the elusive single record or group of records you need.
Access saves time by making it easy to import and recycle data.
You may have used certain tools to import data from other sources — such as Excel worksheets (if you started in Excel and maxed out its usefulness as a data-storage device) and Word tables. Access saves you from reentering all your data and allows you to keep multiple data sources consistent.
Creating databases with multiple tables
Whether your database holds 100 records or 100,000 records (or more), if you need to keep separate tables and relate them for maximum use of the information, you need a relational database — and that’s Access. How do you know whether your data needs to be in separate tables? Think about your data — is it very compartmentalized? Does it go off on tangents? Consider the following example and apply the concepts to your data and see if you need multiple tables for your database. Hint: You probably do!
The Big Organization database
Imagine you work for a very large company, and the company has data pertaining to their customers and their orders, the products the company sells, its suppliers, and its employees. For a complex database like this one, you need multiple tables, as follows:
One table houses the customer data — names, addresses, phone numbers, and email addresses.
A second table contains those customers’ orders, including the name of the customer who placed the order, the products they ordered, the salesperson who handled the sale, shipping information, and the date of the order.
A third table contains information on the products the company sells, including product numbers, supplier names, prices, and the number of items in stock.
A fourth table contains supplier data — about the companies from which the main organization obtains its inventory of products to resell to customers. The table contains the company names, their contact person, and the address, email, and phone-number information to reach them.
A fifth table contains employees’ data — from the date they were hired to their contact information to their job title — and also contains notes about them, sort of a summary of their resumes for reference.
Hopefully, as we listed those tables, you could see the way they’d be conceptually connected to each other — customers, orders, products, suppliers, and employees — and can therefore see how relationships between those tables would help the tables literally work together.
Other tables exist, too — to keep track of shipping companies and their contact information (for shipping customer orders), expenses (for the expenses incurred in running the business), and other tables that are used with the main five tables. The need for and ways to use the main tables and these additional tables are covered later in this book, as you find out how to set up tools for data entry, relate your tables, look up records within your tables, and create reports that provide varying levels of detail on all the data you’ve stored.
Because you don’t have to fill in every field for each record — in any table in the database — if you don’t have a phone number or don’t know an email address, for example, it’s okay to leave those fields blank until you’ve obtained that information.
Fail to plan? Plan to fail
Like just about any undertaking, at least considering the steps involved and the desired outcome is a good idea before you get started. Your database is no different, and has even greater needs for effective planning. If you think carefully about your database, how you use your data, and what you need to know about your employees, customers, volunteers, donors, products, or projects — whatever you’re storing information about — you can plan
How many tables you’ll need
Which data will go into which table
How you’ll use the tables together to get the reports you need
Of course, everyone forgets something, and plans change after a system has already been implemented. But don’t worry — Access isn’t so rigid that chaos will ensue if you begin building your tables and forget something (a field or two, an entire table). You can always add a field that you forgot (or that some bright spark just told you is needed) or add a new table after the fact. But planning ahead as thoroughly as possible is still essential and is definitely worth the effort.
As part of thorough planning, sketch your planned database on paper, drawing a kind of flowchart with boxes for each table and lists of fields that you’ll have in each one. Draw arrows to show how they might be related — it’s sort of like drawing a simple family tree — and you’re well on your way to a well-planned, useful database. If you don’t want to use actual paper, feel free, of course, to use any application you’ve used in the past for planning graphically — such as PowerPoint or any diagramming tool.
Here’s a handy procedure to follow if you’re new to the process of planning a database:
On paper or in a word-processing document, whichever is more comfortable, type the following:
A tentative name for your database
A list of the pieces of information you plan on getting from that database on a daily or regular basis
Based on that information, create a new list of the actual details you could store:
List every piece of information you can possibly think of about your customers, products, ideas, cases, books, works of art, students — whatever your database pertains to. Don’t be afraid to go overboard — you can always skip some of the items in the list if they don’t turn out to be things you really need to know (or can possibly find out) about each item in your database.
Take the list of fields — that’s what all those pieces of information are — and start breaking them up into logical groups.
How? Think about the fields and how they work together:
For example, if the database keeps track of a library of books, perhaps the title, publication date, publisher, ISBN (
I
nternational
S
tandard
B
ook
N
umber, which is unique for each book), price, and page count can be stored in one group, whereas author information, reviews, and lists of other titles by the same author or books on the same topic can be stored in another group. These groups become individual tables, creating your relational database of books.
Figure out what’s unique about each record. As stated in the previous point, you need a field that’s unique for each record. Although Access can create a unique value for you if no unique data exists for each record in your database, it’s often best to have such a field already in place, or to create such a field yourself. Customer numbers, student numbers, Social Security numbers, book ISBNs, catalog numbers, serial numbers — anything that isn’t the same for any two records will do.
With a big list of fields and some tentative groupings of those fields sketched out, and with an idea of which field is unique for each record, you can begin figuring out how to use the data.
Make a list of ways you might use the data, including
Reports you’d like to create, including a list of which fields should be included for each report
Other ways you can use the data — forms for looking things up, catalog data, price lists, contact lists, even labels for mailings and packaging, and so on.
List all the places your data currently resides.
This might be on slips of paper in your pocket, on cards in a box, in another program (such as Excel), or maybe through a company that sells data for marketing purposes.
With this planning done, you’re ready to start building your database. The particulars of that process come later in this chapter and in subsequent chapters, so don’t jump in yet. You should pat yourself on the back, though, because if you’ve read this procedure and applied even some of it to your potential database, you’re way ahead of the game, and we’re confident you’ll make good use of all that Access has to offer.
Databases with user forms
When you’re planning your database, another thing to consider is how the data will be entered:
If you’ll be doing the data entry yourself, perhaps you’re comfortable working in a spreadsheet-like environment (known in Access as Datasheet view), where the table is a big grid. You fill it in row by row, and each row is a record.
Figure 1-1 shows a table of volunteers in progress in Datasheet view. You decide: Is it easy to use, or can you picture yourself forgetting to move down a row and entering the wrong stuff in the wrong columns as you enter each record? As you can see, there are more fields than show in the window, so you’d be doing a lot of scrolling to the left and right to use this view.
You may want to use a
form
(shown in
Figure 1-2
) instead. A form is a specialized interface for data entry, editing, and viewing your database one record at a time, if
You like the idea of seeing and entering/editing one record at a time.
Someone else will be handling data entry.
Typing row after row of data into a big grid seems mind-numbing and likely to produce errors.
FIGURE 1-1: Datasheet view can be an easy environment for data entry. Or not.
FIGURE 1-2: Here’s a simple form for entering new records or reviewing existing ones.
The mind-numbing effect (and increased margin for error) is especially likely when you have lots of fields in a database, and the user, if working in Datasheet view, has to move horizontally through the fields. A form like the one in Figure 1-2 puts the fields in a more visually convenient format, making it easier to enter data into the fields and to see all the fields simultaneously. You can also set up forms that include only the fields you want to see or to use for data entry.
You find out all about forms in Chapter 8. If your database is large enough that you require help doing the data entry, or if it’s going to grow over time, making an ongoing data-entry process likely, Access is the tool for you. The fact that it offers simple forms of data entry/editing is reason enough to make it your database application of choice.
Databases that require special reporting
Yet another reason to use Access is the ability it gives you to create and run customized reports quickly and easily. Some database programs, especially those designed for single-table databases (known as flat-file databases), have some canned reports built in, and that’s all you can do — just select a report from the list and run the same report that every other user of that software runs.
If you’re an Excel user, your reporting capabilities are far from easy or simple, and they’re not designed for use with large databases — they’re meant for spreadsheets and small, one-table lists. Furthermore, you have to dig much deeper into Excel’s tools to get at these reports. Access, on the other hand, is a database application, so reporting is a major, up-front feature.
An example? In Excel, to get a report that groups your data by one or more of the fields in your list, you have to sort the rows in the worksheet first, using the field(s) to sort the data, and then you can create what’s known as a subtotal report. To create it, you use a dialog box that asks you about calculations you want to perform, where to place the results, and whether you’re basing a sort and/or a subtotal on more than one field. The resulting report is not designed for printing, and you have to tinker with your spreadsheet pagination (through a specialized view of the spreadsheet) to control how the report prints out.
In Access? Just fire up the Report Wizard, and you can sort your data, choose how to group it, decide which pieces of data to include in the report, and pick a visual layout and color scheme, all in one simple, streamlined, dialog box–driven process. Without requiring you to do anything, the report is ready for printing. Access is built for reporting — after all, it is a database application — and reports are one of the most (if not the most) important ways you’ll use and share your data.
Because reports are such an important part of Access, you can not only create them with minimum fuss but also customize them to create powerful documentation of your most important data:
Build a quick, simple report that just spits out whatever is in your table in a tidy, easy-to-read format. (See
Figure 1-3
for an example.)
FIGURE 1-3: Ah, simplicity. A quick report is just one click away.
Create a customized report that you design step-by-step with the help of the Report Wizard. (See
Figure 1-4
.) The report shown in the figure has the volunteers sorted by their last names. These options were easily put to work with just a few clicks.
You can really roll up your sleeves and design a new report, or play with an existing one, adding all sorts of bells and whistles.
Figure 1-5
shows this happening in Design view. Note that the report’s title (Volunteers List by Status) is selected: It has a box around it and tiny handles on the corners and sides of the box, which means you can reformat the title, change the font, size, or color of the text, or even edit the words if a new title is needed.
FIGURE 1-4: The Report Wizard creates more elaborate (but simple) reports, like this one.
FIGURE 1-5: Design view might look a little intimidating, but to really customize things, you’ll need it — and you might even enjoy it!
So, you can create any kind of custom report in Access, using any or all of your database tables and any of the fields from those tables, and you can group fields and place them in any order you want:
With the Report Wizard, you can choose from several preset layouts for your report, and you can customize all of it row by row, column by column.
You can easily add and remove fields after creating the report, should you change your mind about what’s included. If you want to place your personal stamp on every aspect of your report, you can use Design view to do the following:
Add titles, instructional or descriptive text boxes, and graphics.
Set up customized headers and footers to include any information you want to appear on all the report’s pages.
If all this sounds exciting, or at least interesting, then you’re really on the right track with Access. The need to create custom reports is a major reason to use Access; you can find out about all these reporting options in Chapters 18 through 21. That’s right: Four whole chapters — all devoted to reporting. It must be a big feature in Access!
Reach Out with SharePoint
Depending on your or your company’s networking and security needs, you may have used SharePoint along the way, or perhaps you’re using it daily to store and work with the files you need to share — thus the name — with others. At the very least, you’ve probably heard of SharePoint and how it provides the ability to see and use your Access data from anywhere — using desktop applications, a web browser, or even your phone. And it really does all that (and more), helping you manage your documents and collaborate with co-workers via the company network. Simply click the Save Database As command in the Access File tab’s panel (see Figure 1-6), and you’re on your way to publishing your database to SharePoint, which means you can access it from pretty much everywhere (nearly a requirement these days, because remote work has become so common). Of course, if you or your company doesn’t have a SharePoint server, you won’t be able to make use of this, and you don’t need to concern yourself with this section.
As shown in Figure 1-6, the Save As options include regular old Save Database As, to save your existing database with a new name or in some format other than as an Access database; and Save Object As, to save a table, form, query, or report with a new name. You can also choose from several Advanced options to save the database as a package (to distribute your Access applications) or as an executable file (a single file that when run by the recipient, opens a database application), to back up the database, and to use the aforementioned SharePoint.
FIGURE 1-6: The Save As command offers choices for … you guessed it … saving your database.
How Access Works and How You Work with It
When you look at the main applications in Microsoft Office — Word, Excel, PowerPoint, Outlook, and of course, Access — you’ll see some consistent features throughout the suite. There are big differences, too, and that’s where books like this one come in handy, helping you deal with what’s different and not terribly obvious to a new user.
Access has several features in common with the rest of the applications in the Microsoft Office suite. You’ll find the same buttons on several of the tabs, and the Quick Access Toolbar (demonstrated in Chapter 2) appears in all the applications.
If you already know how to open, save, and print in, say, Word, you’re probably ready to do the same things in Access without any difficulty.
To make sure you’re totally Access-ready, here’s a look at the basic procedures that can give you a solid foundation on which to build.
Opening Access
Access opens in any one of several ways, and like a restaurant with a very comprehensive menu, some people will love all the choices, and others will say, “I can’t decide! There are just too many options!” Which camp will you fall into? Let’s see. Here are all the ways to open Access:
