Like Salesforce, MS Access uses a relational database. This means each Access table can represent a single SF object. And once in Access, you have multiple tools at your disposal for data transformation.
The first step is to export data from SF and import it into Access. Then you can build queries using a visual drag-and-drop interface. This is significantly easier than using vlookups in Excel. It also allows you to build very complex queries with ease.
Here’s a single example.
Let’s say multiple users left your organization, and you’ve set their user records to inactive.
Now, these users were the owners of multiple contact records. So you want to find all those records and change the owner to another active user.
Once the Contact and User objects are imported into Access,
- Create a query, and drag the Contact and the User tables into the canvas
- Link the Contact table to the User table by clicking on the Contact.OwnerId field and dragging your mouse to the User.Id field. This line represents a join relationship
- Drag three fields from the tables to the lower section: Contact.Id, Contact.OwnerId, and User.IsActive
- For User.IsActive, set the criteria to “False”
- For Contact.OwnerId, set it to the Id of the user who will be the new owner
The results looks like this:
From there you export the data from Access and update SF using your favorite data loader.
The takeaway
MS Access is not as popular as it once was. But it’s extremely useful and powerful for Salesforce admins.
Perhaps it’s time to consider learning some basic steps to help with your next data transformation.