Start Access 2016 updating between two tables

Access 2016 updating between two tables

This e Book requires no passwords or activation to read.

A Microsoft MVP for Excel, his best-sellers include Excel 2016 In Depth.

Michael Alexander, Microsoft Certified Application Developer (MCAD) and Microsoft MVP, is author of several books on advanced business analysis with Excel and Access.

Use Excel 2016 pivot tables and pivot charts to produce powerful, dynamic reports in minutes instead of hours…

understand exactly what’s going on in your business… Even if you’ve never created a pivot table before, this book will help you leverage all their amazing flexibility and analytical power.

A Web Edition is an online learning product that you view in your web browser while connected to the Internet.

Some Web Editions include additional interactive features, such as videos, quizzes, or live code editing. A Web Edition will be accessible from your Account page after purchase.

9Defining a Pivot Table .................................................................9Why You Should Use a Pivot Table .............................................10Advantages of Using a Pivot Table .........................................11When to Use a Pivot Table ......................................................12Anatomy of a Pivot Table .........................................................12Filters Area ........................................................................14Pivot Tables Behind the Scenes ..........................................14Pivot Table Backward Compatibility .................................................15A Word About Compatibility .................................................16Next Steps............................................................................172 Creating a Basic Pivot Table ........................................19Preparing Data for Pivot Table Reporting ..............................................19Creating a Report Filter.......................................................31Understanding the Recommended Pivot Table Feature...........................31Using Slicers ......................................................................................33Dealing with an Expanded Data Source Range Due to the Addition of Rows or Columns ..............39Sharing the Pivot Cache .........................................40Saving Time with New Pivot Table Tools ............................................41Relocating a Pivot Table .................................................43Next Steps.......................................................................433 Customizing a Pivot Table ................................................45Making Common Cosmetic Changes .................................................46Tracking Relative Importance with the Index Option ...................................74Next Steps...............................................................754 Grouping, Sorting, and Filtering Pivot Data ..........................77Automatically Grouping Dates ...................................................77Using a Custom List for Sorting ..................................................93Filtering a Pivot Table: An Overview ...................................................95Using Filters for Row and Column Fields .........................................96Driving Multiple Pivot Tables from One Set of Slicers .......................................110Next Steps......................................................................................1125 Performing Calculations in Pivot Tables ...........................113Introducing Calculated Fields and Calculated Items ............................................113Method 3: Insert a Calculated Field Directly into a Pivot Table ............................116Creating a Calculated Field ...................................116Creating a Calculated Item ...........................................124Understanding the Rules and Shortcomings of Pivot Table Calculations ..................127Documenting Formulas ........................................................133Next Steps............................................................................1346 Using Pivot Charts and Other Visualizations .........................135What Is a Pivot Chart... ..........................................................135Creating a Pivot Chart ..........................................................136Preprogrammed Scenarios for Condition Levels ........................................151Creating Custom Conditional Formatting Rules .............................................152Next Steps...................................................................................................1567 Analyzing Disparate Data Sources with Pivot Tables ................................157Using the Internal Data Model ..................................................158Limitations of the Internal Data Model ...............................................................167Building a Pivot Table Using External Data Sources .............................................168Building a Pivot Table with SQL Server Data ......................................................171Leveraging Power Query to Extract and Transform Data .............................................174Power Query Connection Types ......................................................188Next Steps..........................................................................................1928 Sharing Pivot Tables with Others ........................................193Designing a Workbook as an Interactive Web Page .......................................................193Sharing a Link to a Web Workbook ................................................................196Sharing with Power BI ...............................................................................196Sharing Your Dashboard ..............................................................202Next Steps........................................................................2029 Working with and Analyzing OLAP Data ..........................203Introduction to OLAP .............................................................................203Connecting to an OLAP Cube .....................................................204Understanding the Structure of an OLAP Cube ...................................................207Understanding the Limitations of OLAP Pivot Tables .........................................208Creating an Offline Cube ...................................................209Breaking Out of the Pivot Table Mold with Cube Functions ................................211Performing What-If Analysis with OLAP Data .....................................220Next Steps...............................................................................22210 Mashing Up Data with Power Pivot ...................................223Understanding the Benefits and Drawbacks of Power Pivot and the Data Model ............223Building a Pivot Table ....................................................................237Understanding Differences Between Power Pivot and Regular Pivot Tables ............................238Using DAX Calculations .............................................................239Using Time Intelligence .............................................................242Next Steps.....................................................................................24311 Dashboarding with Power View and 3D Map ..........................245Preparing Data for Power View ....................................................245Creating a Power View Dashboard .............................................................247Using Tile Boxes to Filter a Chart or a Group of Charts .....................................255Replicating Charts Using Multiples .................................................256Showing Data on a Map......................................................257Using Images ..........................................................................258Changing a Calculation ..........................................................................259Animating a Scatter Chart over Time .............................................259Some Closing Tips on Power View ......................................................261Analyzing Geographic Data with 3D Map ................................................261Creating a Video from 3D Map...................................................271Next Steps.........................................................................27412 Enhancing Pivot Table Reports with Macros ................275Why Use Macros with Pivot Table Reports ................................275Recording a Macro ..................................................................276Creating a User Interface with Form Controls .................................278Altering a Recorded Macro to Add Functionality.................................280Inserting a Scrollbar Form Control ............................................281Next Steps......................................................28813 Using VBA to Create Pivot Tables.................................289Enabling VBA in Your Copy of Excel .....................................289Using a File Format That Enables Macros ..................................290Visual Basic Editor .................................................................291Visual Basic Tools .........................................................291The Macro Recorder ..................................................................292Understanding Object-Oriented Code ................................................292Learning Tricks of the Trade ........................................................293Using With and End With to Shorten Code ................................................295Understanding Versions .....................................................................295Building a Pivot Table in Excel VBA ......................................296Putting It All Together ......................................................349Next Steps....................................................................35114 Advanced Pivot Table Tips and Techniques .....................353Tip 1: Force Pivot Tables to Refresh Automatically........................................353Tip 2: Refresh All Pivot Tables in a Workbook at the Same Time ...................................354Tip 3: Sort Data Items in a Unique Order, Not Ascending or Descending ....................355Tip 4: Turn Pivot Tables into Hard Data .............................................355Tip 5: Fill the Empty Cells Left by Row Fields .........................................356Option 2: Use Excel’s Go To Special Functionality ..............................357Tip 6: Add a Rank Number Field to a Pivot Table ...........................359Tip 7: Reduce the Size of Pivot Table Reports .........................360Delete the Source Data Worksheet ......................................360Tip 8: Create an Automatically Expanding Data Range .................................361Tip 9: Compare Tables Using a Pivot Table ..........................361Tip 10: Auto Filter a Pivot Table .............................................363Tip 11: Force Two Number Formats in a Pivot Table ..................................364Tip 12: Create a Frequency Distribution with a Pivot Table ..................................366Tip 13: Use a Pivot Table to Explode a Data Set to Different Tabs ....................

367Tip 14: Apply Restrictions on Pivot Tables and Pivot Fields .........................................368Pivot Field Restrictions ...................................................370Tip 15: Use a Pivot Table to Explode a Data Set to Different Workbooks .....................372Next Steps..................................................................37315 Dr. Get Pivot Data ...................................................375Avoiding the Evil Get Pivot Data Problem ..........................................................376Updating the Report in Future Months ..................................390Conclusion .................................................................391Index .......................................393  This book is part of Que's Content Update Program.

Viewing a Web Edition requires a web browser that fully supports HTML5.

Supported platforms are: A Web Edition is an online learning product that you view in your web browser while connected to the Internet.

Supported platforms are: The popular standard, used most often with the free Adobe® Reader® software.