?
Solved

Access Database- Split Field into 2 fields

Posted on 2007-11-27
13
Medium Priority
?
812 Views
Last Modified: 2010-04-21
I have an access database with a field called acct.     The field is varying lengths of numbers.
Based on the field "Application" I need to move the last 3 or 5 numbers at the end to another field that I can create in the DB.   For example, If Application is IRA and the account number is 111111111222, I need to move the 222 to a separate field and remove it from the account number field.  But the 111111111 could be from 4 to 9 characters long and still have a 3 digit "sub number".  I just need the 222 sub number removed.  I have about 200,000 records to manipulate.    I imported a text file into access to accomplish this but I am open to any other suggestions.  Thanks in advance for your help.

0
Comment
Question by:kgregar
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 2
13 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20357964
you can use the left, right or mid function

if the acct is  more than 9 digits you can simply use

mid([acct],10) to get the sub number

how is the acct formatted if there are less than 9 digits  ( 4-9 characters)?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20357973
if the three digits sub number is constant in length (3 digits), you can use

right([acct],3)   to get the three digit sub number what ever the lentgh of the acct numbet
0
 
LVL 27

Expert Comment

by:MikeToole
ID: 20358003
update MyTable set SubAcct = Right(Acct,3), Acct = left(Len(Acct) - 3)
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20358008
and to get the acct number you can use the left function

left([acct],len([acct])-3)
0
 
LVL 27

Accepted Solution

by:
MikeToole earned 1000 total points
ID: 20358028
Oops, missed a bit
update MyTable set SubAcct = Right(Acct,3), Acct = left(Acct, Len(Acct) - 3)
0
 

Author Comment

by:kgregar
ID: 20359036
My table has many applications.  So I want to use the above just for one type, example IRA, then I need one with right(acct,5) for CLN, etc....  

can I run this from a query that has the applications defined?  thanks again.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20359092
you have to do that in a query..
0
 

Author Comment

by:kgregar
ID: 20360524
Sorry, I am so frustrated with this project I cannot think clearly.
 I am trying to write this so it checks the application for IRA then runs the above code.  I can get the query and I can get the above but I cannot get them together.  your help is appreciated.

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20360559
explain  <so it checks the application for IRA >  in details
0
 

Author Comment

by:kgregar
ID: 20360586
I need this:  SELECT REPORT.BUS_PERIOD, REPORT.CUST_ID, REPORT.APPLICATION, REPORT.ACCT_NBR, REPORT.PRIM_ACCT_OWNER,  REPORT.SubNum
FROM REPORT
WHERE (((REPORT.APPLICATION)="IRA"));
then I need to do this:  update MyTable set SubAcct = Right(Acct,3), Acct = left(Acct, Len(Acct) - 3)
But I don't want to update the entire table, just the IRA part (with the above select statement).

thanks again.

0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 1000 total points
ID: 20360704
so you want to update the field SubNum in table report

update report set subnum=right([ACCT_NBR],3), ACCT_NBR=left([ACCT_NBR],len([ACCT_NBR])-3)
where APPLICATION="IRA"
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20360711
create a back up of your table before  running the update query
0
 

Author Closing Comment

by:kgregar
ID: 31411193
Thank you!!
0

Featured Post

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question