Access 2016 For Dummies E-Book

Laurie A. Ulrich

• Herausgeber: John Wiley & Sons
• Kategorie: Wissenschaft und neue Technologien
• Sprache: Englisch

Veröffentlichungsjahr: 2015

Beschreibung

Your all-access guide to all things Access 2016

If you don't know a relational database from an isolationist table—but still need to figure out how to organize and analyze your data—Access 2016 For Dummies is for you. Written in a friendly and accessible manner, it assumes no prior Access or database-building knowledge and walks you through the basics of creating tables to store your data, building forms that ease data entry, writing queries that pull real information from your data, and creating reports that back up your analysis. Add in a dash of humor and fun, and Access 2016 For Dummies is the only resource you'll need to go from data rookie to data pro!

This expanded and updated edition of Access For Dummies covers all of the latest information and features to help data newcomers better understand Access' role in the world of data analysis and data science. Inside, you'll get a crash course on how databases work—and how to build one from the ground up. Plus, you'll find step-by-step guidance on how to structure data to make it useful, manipulate, edit, and import data into your database, write and execute queries to gain insight from your data, and report data in elegant ways.

• Speak the lingo of database builders and create databases that suit your needs
• Organize your data into tables and build forms that ease data entry
• Query your data to get answers right
• Create reports that tell the story of your data findings

If you have little to no experience with creating and managing a database of any sort, Access 2016 For Dummies is the perfect starting point for learning the basics of building databases, simplifying data entry and reporting, and improving your overall data skills.

Leseprobe

Access 2016® For Dummies®

Published by: John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030-5774, www.wiley.com

Copyright © 2016 by John Wiley & Sons, Inc., Hoboken, New Jersey

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, [phone], fax [phone], 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. Android is a trademark of Google, Inc. 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.

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 [phone]. For technical support, please visit www.wiley.com/techsupport.

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: 2015950108

ISBN: 978-1-119-08310-8

ISBN 978-1-119-08327-6 (ePDF); ISBN 978-1-119-08308-5 (ePUB)

Access 2016® For Dummies®

Visit www.dummies.com/cheatsheet/access2016 to view this book's cheat sheet.

Table of Contents

Cover

Introduction

About This Book

Foolish Assumptions

Icons Used in This Book

Beyond the Book

Where to Go from Here

Part I: Getting Started with Access 2016

Chapter 1: Access 2016 Basic Training

What Is Access Good For, Anyway?

What’s New in Access 2016?

Reach out with SharePoint

How Access Works and How You Work with It

Now What?

Chapter 2: Navigating 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

Chapter 4: Sounds Like a Plan

Planning Your Database Tables

Building Tables in Design View

Part II: Getting It All on the Table

Chapter 5: Table Tune Ups

The Primary Key to Success

Making Tables Get Along

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 III: 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: Automatic Data Editing

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 Office 365

Connect Office 365 to Access 2016

Meet the Custom Web App

Click! Using Hyperlinks in Your Desktop Access Database

Part IV: The Power of Questions

Chapter 12: Finding, Filtering, and Sorting Your Data — Fast

Using the Find Command

Sorting from A to Z or Z to A

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

Working Out the Total Row

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

Hooray for Expression Builder

Chapter 17: Take Charge with Action Queries

Easy Update

Add Records in a Flash

Quick Cleanup

Part V: Simple and Snazzy Reporting

Chapter 18: Fast and Furious Automatic Reporting

Quick and Not-S0-Dirty Automatic Reporting

Previewing Your Report

Beauty Is Only Skin (Report) Deep

Chapter 19: Professionally Designed Reports Made Easy

Report Repairs

Report Organization

Formatting Stuff

Sneaking a Peek

Getting a Themes Makeover

Adding More Design Elements

Chapter 20: Headers and Footers and Groups, Oh My!

A Place for Everything and Everything in Its Place

Customizing Properties

Chapter 21: Magical Mass Mailings

Massive Mailings with the Label Wizard

Part VI: More Power to You

Chapter 22: Analyze This!

From Flat File to Relational Tables with Table Analyzer

Record Database Object Information with the Database Documenter

Improve Database Performance without Steroids

Chapter 23: Steer Users in the Right Direction with Navigation Forms

The Comings and Goings of a Navigation Form

Am I in the Right Place? Testing Navigation Forms

Maintaining the Navigation Form

Displaying the Navigation Form at Startup

Part VII: The Part of Tens

Chapter 24: Ten Common Problems

That’s Just Not Normal

You Type 73.725, but It Changes to 74

The Words They Are A-Changing

Was There and Now It’s Gone

You Run a Query, but the Results Aren’t What You Expect

The Dreaded Parameter Dialog Box

The Slowest Database in Town

Your Database File Is as Big as a Whale

You Get a Mess When Importing Your Spreadsheet

We’re Sorry; Your Database File Is Corrupt

Chapter 25: Ten Uncommon Tips

Document Everything as Though One Day You’ll be Questioned by the FBI

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

Think, Think, and Think Again

Get Organized and Stay Organized

There’s No Shame in Asking for Help

Appendix A: Getting Help

Asking Access for Help

Online Help

Who’s Our Next Caller?

About the Authors

Cheat Sheet

Advertisement Page

Connect with Dummies

End User License Agreement

Guide

Cover

Table of Contents

Begin Reading

Pages

ii

1

2

3

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

97

98

99

100

101

102

103

104

105

106

107

108

109

110

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

158

159

160

161

162

163

164

165

166

167

168

169

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

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

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

247

248

249

250

251

252

253

254

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

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

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

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

369

370

371

372

373

374

375

376

377

378

379

380

381

382

383

384

385

386

387

388

389

391

392

393

394

395

396

397

398

399

400

401

402

403

404

405

406

407

408

409

410

411

412

413

414

415

441

443

444

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 2016, and of course, as the authors, we believe this was a wise decision. We, the authors, base this belief on the fact that both of us have 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 non-profit 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 leafing through your files 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 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 up this book. Hang on to it. Clutch it to your chest and run gleefully from the store, or click the Add to Shopping Cart button and sit back with an expression of satisfaction and accomplishment on your face, because you’ve done a smart thing (if we don’t say so ourselves). When you get home, or when the book arrives in person (or when you download it to your hand-held device), start reading — 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 2016 For Dummies. In the following pages, we presume that you …

Know the basics of Windows 7 and Windows 8.1 — how to open programs, save your files, create folders, find your files once 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 Windows 7, 8.1, or 10

If your computer uses Windows 98, 2000, or Vista, you can’t run Access 2016.

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.

This icon signifies that you’ll find additional relevant content at www.dummies.com/extras/access2016.

Beyond the Book

In addition to the content in this book, you’ll find some extra content available at the www.dummies.com website:

The Cheat Sheet for this book at

www.dummies.com/cheatsheet/access2016

Online articles covering additional topics atwww.dummies.com/extras/access2016

Here you’ll find the articles referred to on the page that introduces each part of the book. So, feel free to visit www.dummies.com/extras/access2016. You’ll feel at home there … find coffee and donuts … okay, maybe not the coffee and donuts (hard to deliver over the ether), but you can find information about setting up budgets in QBO and details on converting a desktop QuickBooks company to a QBO company.

Download files for this book:

Want to explore the database used in the book or practice importing and updating data? Simply go to

www.dummies.com/go/access2016

. Here you will find sample files used in

Chapters 9

and

17

as well as the Lancaster Food Pantry Access database used throughout the book.

Updates to this book, if any, at

www.dummies.com/go/access2016fd

Where to Go from Here

Now nothing’s left to hold you back from the thrills, chills, and power of Access. Hold on tight to your copy of Access 2016 For Dummies and leap into Access. Not sure where to start? See if you spot yourself in these options:

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 I

Getting Started with Access 2016

Visit www.dummies.com for great Dummies content online.

In this part …

Discover what Access is and does and what’s new in Access 2016.

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.

Visit

www.dummies.com

for great Dummies content online.

Chapter 1

Access 2016 Basic Training

In This Chapter

Deciding when to use Access

Discovering what’s new in Access 2016

Unlocking the basics of working with Access

Figuring out how to get started

Access 2016, the most recent version of the Microsoft Office database application, continues to be a very powerful program. You probably already know that, and perhaps that power is what made you choose Access for managing your data and to also reach for this book. Great decisions so far!

For all of its power, Access is pretty friendly to new users. In fact, with just the basic functionality that you’ll discover in this book, you’ll be able to put Access through many of its most important paces, yet 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. There. Don’t you feel better now?

You don’t have to use every feature and tool and push the edges of the Access envelope. 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.