The Excel analyst's guide to Access / Michael Alexander.

Includes index.

"Like many managers, accountants, and analysts, you've been using Excel for data analysis. It's a good tool, but you're beginning to find its limitations frustrating. You're looking for more scalability. More transparency of analytical processes. The ability to separate data from presentation. Access makes it all possible, and this guide helps you add Access to your analytical toolbox. Learn the essentials of using Access, basic and advanced analysis techniques, how to build reports, and much more ... Easily move data between Excel and Access ; Normalize and store Excel data in a structured relational database ; Use Access to analyze large amounts of data ; Build reports and implement dashboard-style visualization ; Report Access data using Excel's presentation layer ; Automate redundant reporting analysis using Excel and Access macros ; Save time and increase productivity by automating redundant processes using VBA ; Get external data using SQL, ADO, and XML." -- Page 4 of cover.

Cover -- Title Page -- Copyright Page -- Table of Contents -- Introduction -- Part I: Fundamentals of Data Analysis in Access -- Chapter 1: The Case for Data Analysis in Access -- Where Data Analysis with Excel Can Go Wrong -- Scalability -- Transparency of Analytical Processes -- Separation of Data and Presentation -- Deciding Whether to Use Access or Excel -- Size of Data -- Data Structure -- Data Evolution -- Functional Complexity -- Shared Processing -- An Excel User's Guide to Access: Don't Panic! -- Tables -- Queries -- Reports -- Macros and VBA -- Summary -- Chapter 2: Access Basics -- Access Tables -- Table Basics -- Opening a Table in the Datasheet View -- Identifying Important Table Elements -- Opening a Table in the Design View -- Exploring Data Types -- Creating a Table from Scratch -- Working with Field Properties -- Setting Primary Keys -- Importing Data into Access -- Importing Data from Text File -- Linking an Excel Worksheet to Access -- Understanding the Relational Database Concept -- Why Is This Concept Important? -- Excel and the Flat-File Format -- Splitting Data into Separate Tables -- Relationship Types -- Query Basics -- What Is a Query? -- Creating Your First Select Query -- Sorting Query Results -- Filtering Query Results -- Querying Multiple Tables -- Refining the Query -- Using Operators in Queries -- The Top Ten Query Errors -- Summary -- Chapter 3: Beyond Select Queries -- Aggregate Queries -- Creating an Aggregate Query -- About Aggregate Functions -- Group By -- Sum, Avg, Count, StDev, Var -- Min, Max, First, Last -- Expression,Where -- Action Queries -- Why Use Action Queries? -- Make-Table Queries -- Why Use a Make-Table Query? -- What Are the Hazards of Make-Table Queries? -- Creating a Make-Table Query -- Delete Queries -- Why Use a Delete Query? -- What Are the Hazards of Delete Queries? -- Creating a Delete Query -- Append Queries -- Why Use an Append Query? -- What Are the Hazards of Append Queries? -- Creating an Append Query -- Update Queries -- Why Use an Update Query? -- What Are the Hazards of Update Queries? -- Creating an Update Query -- AWord on Updatable Datasets -- Crosstab Queries -- Using the Crosstab QueryWizard -- Creating a Crosstab Query Manually -- Customizing Your Crosstab Queries -- Summary -- Part II: Basic Analysis Techniques -- Chapter 4: Transforming Your Data with Access -- Finding and Removing Duplicate Records -- Defining Duplicate Records -- Finding Duplicate Records -- Removing Duplicate Records -- Common Transformation Tasks -- Filling in Blank Fields -- Concatenating -- Concatenating Fields -- Augmenting Field Values with Your Own Text -- Changing Case -- Removing Leading and Trailing Spaces from a String -- Finding and Replacing Specific Text -- Adding Your Own Text in Key PositionsWithin a String -- Parsing Strings Using Character Markers -- Query 1 -- Query 2 -- Summary -- Chapter 5: Working with Calculations and Dates -- Using Calculations in Your Analysis -- Common Calculation Scenarios -- Using Constants in Calculations -- Using Fields in Calculations -- Using the Results of Aggregation in Calculations.

Trade John Wiley & Sons, Incorporated.

eBooks on EBSCOhost EBSCO eBook Subscription Academic Collection - Worldwide