Transforming data is the process by which data is prepared for use in a database. One of the banes of a database administrator’s existence is the need to “clean” data. Often, new data is available to be imported into a database but it is flawed in ways that make it unusable. One example would be a dataset that includes duplicate data. Other examples of data that requires cleaning or transformation are datasets with blank fields and concatenated strings.

Access provides some automated means of handling data transformation for duplicate records. If a dataset is suspected of containing duplicates, select the “Create” command from the toolbar and click “Query Wizard” and select “Find Duplicates Query Wizard,” identify the dataset in question and follow the instructions. A datasheet view will be created that shows records that appear to be duplicates.

Dealing with impurities in datasets is a job not just for one application like MS Access. IT professionals in all disciplines are confronted with the need for some sort of data “scrubbing” or another, and it is safe to say that there are no bullet-proof, automated means of doing so. Using Access queries, views, and its programming language, does provide a means of identifying and correcting faulty datasets.