Chapter 11: Transforming Data in Access


Finding and removing duplicate records

Filling in blank fields


Changing case

Removing leading and trailing spaces

Finding and replacing specific text

Padding strings

Parsing strings

Data transformation generally entails certain actions that are meant to “clean” your data — actions such as establishing a table structure, removing duplicates, cleaning text, removing blanks, and standardizing data fields.

You'll often receive data that is unpolished or raw. That is to say, the data may have duplicates, there may be blank fields, there may be inconsistent text, and so on. Before you can perform any kind of meaningful analysis on data in this state, it's important to go through a process of data transformation or data cleanup.

Many people store their data in Access, but few use Access for data transformation purposes, oftentimes preferring to export the data to Excel, perform any necessary cleanup there, and then import the data back to Access. The obvious motive for this behavior is familiarity with the flexible Excel environment. However, exporting and importing data simply to perform such easy tasks can be quite inefficient, especially if you're working with large datasets.

In this chapter, we introduce you to some of the tools and techniques in Access that make it easy for you to clean and massage your data without turning to Excel.

On the Web

The starting database for this walkthrough, Chapter11.accdb, can ...

Get Access 2013 Bible now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.