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