Access 2013 Bible E-Book

Michael Alexander

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

Seitenzahl: 1626

Veröffentlichungsjahr: 2013

Beschreibung

A comprehensive reference to the updated and new features of Access 2013

As the world's most popular database management tool, Access enables you to organize, present, analyze, and share data as well as build powerful database solutions. However, databases can be complex. That's why you need the expert guidance in this comprehensive reference. Access 2013 Bible helps you gain a solid understanding of database purpose, construction, and application so that whether you're new to Access or looking to upgrade to the 2013 version, this well-rounded resource provides you with a thorough look at everything Access can do.

• Explains how to create tables, manipulate datasheets, and work with multiple tables
• Teaches you how to apply the seven-step design method to build databases that are tailored to your needs
• Covers building forms with wizards, creating bound and unbound forms, and adding data validation
• Shows you ways to automate query parameters, create functions and subroutines, and add programmed error routines
• Features a bonus website with content that contains all source code from the book as well as bonus shareware, freeware, trial, demo, and evaluation programs

If you are looking for a comprehensive book on all things Access, look no further than Access 2013 Bible.

Leseprobe

Access® 2013 Bible

Table of Contents

Introduction

Part I: Access Building Blocks

Chapter 1: An Introduction to Database Development

The Database Terminology of Access

Databases

Tables

Records and fields

Values

Relational Databases

Access Database Objects

Datasheets

Queries

Data-entry and display forms

Reports

Database objects

A Five-Step Design Method

Step 1: The overall design — from concept to reality

Step 2: Report design

Step 3: Data design

Step 4: Table design

Step 5: Form design

Chapter 2: Getting Started with Access

The Access Welcome Screen

How to Create a Blank Desktop Database

The Access 2013 Interface

The Navigation pane

The Ribbon

The Quick Access toolbar

Part II: Understanding Access Tables

Chapter 3: Creating Access Tables

Table Types

Object tables

Transaction tables

Join tables

Creating a New Table

Designing tables

Using the Design tab

Working with fields

Creating tblCustomers

Using AutoNumber fields

Completing tblCustomers

Changing a Table Design

Inserting a new field

Deleting a field

Changing a field location

Changing a field name

Changing a field size

Handling data conversion issues

Assigning field properties

Understanding tblCustomers Field Properties

Setting the Primary Key

Choosing a primary key

Creating the primary key

Creating composite primary keys

Indexing Access Tables

The importance of indexes

Multiple-field indexes

When to index tables

Printing a Table Design

Saving the Completed Table

Manipulating Tables

Renaming tables

Deleting tables

Copying tables in a database

Copying a table to another database

Adding Records to a Database Table

Understanding Attachment Fields

Chapter 4: Understanding Table Relationships

Building Bulletproof Databases

Data Normalization and Denormalization

First normal form

Second normal form

Third normal form

Denormalization

Table Relationships

Connecting the data

One-to-one

One-to-many

Many-to-many

Integrity Rules

No primary key can contain a null value

All foreign key values must be matched by corresponding primary keys

Keys

Deciding on a primary key

Looking at the benefits of a primary key

Designating a primary key

Creating relationships and enforcing referential integrity

Viewing all relationships

Deleting relationships

Following application-specific integrity rules

Chapter 5: Working with Access Tables

Understanding Datasheets

Looking at the Datasheet Window

Moving within a datasheet

Using the Navigation buttons

Examining the Datasheet Ribbon

Opening a Datasheet

Entering New Data

Saving the record

Understanding automatic data-type validation

Knowing how properties affect data entry

Navigating Records in a Datasheet

Moving between records

Finding a specific value

Changing Values in a Datasheet

Manually replacing an existing value

Changing an existing value

Using the Undo Feature

Copying and Pasting Values

Replacing Values

Adding New Records

Deleting Records

Displaying Records

Changing the field order

Changing the field display width

Changing the record display height

Changing display fonts

Displaying cell gridlines and alternate row colors

Aligning data in columns

Hiding and unhiding columns

Freezing columns

Saving the changed layout

Saving a record

Sorting and Filtering Records in a Datasheet

Sorting your records with QuickSort

Filtering a selection

Filtering by form

Aggregating Data

Printing Records

Printing the datasheet

Using the Print Preview window

Chapter 6: Importing and Exporting Data

How Access Works with External Data

Types of external data

Ways of working with external data

Options for Importing and Exporting

How to Import External Data

Importing from another Access database

Importing from an Excel spreadsheet

Importing a SharePoint list

Importing data from text files

Importing and exporting XML documents

Importing and exporting HTML documents

Importing Access objects other than tables

Importing an Outlook folder

How to Export to External Formats

Exporting objects to other Access databases

Exporting through ODBC drivers

Exporting to Word

Publishing to PDF or XPS

Chapter 7: Linking to External Data

Linking External Data

Identifying linked tables

Limitations of linked data

Linking to other Access database tables

Linking to ODBC data sources

Linking to non-database data

Working with Linked Tables

Setting view properties

Setting relationships

Optimizing linked tables

Deleting a linked table reference

Viewing or changing information for linked tables

Refreshing linked tables

Splitting a Database

The benefits of splitting a database

Knowing where to put which objects

Using the Database Splitter add-in

Part III: Working with Access Queries

Chapter 8: Selecting Data with Queries

Introducing Queries

What queries are

What queries can do

What queries return

Creating a Query

Adding fields to your queries

Running your query

Working with Query Fields

Selecting a field in the QBE grid

Changing field order

Resizing columns in the QBE grid

Removing a field

Inserting a field

Hiding a field

Changing the sort order of a field

Adding Criteria to Your Queries

Understanding selection criteria

Entering simple string criteria

Entering other simple criteria

Printing a Query's Recordset

Saving a Query

Creating Multi-Table Queries

Viewing table names

Adding multiple fields

Recognizing the limitations of multi-table queries

Overcoming query limitations

Working with the Table Pane

Looking at the join line

Moving a table

Removing a table

Adding more tables

Creating and Working with Query Joins

Understanding joins

Leveraging ad hoc table joins

Specifying the type of join

Deleting joins

Chapter 9: Using Operators and Expressions in Access

Introducing Operators

Types of operators

Operator precedence

Using Operators and Expressions in Queries

Using query comparison operators

Understanding complex criteria

Using functions in select queries

Referencing fields in select queries

Entering Single-Value Field Criteria

Entering character (Text or Memo) criteria

The Like operator and wildcards

Specifying non-matching values

Entering numeric criteria

Entering true or false criteria

Entering OLE object criteria

Using Multiple Criteria in a Query

Understanding an Or operation

Specifying multiple values with the Or operator

Using the Or cell of the QBE pane

Using a list of values with the In operator

Using And to specify a range

Using the Between...And operator

Searching for null data

Entering Criteria in Multiple Fields

Using And and Or across fields in a query

Specifying Or criteria across fields of a query

Using And and Or together in different fields

A complex query on different lines

Chapter 10: Going Beyond Select Queries

Aggregate Queries

Creating an aggregate query

About aggregate functions

Action Queries

Make-table queries

Delete queries

Append queries

Update queries

Crosstab Queries

Using the Crosstab Query Wizard

Manually creating a crosstab query

Part IV: Analyzing Data in Access

Chapter 11: Transforming Data in Access

Finding and Removing Duplicate Records

Defining duplicate records

Finding duplicate records

Removing duplicate records

Common Transformation Tasks

Filling in blank fields

Concatenating

Changing case

Removing leading and trailing spaces from a string

Finding and replacing specific text

Adding your own text in key positions within a string

Parsing strings using character markers

Chapter 12: Working with Calculations and Dates

Using Calculations in Your Analyses

Common calculation scenarios

Constructing calculations with the Expression Builder

Common calculation errors

Using Dates in Your Analyses

Simple date calculations

Advanced analysis using functions

Chapter 13: Performing Conditional Analyses

Using Parameter Queries

How parameter queries work

Ground rules of parameter queries

Working with parameter queries

Using Conditional Functions

The IIf function

The Switch function

Comparing the IIf and Switch functions

Chapter 14: Fundamentals of Using Access SQL

Understanding Basic SQL

The SELECT statement

The WHERE clause

Making sense of joins

Getting Fancy with Advanced SQL Statements

Expanding your search with the Like operator

Selecting unique values and rows without grouping

Grouping and aggregating with the GROUP BY clause

Setting sort order with the ORDER BY clause

Creating aliases with the AS clause

Showing only the SELECT TOP or SELECT TOP PERCENT

Performing action queries via SQL statements

Creating crosstabs with the TRANSFORM statement

Using SQL Specific Queries

Merging datasets with the UNION operator

Creating a table with the CREATE TABLE statement

Manipulating columns with the ALTER TABLE statement

Creating pass-through queries

Chapter 15: Subqueries and Domain Aggregate Functions

Enhancing Your Analyses with Subqueries

Why use subqueries?

Subquery ground rules

Creating subqueries without typing SQL statements

Using IN and NOT IN with subqueries

Using subqueries with comparison operators

Using subqueries as expressions

Using correlated subqueries

Using subqueries within action queries

Domain Aggregate Functions

Understanding the different domain aggregate functions

Examining the syntax of domain aggregate functions

Using domain aggregate functions

Chapter 16: Running Descriptive Statistics in Access

Basic Descriptive Statistics

Running descriptive statistics with aggregate queries

Determining rank, mode, and median

Pulling a random sampling from your dataset

Advanced Descriptive Statistics

Calculating percentile ranking

Determining the quartile standing of a record

Creating a frequency distribution

Part V: Working with Access Forms and Reports

Chapter 17: Creating Basic Access Forms

Formulating Forms

Creating a new form

Looking at special types of forms

Resizing the form area

Saving your form

Working with Controls

Categorizing controls

Adding a control

Selecting and deselecting controls

Manipulating controls

Introducing Properties

Displaying the Property Sheet

Getting acquainted with the Property Sheet

Changing a control's property setting

Naming control labels and their captions

Chapter 18: Working with Data on Access Forms

Using Form View

Looking at the Home tab of the Ribbon

Navigating among fields

Moving among records in a form

Changing Values in a Form

Knowing which controls you can't edit

Working with pictures and OLE objects

Entering data in the Long Text field

Entering data in the Date field

Using option groups

Using combo boxes and list boxes

Switching to Datasheet view

Saving a record

Printing a Form

Working with Form Properties

Changing the title bar text with the Caption property

Creating a bound form

Specifying how to view the form

Removing the Record Selector

Looking at other form properties

Adding a Form Header or Footer

Working with Section Properties

The Visible property

The Height property

The Back Color property

The Special Effect property

The Display When property

The printing properties

Changing the Layout

Changing a control's properties

Setting the tab order

Modifying the format of text in a control

Using the Field List to add controls

Converting a Form to a Report

Chapter 19: Working with Form Controls

Setting Control Properties

Customizing default properties

Looking at common controls and properties

Creating a Calculated Control

Working with Subforms

Form Design Tips

Using the Tab Stop property

Tallying check boxes

Using SQL for a faster refresh

Setting up combo boxes and list boxes

Tackling Advanced Forms Techniques

Using the Page Number and Date/Time controls

Using the Image control

Morphing a control

Using the Format Painter

Offering more end-user help

Adding background pictures

Limiting the records shown on a form

Using the Tab Control

Using Dialog Boxes to Collect Information

Designing the query

Setting up the command buttons

Adding a default button

Setting a Cancel button

Removing the control menu

Designing a Form from Scratch

Creating the basic form

Creating a subform

Adding the subform

Changing the form's behavior

Changing the form's appearance

Chapter 20: Presenting Data with Access Reports

Introducing Reports

Identifying the different types of reports

Distinguishing between reports and forms

Creating a Report, from Beginning to End

Defining the report layout

Assembling the data

Creating a report with the Report Wizard

Printing or viewing the report

Saving the report

Banded Report Design Concepts

The Report Header section

The Page Header section

The Group Header section

The Detail section

The Group Footer section

The Page Footer section

The Report Footer section

Creating a Report from Scratch

Creating a new report and binding it to a query

Defining the report page size and layout

Placing controls on the report

Resizing a section

Working with text boxes

Changing label and text-box control properties

Growing and shrinking Text Box controls

Sorting and grouping data

Sorting data within groups

Adding page breaks

Improving the Report's Appearance

Adjusting the page header

Creating an expression in the group header

Creating a report header

Chapter 21: Advanced Access Report Techniques

Grouping and Sorting Data

Grouping data alphabetically

Grouping on date intervals

Hiding repeating information

Hiding a page header

Starting a new page number for each group

Formatting Data

Creating numbered lists

Adding bullet characters

Adding emphasis at runtime

Avoiding empty reports

Inserting vertical lines between columns

Adding a blank line every n records

Even-odd page printing

Using different formats in the same text box

Centering the title

Easily aligning control labels

Micro-adjusting controls

Adding Data

Adding more information to a report

Adding the user's name to a bound report

Trying More Techniques

Displaying all reports in a combo box

Fast printing from queried data

Using snaking columns in a report

Exploiting two-pass report processing

Assigning unique names to controls

Part VI: Access Programming Fundamentals

Chapter 22: Using Access Macros

An Introduction to Macros

Creating a macro

Assigning a macro to an event

Understanding Macro Security

Enabling sandbox mode

The Trust Center

Multi-Action Macros

Submacros

Conditions

Opening reports using conditions

Multiple actions in conditions

Temporary Variables

Enhancing a macro you've already created

Using temporary variables to simplify macros

Using temporary variables in VBA

Error Handling and Macro Debugging

The OnError action

The MacroError object

Debugging macros

Embedded Macros

Macros versus VBA Statements

Choosing between macros and VBA

Converting existing macros to VBA

Chapter 23: Getting Started with Access VBA

Introducing Visual Basic for Applications

Understanding VBA Terminology

Starting with VBA Code Basics

Creating VBA Programs

Modules and procedures

Working in the code window

Understanding VBA Branching Constructs

Branching

Looping

Working with Objects and Collections

An object primer

Properties and methods

The With statement

The For Each statement

Exploring the Visual Basic Editor

The Immediate window

The Project Explorer

The Object Browser

VBE options

Chapter 24: Mastering VBA Data Types and Procedures

Using Variables

Naming variables

Declaring variables

Working with Data Types

Comparing implicit and explicit variables

Forcing explicit declaration

Using a naming convention

Understanding variable scope and lifetime

Using constants

Working with arrays

Understanding Subs and Functions

Understanding where to create a procedure

Calling VBA procedures

Creating subs

Creating Functions

Handling parameters

Calling a function and passing parameters

Creating a function to calculate sales tax

Simplifying Code with Named Arguments

Chapter 25: Understanding the Access Event Model

Programming Events

Understanding how events trigger VBA code

Creating event procedures

Identifying Common Events

Form event procedures

Control event procedures

Report event procedures

Report section event procedures

Paying Attention to Event Sequence

Looking at common event sequences

Writing simple form and control event procedures

Chapter 26: Debugging Your Access Applications

Organizing VBA Code

Testing Your Applications

Testing functions

Compiling VBA code

Traditional Debugging Techniques

Using MsgBox

Using Debug.Print

Using the Access Debugging Tools

Running code with the Immediate window

Suspending execution with breakpoints

Looking at variables with the Locals window

Setting watches with the Watches window

Using conditional watches

Using the Call Stack window

Trapping Errors in Your Code

Understanding error trapping

The Err object

Including error handling in your procedures

Part VII: Advanced Access Programming Techniques

Chapter 27: Accessing Data with VBA Code

Working with Data

Understanding ADO Objects

The ADO Connection object

The ADO Command object

The ADO Recordset object

Understanding DAO Objects

The DAO DBEngine object

The DAO Workspace object

The DAO Database object

The DAO TableDef object

The DAO QueryDef object

The DAO Recordset object

The DAO Field objects (recordsets)

Writing VBA Code to Update a Table

Updating fields in a record using ADO

Updating a calculated control

Adding a new record

Deleting a record

Deleting related records in multiple tables

Chapter 28: Advanced Data Access with VBA

Adding an Unbound Combo Box to a Form to Find Data

Using the FindRecord method

Using a bookmark

Filtering a Form

With code

With a query

Chapter 29: Integrating Access and SQL Server

Introducing SQL Server Express

Understanding Data Types in SQL Server

Installing SQL Server Express

Installing the database engine

Installing a sample database

Getting to know Management Studio

Using the command line

Connecting to SQL Server

Creating a data source

Linking SQL Server to Access

Using ADO with SQL Server

Working with SQL Server Objects

Using SQL Server tables from Access

Views

Stored procedures

Triggers

Chapter 30: Customizing the Ribbon

The Ribbon Hierarchy

Controls for Access Ribbons

Special Ribbon features

Editing the Default Ribbon

Working with the Quick Access Toolbar

Developing Custom Ribbons

The Ribbon creation process

Using VBA callbacks

Creating a Custom Ribbon

Step 1: Design the Ribbon and build the XML

Step 2: Write the callback routines

Step 3: Create the USysRibbons table

Step 4: Add XML to USysRibbons

Step 5: Specify the custom Ribbon property

The Basic Ribbon XML

Adding Ribbon Controls

Specifying imageMso

The Label control

The Button control

Separators

Check boxes

The DropDown control

The SplitButton Control

Attaching Ribbons to Forms and Reports

Completely Removing the Ribbon

Chapter 31: Preparing Your Access Application for Distribution

Defining the Current Database Options

Application options

Navigation options

Ribbon and toolbar options

Name AutoCorrect Options

Developing the Application

Building to a specification

Creating documentation

Testing the application before distribution

Polishing Your Application

Giving your application a consistent look and feel

Adding common professional components

Making the application easy to start

Bulletproofing an Application

Using error trapping on all Visual Basic procedures

Separating tables from the rest of the application

Building bulletproof forms

Validating user input

Using the /runtime option

Encrypting or encoding a database

Protecting Visual Basic code

Securing the Environment

Setting startup options in code

Disabling startup bypass

Setting property values

Getting property values

Part VIII: Access and Windows SharePoint Services

Chapter 32: Understanding Windows SharePoint Services

Introducing SharePoint

Reviewing Various Types of SharePoint Sites

Looking at a SharePoint Website

Understanding SharePoint documents

Understanding SharePoint lists

The Concept Behind Access and SharePoint Integration

Chapter 33: Integrating Access with SharePoint

Sharing Access Data with SharePoint

Linking to SharePoint lists

Importing SharePoint lists

Exporting Access tables to SharePoint

Moving Access tables to SharePoint

Using SharePoint Templates

Chapter 34: Understanding Access Services

Explaining Managed Applications

Looking at Web Publishing in Access

Why SharePoint?

Leveraging SharePoint features

Understanding Access Services

Examining Access Web Application Limits

Limitations of Access Services

Transactional limitations

Chapter 35: Deploying Access Applications to SharePoint

Publishing a Custom Access Application to SharePoint

Preparing the Access data model

Initializing and configuring the custom web application

Launching and managing your web application

Chapter 36: Using Access Data Macros

Introducing Data Macros

Understanding Table Events

“Before” events

“After” events

Using the Macro Designer for Data Macros

Understanding the Action Catalog

Program flow

Data blocks

Data actions

Creating Your First Data Macro

Managing Macro Objects

Collapsing and expanding macro items

Moving macro items

Saving a macro as XML

Recognizing the Limitations of Data Macros

Part IX: Appendixes

Appendix A: Access 2013 Specifications

Appendix B: Optimizing Access Applications

Organizing your modules

Pruning the call tree

Putting your application's code into a compiled state

Losing the compiled state

Tuning your system

Getting the most from your tables

Getting the most from your queries

Getting the most from your forms and reports

Getting the most from your modules

Increasing network performance

Understanding how databases grow in size

Recognizing that compiling and compacting may not be enough

Making small changes to large databases

Appendix C: Understanding Query Performance and Database Corruption

Normalizing your database design

Using indexes on appropriate fields

Optimizing by improving query design

Compacting and repairing your database regularly

Identifying a corrupted database

Recovering a corrupted database

Preventing database corruption

Appendix D: Function Reference for Data Analysts

End User License Agreement

Access® 2013 Bible

Published byJohn Wiley & Sons, Inc.10475 Crosspoint BoulevardIndianapolis, IN 46256www.wiley.com

Copyright © 2013 by John Wiley & Sons, Inc., Indianapolis, Indiana

Published simultaneously in Canada

ISBN 978-1-118-49035-8 (pbk); ISBN 978-1-118-49034-1 (ebk); ISBN 978-1-118-49155-3 (ebk); ISBN 978-1-118-49154-6 (ebk)

Manufactured in the United States of America

10 9 8 7 6 5 4 3 2 1

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 either the prior written permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA 01923, [phone], fax [phone]. 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 [phone], or online at http://www.wiley.com/go/permissions.

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 or to obtain technical support, please contact our Customer Care Department within the U.S. at [phone], outside the U.S. at [phone] or fax [phone].

Library of Congress Control Number: 2013933952

Trademarks: Wiley and the Wiley logo are trademarks or registered trademarks of John Wiley & Sons, Inc., in the United States and other countries, and may not be used without written permission. Microsoft and Access are 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.

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.

Credits

Sr. Acquisitions Editor

Katie Mohr

Project Editor

Elizabeth Kuball

Technical Editor

Doug Steele

Copy Editor

Elizabeth Kuball

Editorial Manager

Jodi Jensen

Editorial Director

Mary Corder

Vice President and Executive Group Publisher

Richard Swadley

Vice President and Publisher

Andy Cummings

Senior Project Coordinator

Kristie Rees

Graphics and Production Specialists

Andrea HornbergerJennifer Mayberry

Quality Control Technician

Lindsay Amones

Proofreading and Indexing

Evelyn WellbornBIM Indexing & Proofreading Services

Vertical Websites Project Manager and Producer

Rich Graves

About the Authors

Michael Alexander is a Microsoft Certified Application Developer (MCAD) and author of several books on advanced business analysis with Microsoft Access and Microsoft Excel. He has more than 15 years of experience consulting and developing Microsoft Office solutions. Mike has been named a Microsoft MVP for his ongoing contributions to the Excel community. In his spare time, he runs a free tutorial site, www.datapigtechnologies.com, where he shares Excel and Access tips.

Dick Kusleika has been awarded as a Microsoft MVP for 12 consecutive years and has been working with Microsoft Office for more than 20. Dick develops Access- and Excel-based solutions for his clients and has conducted training seminars on Office products in the United States and Australia. Dick also writes a popular Excel-related blog at www.dailydoseofexcel.com.

To the memory of Mike Groh, the author of this book's previous two editions.

Our deepest thanks to the professionals at John Wiley & Sons for all the hours of work put into bringing this book to life. Thanks also to Doug Steele for suggesting numerous improvements to the examples and text in this book. Finally, a special thank you goes out to our families for putting up with all the time spent locked away on this project.

Introduction

Welcome to Access 2013 Bible, your personal guide to the most powerful desktop database management system available today.

If you've picked up this book, you've probably already recognized that Microsoft Access can help you manage your data in ways that no other application can. Even the king of applications, Microsoft Excel, can't do what Access can. Now, it may seem silly to compare Access (a database management application) with Excel (a spreadsheet application), but there is no doubt that Excel is being used every day to manage and analyze large amounts of data in all kinds of organizations. Indeed, you may be opening this book because you need to get past the limitations of Excel.

Access is an excellent (many would say logical) next step for the analyst who faces an ever-increasing data pool. Access takes very few performance hits with larger datasets. It has no predetermined row limitations. And it can effectively manage the relationships between disparate data tables. In addition, Access comes with tools that help you build your own distributable applications.

Today, when we have more data than ever and more demands for complex data analysis, power analysts need to add some tools to their repertoire in order to get away from being simply “spreadsheet mechanics.” That's why this book is such an important step in the evolution of your skillset. Throughout this book, not only will you get an introduction to Access, but you'll also learn various ways in which you can use Access to improve your daily data management and analysis.

Is This Book for You?

This book contains everything you need in order to learn Access 2013 to a mid-advanced level. The book starts off with database basics and builds, chapter by chapter.

This book is designed to enhance the skillset of users at all levels (beginning, intermediate, and even advanced users of Access). Start at the beginning if you're new to Access. If you're already familiar with Access and comfortable building Access applications, you may want to start with the later parts of this book.

If you're new to the world of database management, this book has everything you need to get started with Access 2013. It also offers advanced topics for reference and learning. Beginning developers should pay particular attention to Part I, where we cover the essential skills necessary for building successful and efficient databases. Your ability as a database designer is constantly judged by how well the applications you build perform and how well they handle data entrusted to them by their users.

If you've been working an earlier version of Access, this book is for you. Although you may already be familiar with the workings of Access, every new version has changes not only in features, but also in the mechanics of how certain actions are performed. This book will help you navigate through all these changes.

If you want to learn the basics of Visual Basic for Applications (VBA) programming, you'll find what you need in this book. Although the topic of VBA is a rich one that deserves its own book, this book offers a robust set of chapters that will get you started leveraging VBA to enhance your Access databases. Part VI of this book explains the nuts and bolts — with a lot of gritty technical details — of writing VBA procedures and building Access applications around the code you add to your databases.

Conventions Used in This Book

We use the following conventions in this book:

• When you're instructed to press a key combination (press and hold down one key while pressing another key), the key combination is separated by a plus sign. For example, Ctrl+Esc indicates that you must hold down the Ctrl key and press the Esc key; then release both keys.

• Point the mouse refers to moving the mouse so that the mouse pointer is on a specific item. Click refers to pressing the left mouse button once and releasing it. Double-click refers to pressing the left mouse button twice in rapid succession and then releasing it. Right-click refers to pressing the right mouse button once and releasing it. Drag refers to pressing and holding down the left mouse button while moving the mouse.

• We use italics for new terms and for emphasis.

• We use bold for material that you need to type directly into the computer.

• We use monofont for code and for on-screen messages.

How This Book Is Organized

This book is divided into nine parts:

• Part I: Access Building Blocks: Part I provides a solid understanding of the basic elements of databases, introduces you to the keywords of database management, and teaches you how to plan tables and work with Access data types. In this part, you'll also get your first look into Access and the Access interface.

• Part II: Understanding Access Tables: In Part II, you get the skills you need to build Access tables, manage relationships between tables, and link to disparate data sources like Excel files, text files, SQL Server, and other Access databases.

• Part III: Working with Access Queries: Part III introduces you to some of the basic analytical tools available in Access. Here, you'll explore the Query Builder, as well as techniques to create both simple and advanced analytical outputs from your Access tables. We cover query basics, aggregate queries, action queries, and crosstab queries.

• Part IV: Analyzing Data in Access: Part IV demonstrates many of the advanced techniques that truly bring data analysis to the next level. Here, you'll explore how to transform your data via queries, create custom calculations, perform conditional analysis, build powerful subqueries, and apply statistical analysis to your queries.

• Part V: Working with Access Forms and Reports: Part V focuses on building forms and reports using Access. Here, we cover the basics of turning data into slick-looking user interfaces and PDF-style Access reports. You'll also explore how to enhance the look and feel of your Access applications via advanced form controls.

• Part VI: Access Programming Fundamentals: In Part VI, you'll take the next step and dive into programming. The chapters in this part start you with Access macros, take you into VBA fundamentals, and eventually work up to leveraging VBA to improve your Access database. This part helps you understand the complex object and event models that drive Access applications and how to construct the VBA code necessary to take advantage of this rich programming environment.

• Part VII: Advanced Access Programming Techniques: Part VII turns your attention to automation and integration, showing you how your reporting mechanisms can be enhanced by leveraging other programs and platforms. In these chapters, you'll not only learn the fundamental skills required to become more proficient in VBA, but you'll also discover many insider tricks to apply to your Access application development projects. You'll also explore advanced techniques, such as customizing the Access 2013 Ribbon.

• Part VIII: Access and Windows SharePoint Services: In Part VIII, we cover the topic of Microsoft Windows SharePoint Services. Here, you'll discover the extended Microsoft SharePoint integration capabilities in Access that allow you to publish Access tables, forms, and reports on SharePoint sites. Although somewhat limited when compared with strictly Access applications, publishing Access objects to the SharePoint platform provides a powerful way of sharing Access data with remote users.

• Part IX: Appendixes: Part IX includes useful reference materials that will assist you in your everyday dealings with Access. Appendix A documents the Access 2013 specifications, including maximum and minimum sizes of databases and many of the controls in Access. Appendix B offers ideas on how to improve speed and performance of your Access applications. Appendix C shows you how to avoid and handle corruption issues when working with Access databases. Appendix D details many of the built-in Access functions that are available to data analysts.

How to Use This Book

Although each chapter is an integral part of the book as a whole, each chapter can also stand on its own and has its own example files, available on the book's website. You can read the book in any order you want, skipping from chapter to chapter and from topic to topic. This book's index is particularly thorough; you can refer to the index to find the location of a particular topic you're interested in.

What's on the Website

The examples demonstrated throughout this book can be found on this book's website. The URL is www.wiley.com/go/access2013bible.

Getting Additional Help with Access

As you experiment with the new functions and tools you learn here in this book, you may sometimes need an extra push in the right direction. The first place you should look is Access's Help system. The Help system in Access isn't perfect. To a new user, the Help system may seem like a clunky add-in that returns a perplexing list of topics that has nothing to do with the original topic being searched. The truth is, however, once you learn how to use the Access Help system effectively, it's often the fastest and easiest way to get extra help on a topic.

Following are some tips that will help you get the most out of Access's Help system:

• Location matters when asking for help. You may remember the Help system in older versions of Access being a lot more user-friendly and more effective than newer versions of Access. Well, rest assured that you aren't just imagining it. The fact is, Microsoft fundamentally changed the mechanics of the Access Help system.

In Access 2013, there are actually two Help systems: one providing help on Access features and another providing help on VBA programming topics. Instead of doing a global search with your criteria, Access throws your search criteria only against the Help system that is relevant to your current location. This essentially means that the help you get is determined by the area of Access in which you're working. So, if you need help on a topic that involves VBA programming, you'll need to be in the VBA Editor while performing your search. On the other hand, if you need help on building a query, it's best to be in the Query Design view. This will ensure that your keyword search is performed on the correct Help system.

• Online help is better than offline help. When you search for help on a topic, Access checks to see if you're connected to the Internet. If you are, Access returns help results based on online content from Microsoft's website. If you aren't, Access uses the Help files that are locally stored with Microsoft Office. One way to maximize the help you get in Access is to use the online help. Online help is generally better than offline help because the content you find with online help is often more detailed and includes updated information, as well as links to other resources not available offline.

• Diversify your knowledge base with online resources. Familiarize yourself with a handful of websites and forums dedicated to Access. These resources can serve as supplemental help, not only for basic Access topics, but also to give you situation-specific tips and tricks. The following list of sites should get you started.

• www.allenbrowne.com

• www.microsoft.com/office/community/en-us/default.mspx

• www.mvps.org/access

• www.utteraccess.com

These sites are free to use and are particularly helpful when you need an extra push in the right direction.

Part I: Access Building Blocks

In This Part

Chapter 1: An Introduction to Database Development

Chapter 2: Getting Started with Access

Each part of this book builds on previous parts, and the chapters in each part contain examples that draw on techniques explained in previous parts and chapters. As a developer, your applications will benefit from the skills you acquire by reading the chapters and practicing the examples contained in this book.

But everyone has to start somewhere when approaching a new discipline, and Part I of this book presents the essential skills necessary for anyone to succeed at database development with Access. The topics covered in this part explain the concepts and techniques that are necessary to successfully use database environments and give you the skills necessary to normalize data and plan and implement effective tables.

If you're already familiar with the concepts involved in database design, you may want to skim these chapters. If you're new to the world of databases, spend some time here gaining a thorough understanding of these important topics.

Chapter 1: An Introduction to Database Development

IN THIS CHAPTER

Examining the differences between databases, tables, records, fields, and values

Discovering why multiple tables are used in a database

Exploring Access database objects

Designing a database system

Database development is unlike most other ways you work with computers. Unlike Microsoft Word or Excel, where the approach to working with the application is relatively intuitive, good database development requires prior knowledge. You have to learn a handful of fundamentals, including database terminology, basic database concepts, and database best practices.

Throughout this chapter, we cover the fundamentals of database development.

 If your goal is to get right into Access, you might want to skip to Chapter 2.

The Database Terminology of Access

Access follows most, but not all, traditional database terminology. The terms database, table, record, field, and value indicate a hierarchy from largest to smallest. These same terms are used with virtually all database systems.

Databases

Generally, the word database is a computer term for a collection of information concerning a certain topic or business application. Databases help you organize this related information in a logical fashion for easy access and retrieval.

Note

Some older database systems used the term database to describe individual tables; current use of database applies to all elements of a database system.

Databases aren't only for computers. There are also manual databases; we sometimes refer to these as manual filing systems or manual database systems. These filing systems usually consist of people, papers, folders, and filing cabinets — paper is the key to a manual database system. In manual database systems, you typically have in and out baskets and some type of formal filing method. You access information manually by opening a file cabinet, taking out a file folder, and finding the correct piece of paper. Users fill out paper forms for input, perhaps by using a keyboard to input information that's printed on forms. You find information by manually sorting the papers or by copying information from many papers to another piece of paper (or even into an Excel spreadsheet). You may use a spreadsheet or calculator to analyze the data or display it in new and interesting ways.

An Access database is nothing more than an automated version of the filing and retrieval functions of a paper filing system. Access databases store information in a carefully defined structure. Access tables store a variety of different kinds of data, from simple lines of text (such as name and address) to complex data such as pictures, sounds, or video images. Storing data in a precise format enables a database management system (DBMS) like Access to turn data into useful information.

Tables serve as the primary data repository in an Access database. Queries, forms, and reports provide access to the data, enabling a user to add or extract data, and presenting the data in useful ways. Most developers add macros or Visual Basic for Applications (VBA) code to forms and reports to make their Access applications easier to use.

A relational database management system (RDBMS), such as Access, stores data in related tables. For example, a table containing employee data (names and addresses) may be related to a table containing payroll information (pay date, pay amount, and check number).

Queries allow the user to ask complex questions (such as “What is the sum of all paychecks issued to Jane Doe in 2012?”) from these related tables, with the answers displayed as onscreen forms and printed reports.

In fact, one of the fundamental differences between a relational database and a manual filing system is that, in a relational database system, data for a single person or item may be stored in separate tables. For example, in a patient management system, the patient's name, address, and other contact information is likely to be stored in a different table from the table holding patient treatments. In fact, the treatment table holds all treatment information for all patients, and a patient identifier (usually a number) is used to look up an individual patient's treatments in the treatment table.

In Access, a database is the overall container for the data and associated objects. It's more than the collection of tables, however — a database includes many types of objects, including queries, forms, reports, macros, and code modules.

As you open an Access database, the objects (tables, queries, and so on) in the database are presented for you to work with. You may open several copies of Access at the same time and simultaneously work with more than one database, if needed.

Many Access databases contain hundreds, or even thousands, of tables, forms, queries, reports, macros, and modules. With a few exceptions, all the objects in an Access database reside within a single file with an extension of ACCDB, ACCDE, MDB, MDE, or ADP.

Tables

A table is just a container for raw information (called data), similar to a folder in a manual filing system. Each table in an Access database contains information about a single entity, such as a person or product, and the data in the table is organized into rows and columns.

 In Chapters 3 and 4, you learn the very important rules governing relational table design and how to incorporate those rules into your Access databases. These rules and guidelines ensure that your applications perform well while protecting the integrity of the data contained within your tables.

In Access a table is an entity. As you design and build Access databases, or even when working with an existing application, you must think of how the tables and other database objects represent the physical entities managed by your database and how the entities relate to one another.

After you create a table, you can view the table in a spreadsheet-like form, called a datasheet, comprising rows and columns (known as records and fields, respectively — see the following section, “Records and fields”). Although a datasheet and a spreadsheet are superficially similar, a datasheet is a very different type of object.

 Chapter 5 discusses Access datasheets and the differences between datasheets and spreadsheets. You can find much more about fields and field properties in Chapter 3.

Records and fields

A datasheet is divided into rows (called records) and columns (called fields), with the first row (the heading on top of each column) containing the names of the fields in the database.

Each row is a single record containing fields that are related to that record. In a manual system, the rows are individual forms (sheets of paper), and the fields are equivalent to the blank areas on a printed form that you fill in.

Each column is a field that includes many properties that specify the type of data contained within the field, and how Access should handle the field's data. These properties include the name of the field (Company) and the type of data in the field (Text). A field may include other properties as well. For example, the Address field's Size property tells Access the maximum number of characters allowed for the address.

Note

When working with Access, the term field is used to refer to an attribute stored in a record. In many other database systems, including Microsoft SQL Server, column is the expression you'll hear most often in place of field.Field and column mean the same thing. The terminology used relies somewhat on the context of the database system underlying the table containing the record.

Values

At the intersection of a record and a field is a value — the actual data element. For example, if you have a field called Company, a company name entered into that field would represent one data value. Certain rules govern how data is contained in an Access table.

 See Chapters 3 and 4 for more on these rules.

Relational Databases

Access is a relational database management system. Access data is stored in related tables, where data in one table (such as customers) is related to data in another table (such as orders). Access maintains the relationships between related tables, making it easy to extract a customer and all the customer's orders, without losing any data or pulling order records not owned by the customer.

Multiple tables simplify data entry and reporting by decreasing the input of redundant data. By defining two tables for an application that uses customer information, for example, you don't need to store the customer's name and address every time the customer purchases an item.

After you've created the tables, they need to be related to each other. For example, if you have a Customer table and a Sales table, you can relate the two tables using a common field between them. In this case, Customer Number would be a good field to have in both tables. This will allow you to see sales in the Sales table where the Customer Number matches the Customer table.

The benefit of this model is that you don't have to repeat key attributes about a customer (like customer name, address, city, state, zip) each time you add a new record to the Sales table. All you need is the customer number. When a customer changes address, for example, the address changes only in one record in the Customers table.

Why Create Multiple Tables?

The prospect of creating multiple tables almost always intimidates beginning database users. Most often, beginners want to create one huge table that contains all the information they need — for example, a Customer table with all the sales placed by the customer and the customer's name, address, and other information. After all, if you've been using Excel to store data so far, it may seem quite reasonable to take the same approach when building tables in Access.

A single large table for all customer information quickly becomes difficult to maintain. You have to input the customer information for every sale a customer makes (repeating the name and address information over and over in every row). The same is true for the items purchased for each sale when the customer has purchased multiple items as part of a single purchase. This makes the system more inefficient and prone to data-entry mistakes. The information in the table is inefficiently stored — certain fields may not be needed for each sales record, and the table ends up with a lot of empty fields.

You want to create tables that hold a minimum of information while still making the system easy to use and flexible enough to grow. To accomplish this, you need to consider making more than one table, with each table containing fields that are related only to the focus of that table. Then, after you create the tables, you link them so that you're able to glean useful information from them. Although this process sounds extremely complex, the actual implementation is relatively easy.

Separating data into multiple tables within a database makes a system easier to maintain because all records of a given type are within the same table. By taking the time to properly segment data into multiple tables, you experience a significant reduction in design and work time. This process is known as normalization.

 You can read about normalization in Chapter 4.

Access Database Objects

If you're new to databases (or even if you're an experienced database user), you need to understand a few key concepts before starting to build Access databases. The Access database contains six types of top-level objects, which consist of the data and tools that you need to use Access:

• Table: Holds the actual data.

• Query: Searches for, sorts, and retrieves specific data.

• Form: Lets you enter and display data in a customized format.

• Report: Displays and prints formatted data.

• Macro: Automates tasks without programming.

• Module: Containsprogramming statements written in the VBA programming language.

Datasheets

Datasheets are one of the many ways by which you can view data in Access. Although not a permanent database object, a datasheet displays a table's content in a row-and-column format similar to an Excel worksheet. A datasheet displays a table's information in a raw form, without transformations or filtering. The Datasheet view is the default mode for displaying all fields for all records.

You can scroll through the datasheet using the directional keys on your keyboard. You can also display related records in other tables while in a datasheet. In addition, you can make changes to the displayed data.

Queries

Queries extract information from a database. A query selects and defines a group of records that fulfill a certain condition. Most forms and reports are based on queries that combine, filter, or sort data before it's displayed. Queries are often called from macros or VBA procedures to change, add, or delete database records.

An example of a query is when a person at the sales office tells the database, “Show me all customers, in alphabetical order by name, who are located in Massachusetts and bought something over the past six months” or “Show me all customers who bought Chevrolet car models within the past six months and display them sorted by customer name and then by sale date.”

Instead of asking the question in plain English, a person uses the query by example (QBE) method. When you enter instructions into the Query Designer window and run the query, the query translates the instructions into Structured Query Language (SQL) and retrieves the desired data.

 Chapter 8 discusses the Query Designer window and building queries.

Data-entry and display forms

Data-entry forms help users get information into a database table quickly, easily, and accurately. Data-entry and display forms provide a more structured view of the data than what a datasheet provides. From this structured view, database records can be viewed, added, changed, or deleted. Entering data through the data-entry forms is the most common way to get the data into the database table.

Data-entry forms restrict access to certain fields within the table. Forms can also be enhanced with data validation rules or VBA code to check the validity of your data before it's added to the database table.

Most users prefer to enter information into data-entry forms rather than into Datasheet views of tables. Forms often resemble familiar paper documents and can aid the user with data-entry tasks. Forms make data entry easy to understand by guiding the user through the fields of the table being updated.

Read-only forms are often used for inquiry purposes. These forms display certain fields within a table. Displaying some fields and not others means that you can limit a user's access to sensitive data while allowing access to other fields within the same table.

Reports

Reports present your data in printed format. Access allows for an extraordinary amount of flexibility when creating reports. For instance, you can configure a report to list all records in a given table (such as a Customers table) or you can have the report contain only the records meeting certain criteria (such as all customers living in Arizona). You do this by basing the report on a query that selects only the records needed by the report.

Reports often combine multiple tables to present complex relationships among different sets of data. An example is printing an invoice. The customers table provides the customer's name and address (and other relevant data) and related records in the sales table to print the individual line-item information for each product ordered. The report also calculates the sales totals and prints them in a specific format. Additionally, you can have Access output records into an invoice report, a printed document that summarizes the invoice.

Tip

When you design your database tables, keep in mind all the types of information that you want to print. Doing so ensures that the information you require in your various reports is available from within your database tables.

Database objects

To create database objects, such as tables, forms, and reports, you first complete a series of design tasks. The better your design is, the better your application will be. The more you think through your design, the faster and more successfully you can complete any system. The design process is not some necessary evil, nor is its intent to produce voluminous amounts of documentation. The sole intent of designing an object is to produce a clear-cut path to follow as you implement it.

A Five-Step Design Method

The five design steps described in this section provide a solid foundation for creating database applications — including tables, queries, forms, reports, macros, and simple VBA modules.

The time you spend on each step depends entirely on the circumstances of the database you're building. For example, sometimes users give you an example of a report they want printed from their Access database, and the sources of data on the report are so obvious that designing the report takes a few minutes. Other times, particularly when the users' requirements are complex, or the business processes supported by the application require a great deal of research, you may spend many days on Step 1.

As you read through each step of the design process, always look at the design in terms of outputs and inputs.

Step 1: The overall design — from concept to reality

All software developers face similar problems, the first of which is determining how to meet the needs of the end-user. It's important to understand the overall user requirements before zeroing in on the details.

For example your users may ask for a database that supports the following tasks:

• Entering and maintaining customer information (name, address, and financial history)

• Entering and maintaining sales information (sales date, payment method, total amount, customer identity, and other fields)

• Entering and maintaining sales line-item information (details of items purchased)

• Viewing information from all the tables (sales, customers, sales line items, and payments)

• Asking all types of questions about the information in the database

• Producing a monthly invoice report

• Producing a customer sales history

• Producing mailing labels and mail-merge reports

When reviewing these eight tasks, you may need to consider other peripheral tasks that weren't mentioned by the user. Before you jump into designing, sit down and learn how the existing process works. To accomplish this, you must do a thorough needs analysis of the existing system and how you might automate it.

Prepare a series of questions that give insight to the client's business and how the client uses his data. For example, when considering automating any type of business, you might ask these questions:

• What reports and forms are currently used?

• How are sales, customers, and other records currently stored?

• How are billings processed?

As you ask these questions and others, the client will probably remember other things about the business that you should know.

A walkthrough of the existing process is also helpful to get a feel for the business. You may have to go back several times to observe the existing process and how the employees work.

As you prepare to complete the remaining steps, keep the client involved — let the users know what you're doing and ask for input on what to accomplish, making sure it's within the scope of the user's needs.

Step 2: Report design

Although it may seem odd to start with reports, in many cases, users are more interested in the printed output from a database than they are in any other aspect of the application. Reports often include every bit of data managed by an application. Because reports tend to be comprehensive, they're often the best way to gather important information about a database's requirements.

When you see the reports that you'll create in this section, you may wonder, “Which comes first — the chicken or the egg?” Does the report layout come first, or do you first determine the data items and text that make up the report? Actually, these items are considered at the same time.

It isn't important how you lay out the data in a report. The more time you take now, however, the easier it will be to construct the report. Some people go so far as to place gridlines on the report to identify exactly where they want each bit of data to be.

Step 3: Data design

The next step in the design phase is to take an inventory of all the information needed by the reports. One of the best methods is to list the data items in each report. As you do so, take careful note of items that are included in more than one report. Make sure that you keep the same name for a data item that is in more than one report because the data item is really the same item.

For example, you can start with all the customer data you'll need for each report, as shown in Table 1.1.

TABLE 1.1 Customer-Related Data Items Found in the Reports

Customers Report

Invoice Report

Customer Name

Customer Name

Street

Street

City

City

State

State

ZIP Code

ZIP Code

Phone Numbers

Phone Numbers

E-Mail Address

Web Address

Discount Rate

Customer Since

Last Sales Date

Sales Tax Rate

Credit Information (four fields)

As you can see by comparing the type of customer information needed for each report, there are many common fields. Most of the customer data fields are found in both reports. Table 1.1 shows only some of the fields that are used in each report — those related to customer information. Because the related row and field names are the same, you can easily make sure that you have all the data items. Although locating items easily isn't critical for this small database, it becomes very important when you have to deal with large tables containing many fields.

After extracting the customer data, you can move on to the sales data. In this case, you need to analyze only the Invoice report for data items that are specific to the sales. Table 1.2 lists the fields in the report that contain information about sales.

TABLE 1.2 Sales Data Items Found in the Reports

Invoice Report

Line Item Data

Invoice Number

Product Purchased

Sales Date

Quantity Purchased

Invoice Date

Description of Item Purchased

Payment Method

Price of Item

Salesperson

Discount for Each Item

Discount (overall for sale)

Tax Location

Tax Rate

Product Purchased (multiple lines)

Quantity Purchased (multiple lines)

Description of Item Purchased (multiple lines)

Price of Item (multiple lines)

Discount for each item (multiple lines)

Payment Type (multiple lines)

Payment Date (multiple lines)

Payment Amount (multiple lines)

Credit Card Number (multiple lines)

Expiration Date (multiple lines)

As you can see when you examine the type of sales information needed for the report, a few items (fields) are repeating (for example, the Product Purchased, Quantity Purchased, and Price of Item fields). Each invoice can have multiple items, and each of these items needs the same type of information — number ordered and price per item. Many sales have more than one purchased item. Also, each invoice may include partial payments, and it's possible that this payment information will have multiple lines of payment information, so these repeating items can be put into their own grouping.

You can take all the individual items that you found in the sales information group in the preceding section and extract them to their own group for the invoice report. Table 1.2 shows the information related to each line item.

Step 4: Table design

Now for the difficult part: You must determine what fields are needed for the tables that make up the reports. When you examine the multitude of fields and calculations that make up the many documents you have, you begin to see which fields belong to the various tables in the database. (You already did much of the preliminary work by arranging the fields into logical groups.) For now, include every field you extracted. You'll need to add others later (for various reasons), although certain fields won't appear in any table.

It's important to understand that you don't need to add every little bit of data into the database's tables. For example, users may want to add vacation and other out-of-office days to the database to make it easy to know which employees are available on a particular day. However, it's very easy to burden an application's initial design by incorporating too many ideas during the initial development phases. Because Access tables are so easy to modify later on, it's probably best to put aside noncritical items until the initial design is complete. Generally speaking, it's not difficult to accommodate user requests after the database development project is under way.