O'Reilly logo

Access 2013 Bible by Dick Kusleika, Michael Alexander

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Chapter 11: Transforming Data in Access

IN THIS CHAPTER

Finding and removing duplicate records

Filling in blank fields

Concatenating

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required