Access 2019 For Dummies

 
 
Standards Information Network (Verlag)
  • 1. Auflage
  • |
  • erschienen am 8. Oktober 2018
  • |
  • 432 Seiten
 
E-Book | ePUB mit Adobe-DRM | Systemvoraussetzungen
978-1-119-51328-5 (ISBN)
 
Easy steps to practical databases

People who really know how to build, populate, and simplify databases are few and far between. Access 2019 For Dummies is here to help you join the ranks of office heroes who possess these precious skills.

This book offers clear and simple advice on how to build and operate databases as well as create simple forms, import data from outside sources, query databases for information, and share knowledge in reports. In short, it's the book that holds all the secrets behind the mysteries of Access!



Build effective databases from the ground up
Simplify your data entry with forms and tables
Write queries that produce answers to your data questions
Simplify input with forms

There's no time like the present to get your hands on the insight that database beginners need to become Access gurus.
1. Auflage
  • Englisch
  • Newark
  • |
  • USA
John Wiley & Sons Inc
  • Reflowable
  • 12,92 MB
978-1-119-51328-5 (9781119513285)

weitere Ausgaben werden ermittelt
Laurie Ann Ulrich has trained more than 20,000 people to make better, more creative use of their computers. She has created online courses and written more than 30 books.



Ken Cook built and manages a computer consulting business that has helped users for over 20 years. He's an expert at creating Microsoft Office and Access database solutions and teaches online Access courses.
Introduction 1

About This Book 1

Foolish Assumptions 2

Icons Used in This Book 2

Where to Go from Here 3

Part 1: Basic Training 5

Chapter 1: Access 2019 Basic Training 7

What Is Access Good For, Anyway? 8

Building big databases 8

Creating databases with multiple tables 9

Databases with user forms 12

Databases that require special reporting 13

What's New in Access 2019? 16

Reach Out with SharePoint 17

How Access Works and How You Work with it 18

Opening Access 18

Selecting a starting point 19

Now What? 23

Chapter 2: Navigating the Access Workspace 25

Diving Right In 27

Working with Onscreen Tools in Access 29

Clicking tabs 30

Using buttons 31

The File tab and Quick Access tools 31

Accessing panes, panels, and context-sensitive tools 33

Customizing the Access Workspace 34

Repositioning the Quick Access Toolbar 34

Adding buttons to the Quick Access Toolbar 35

Removing buttons from the Quick Access Toolbar 37

Minimizing the Ribbon 37

Working with ScreenTips 38

Mousing Around 41

Navigating Access with the Alt Key 41

Chapter 3: Database Basics 43

Database Lingo 43

Data, no matter how you pronounce it 44

Fields of dreams (or data) 44

Records 45

Tables 45

The database 45

Field Types and Uses 46

Choosing Between Flat and Relational Databases 51

Isolationist tables 51

Tables that mix and mingle 52

Building a Database 53

Adding and Removing Tables 57

One more, please 57

Oops, I didn't mean to do that 59

Part 2: Getting it All on the Table 61

Chapter 4: Sounds Like a Plan 63

Planning Your Database Tables 63

Reviewing fields 64

Determining data types 64

The new normal 66

Understanding normal forms 66

Normalizing your tables 67

Building Tables in Design View 70

Creating fields 70

Setting data types 72

Chapter 5: Table Tune Ups 75

The Primary Key to Success 76

The lowdown on primary keys 76

Creating a primary key 77

Making Tables Get Along 79

Rules of relationships 79

Relationship types 80

Building Table Relationships 82

The Relationships window 82

Table relationships 83

Indexing for Faster Queries 87

Creating your own index 88

Adding and removing indexes 91

Chapter 6: Remodeling Your Data 93

Opening a Table for Editing 94

Inserting Records and Fields 97

Adding a record 97

Inserting a field 98

Deleting a field 101

Modifying Field Content 102

Name-Calling 103

Renaming fields 104

Renaming a table 106

Turn Uh-Oh! into Yee-Hah! 108

Chapter 7: Types, Masks, and Triggers 109

Access Table Settings 110

Field Data Formats 112

Text fields 113

Number and currency fields 115

Date/time fields 118

Yes/No fields 119

Gaining Control of Data Entry 121

You really need to put a mask on those fields 121

To require or not to require 128

Making your data toe the line with validation 129

Give Your Fingers a Mini Vacation by Default 131

Part 3: Data Management Mania 133

Chapter 8: A Form for All Reasons 135

Generating Forms 135

Keeping it simple: Form tools 137

Granting most wishes: The Form Wizard 139

Customizing Form Parts 143

Taking the Layout view 143

The theme's the thing 145

Managing form controls 145

Managing Data in Form View 150

Navigating and finding records 150

Saving, clearing, and deleting 151

Chapter 9: Importing and Exporting Data 153

Retrieving Data from Other Sources 154

Translating data formats 154

Importing and linking 157

Hit the Road, Data 162

Export formats 162

Exporting table or query data 163

Exporting a report to PDF 165

Chapter 10: Automatically Editing Data 167

Please Read This First! 167

Creating Consistent Corrections 170

Using Queries to Automate the Editing Process 173

Looking for duplicate records 173

Running the Find Duplicates Query Wizard 174

Chapter 11: Access and the Web 179

How Access Works with the Web 179

Understanding Office 365 180

Connect Office 365 to Access 2019 181

Your data and the cloud 182

Upload your Access desktop data to the cloud 183

Click! Using Hyperlinks in your desktop Access database 186

Adding a Hyperlink field to your desktop database table 187

Fine-tuning your hyperlinks 188

Testing links 190

Part 4: The Power of Questions 191

Chapter 12: Finding, Filtering, and Sorting Your Data - Fast 193

Using the Find Command 194

Finding anything fast 194

Shifting Find into high gear 196

Sorting Alphabetically and Numerically 199

Sorting by a single field 199

Sorting on more than one field 200

Fast and Furious Filtering 201

Filtering by a field's content 202

Filter by selection 203

Filter by Form 204

Unfiltering Filter by Form 207

Filter by excluding selection 207

Chapter 13: I Was Just Asking for Answers 209

Simple (Yet Potent) Filter and Sort Tools 210

Filter things first 210

Fact-finding with fun, fast filtering 213

Here's the "advanced" part 214

Select Queries 220

Solid relationships are the key to getting it all (from your tables) 221

Running the Query Wizard 221

Getting Your Feet Wet with Ad Hoc Queries 227

Adding the finishing touches 230

Saving the query 231

Running your query 233

Chapter 14: I Want These AND Those OR Them 235

Working with AND and/or OR 236

Data from here to there 237

Using multiple levels of AND 238

Establishing criteria with OR 239

Combining AND with OR and OR with AND 240

Chapter 15: Number Crunching with the Total Row 243

Say Hello to the Total Row 243

Adding the Total Row to Your Queries 245

Working Out the Total Row 246

Putting it together with Group By 247

Well, that certainly sums it up 248

Counting, the easy way 250

Narrowing the results with Where 251

Creating Your Own Top-Ten List 252

Choosing the Right Field for the Summary Instruction 254

Chapter 16: Express Yourself with Formulas 255

A Simple Calculation 256

Complex Calculations 258

Calculate 'til you drop! 259

Using one calculation in another 259

Using parameter queries to ask for help 261

"Adding" words with text formulas 262

Hooray for Expression Builder 264

Chapter 17: Take Charge with Action Queries 269

Easy Update 270

Add Records in a Flash 274

Quick Cleanup 277

Part 5: Simple and Snazzy Reporting 281

Chapter 18: Fast and Furious Automatic Reporting 283

Quick and Not-S0-Dirty Automatic Reporting 284

Creating a quick, one-table report 285

Starting the Report Wizard 289

Previewing Your Report 294

Zooming in and out and all around 295

Pop goes the menu 297

Beauty Is Only Skin (Report) Deep 298

The Print Options tab 298

The Page tab 300

The Columns tab 301

Chapter 19: Professionally Designed Reports Made Easy 303

Report Repairs 304

Report Organization 305

Structural devices 306

Page breaks 308

Formatting Stuff 310

Adding color 313

Relocation, relocation, relocation 314

One size does not fit all 316

Spaced-out controls 317

Borderline beauty 318

Tweaking your text 321

Sneaking a Peek 323

Getting a Themes Makeover 324

Adding More Design Elements 325

Drawing lines 325

Pretty as a picture 326

Adding a logo 327

Chapter 20: Groups and Page Breaks, Headers and Footers 329

A Place for Everything and Everything in Its Place 330

Layout basics 330

Sections 332

Grouping your records 335

So you want more? 338

Customizing Properties 338

Controlling report and page headings 341

Adjusting individual sections 343

Itemized adjustments 344

Chapter 21: Mailings to the Masses 351

Massive Mailings with the Label Wizard 351

Part 6: The Parts of Tens 359

Chapter 22: Ten Common Problems 361

That's Just Not Normal 362

You Type 73.725, but it Changes to 74 363

The Words They Are A-Changing 363

Was There and Now It's Gone 364

Undo 365

Search for the missing record 365

Backup recovery 365

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

The Dreaded Parameter Dialog Box 367

The Slowest Database in Town 368

Your Database File Is as Big as a House 369

You Get a Mess When Importing Your Spreadsheet 371

We're Sorry; Your Database File Is Corrupt 371

Chapter 23: Ten Uncommon Tips 373

Document Everything as Though One Day You'll Be Questioned by the FBI 374

Keep Your Fields as Small as Possible 375

Use Number Fields for Real Numbers 376

Validate Your Data 376

Use Understandable Names to Keep Things Simple 377

Delete with Great Caution 377

Backup, Backup, Backup 377

Think, Think, and Think Again 378

Get Organized and Stay Organized 378

There's No Shame in Asking for Help 379

Appendix A: Getting Help 381

Index 383

Chapter 1

Access 2019 Basic Training


IN THIS CHAPTER

Deciding when to use Access

Discovering what's new in Access 2019

Unlocking the basics of working with Access

Figuring out how to get started

Access 2019, the latest version of the Microsoft Office database application, has always been a powerful program, and this version is no different. Chances are, you're reading this book because all that power makes Access an application that's not so easy to learn on your own. If you're hoping to unleash that power for your data, you'll need us. So, good decision to buy this book!

Now, all that power and the need for our book aside, with the very basic parts of Access, 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.

In this chapter, you'll discover what Access does best (and when you might want to use another tool instead), and you'll get a look at what's new and improved in Access 2019 (compared to Access 2016). You'll see how it does what it does, and hopefully you'll begin to understand and absorb some basic terminology.

Now, don't panic; nobody's expecting you to memorize tons of complex vocabulary or anything scary like that. 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. Well, the list of what you can do with it is a lot longer than the list of what you can't do with it - of course, especially if you leave things like "wash your car" and "put away the dishes" off the "can't do" list. When it comes to data organization, storage, and retrieval, Access is at the head of the class.

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). In addition, you can't use Excel to set up anything beyond a simple list that can be sorted and filtered. So anything with a lot of records and complex data is best done in Access.

Some reasons why Access handles big databases well are:

  • Typically, a big database has big data-entry needs. Access offers not only forms but also features that can create a quick form through which someone can enter all those records. This can make data entry easier and faster and can reduce the margin of error significantly. (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 such as 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 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.

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 the customers' orders, including the name of the customer who placed the order, 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.

Other tables exist, too - to keep a list of shipping companies and their contact information (for shipping customer orders), an expense table (for the expenses incurred in running the business), and other tables that are used with the main four 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, look up records, 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

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.

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.

Here's a handy procedure to follow if you're new to the process of planning a database:

  1. On paper or in a word-processing document, whichever is more comfortable, type the...

Dateiformat: ePUB
Kopierschutz: Adobe-DRM (Digital Rights Management)

Systemvoraussetzungen:

Computer (Windows; MacOS X; Linux): Installieren Sie bereits vor dem Download die kostenlose Software Adobe Digital Editions (siehe E-Book Hilfe).

Tablet/Smartphone (Android; iOS): Installieren Sie bereits vor dem Download die kostenlose App Adobe Digital Editions (siehe E-Book Hilfe).

E-Book-Reader: Bookeen, Kobo, Pocketbook, Sony, Tolino u.v.a.m. (nicht Kindle)

Das Dateiformat ePUB ist sehr gut für Romane und Sachbücher geeignet - also für "fließenden" Text ohne komplexes Layout. Bei E-Readern oder Smartphones passt sich der Zeilen- und Seitenumbruch automatisch den kleinen Displays an. Mit Adobe-DRM wird hier ein "harter" Kopierschutz verwendet. Wenn die notwendigen Voraussetzungen nicht vorliegen, können Sie das E-Book leider nicht öffnen. Daher müssen Sie bereits vor dem Download Ihre Lese-Hardware vorbereiten.

Bitte beachten Sie bei der Verwendung der Lese-Software Adobe Digital Editions: wir empfehlen Ihnen unbedingt nach Installation der Lese-Software diese mit Ihrer persönlichen Adobe-ID zu autorisieren!

Weitere Informationen finden Sie in unserer E-Book Hilfe.


Download (sofort verfügbar)

20,99 €
inkl. 7% MwSt.
Download / Einzel-Lizenz
ePUB mit Adobe-DRM
siehe Systemvoraussetzungen
E-Book bestellen