Greg has attended:
Access Introduction course
Access Intermediate course
Access Advanced course
Access - splitting imported data
I have a table of imported data which includes one column of 8 numbers which I need to split. I want to create a seperate column in access (in the original table if possible) of the 5 numbers on the left only as this represents a seperate identifier which I would use for grouping up of clients. How do I do this??
RE: Access - splitting imported data
Hi Greg,
Thank you for your question.
As long as the field is a text data type then you can use the left function within Access.
I suggest you could try the following:
1. Before you do anything take a copy of your table just in case as back up.
2. Create a query based on your copied table and include all the fields.
3. Save the query with a name.
4. Click into the next available blank Field Name cell and click the wand up on your toolbar (expression builder or Build).
5. Type the name of the new field followed by a colon.
6. Below is the formula you need:
new field name: left([Name of the field with the 8 numbers],5)
This will give you the first five characters from the left of the field.
Then change the query type to a Make Table query and specify a new table name that will include all the original data plus the extra column.
Run the query using the red exclamation mark and accept the You are about... and paste ... message boxes. Close and save the query and then go and open the new table that was generated.
Try this and tell me how you get on.
Regards
Simon
