We covered the Excel solution to this post’s scenario in the post Cleaning data in Excel. Using exactly the same logic and almost identically named Access functions, we can create a solution in Access to accomplish the same result. Please be careful: once the UPDATE query has run it will have changed your data; there is no “Undo”. You MUST back your data up before you try this exercise.
In this post:
- Create a test query
- Add functions to your test
- Test your test
- Add fields to your table
- Create your Update Query
- “Run, Forrest. Run!”
- Clean up
Your table, tblMembers, includes a field named “Fullname” that is storing two values — a first name and a surname — and you need to fix this problem by splitting the names into two separate fields.
Strategy: Find the first position of the first space in the field using the
InStr function. We can assume that the character on position before that is the last letter of the first name. Use the
Left function to return the part of the string up to that and including the letter in that position. Then, calculate the length of the entire string using the
Len function, subtract the position of the first space, and use the
Right function to return the part of the string from the right side to the position of the space.
2. Create a test query
Create a new Query called qryTesting. Add the field that contains the values that need to be split, in this example, the FullName field. Our first task is to work out at which position in the string the first space falls. In the Excel solution, we used Excel’s
FIND function; the equivalent function in Access is
The syntax is as follows:
InStr([start, ] string1, string2 [, compare ] )
The arguments in square brackets are optional and in our instance not needed so we will simply leave them out. string1 is the string you are searching in and string2 is the (sub) string you are searching for in string1. The function returns an integer representing the position of the first occurrence of string2 in string1.
Add the following calculated field in the query grid:
Space_pos: InStr([memberFullname]," ")
3. Add functions to your test
In the same manner, add 2 more calculated fields to the Query grid:
FirstName: Left([memberFullname],InStr([memberFullname]," ")-1)
Surname: Right([memberFullname],Len([memberFullname])-InStr([memberFullname]," "))
Right functions operate in exactly the same way as they do in Excel, except of course they take Field names instead of cell ranges as arguments.
4. Test your test
Test. Run the query! The position of the first space, as well as the names and surnames, should appear correctly in the 3 calculated fields as below:
Click in the FirstName and Surname fields and confirm that there are no trailing spaces (spaces after the text) in the field.
5. Add fields to your table
Add 2 new fields named FirstName & Surname below the Fullname field in the tblMembers table. Now that we know how to separate the data we can create our UPDATE Query.
6. Create your Update Query
Create a new Query:
- Save the Query as qryNameSplit
- Add tblMembers to the Query
- Add the memberFirstName to the Query grid
- Add the memberSurname to the Query grid
- Click the Update button
- The Update To row appeared when you selected the Update button in Step 5
- Add the calculation to the Update To field in the memberFirstName field
- Add the calculation to the Update To field in the memberSurname field
Save your UPDATE Query. Notice that the icon in the Object Bar has changed!
7. “Run, Forrest. Run!”
Run the Query.
Click Yes again.
Below is the before and after of your tblMember table:
8. Clean up
The final step is to delete the memberFullname field (and in doing so all its data) in the tblMembers table.