Forum home » Delegate support and help forum » Microsoft Access Training and help » Access - splitting imported data
|
|
|
The UK's most regular Training information: See also: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Access - splitting imported data
Resolved · Low Priority · Version 2007
Reply Wed 24 Mar 2010, 10:21Delegate Greg said...
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??
For upcoming training course dates see: Pricing & availability
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
Thu 1 Apr 2010: Automatically marked as resolved.
|