Solved

copy part of record to another field

Posted on 2008-10-31
4
536 Views
Last Modified: 2012-05-05
Hello,
In the database I am working on, the Last Name & First Name fields have been used to store all naming information about the contacts.  In the last name field there are prefixes, organizations names, etc.
In the first name field there are more prefixs, suffixs, and spouse names.
I specificly need help coping only the spouse name to a new field, but I hope the solution can work for all the date cleaning problems I have with these two fields.
The data in the FIRST NAME field I need to parse out is mainly in the format of "name & name"
I can't find out how to copy just the text after "& name"
I have tried Instr, but am not knowledgeable enought about the details of Access to use it (does it go in the design part of a query, is it used in making a SQL statement, or does it need to be in a modules?)
(I know the names of the different parts of Access, but not how to use them, yet)
Thank you.
0
Comment
Question by:jaud
  • 2
  • 2
4 Comments
 
LVL 15

Expert Comment

by:dbbishop
ID: 22856092
The following should do it. The IIf() function first uses the InStr function to determine if there is an '&' in the column. If so, the Mid() function is used to pull the data following the '&' (+2 to skip the character and the space following it. If there is no '&', the else portion of the IIF() function returns the whole name.

SELECT IIf(InStr([FirstName],"&") > 0, Mid([FirstName], InStr([Firstname], "&") + 2), FirstName) AS Name
FROM tblEmployee;
0
 

Author Comment

by:jaud
ID: 22857635
Thanks dbbishop,

I copied your code, modified it for my values, and ran it.  I appreciate you telling me what different parts and functions of your code are for and what they do.  

I made a new query in design mode, went to view, selected SQL and entered the following:

SELECT IIf(InStr([FIRSTNAME],"&") > 0, Mid([FIRSTNAME], InStr([FIRSTNAME],
"&") + 2), FIRSTNAME) AS SPOUSENAME
FROM tblPeople;

The query returned the results of all names in the FIRST NAME field, not just the results of the characters after the "&".   I need the query to only return results for the records after the "& " sign.  I guess I need the the 'else' portion of the IIF() function to leave the data as it is and not return a value if there is no "&".  (however, I did not see an "else" portion in the code you gave me).  

I also did not see how the query would update the results to the SPOUSENAME field.  The query just showed me results.  I can add the id field to this query, make a table tblSpouse, then join tblSpouse and tblPeople in a query via the id field and run an update, but that would leave me with the spouse name in the FIRSTNAME field and the SPOUSENAME field.  Then I could go to the table and do a find a replace to get rid of the "& *".

The make table solution would work, but I am really hoping a query can select the text I want, update it to the field I want, and then delete it from the original record.  I will need to run this query and all associated procedures (find & replace) many times to move other parts of data to the correct field (middle names/ initials in particular).
0
 
LVL 15

Accepted Solution

by:
dbbishop earned 500 total points
ID: 22858262
I just provided the code to parse the FirstName for the SPouseName. To actually update and the delete, I would recommend two passes, although it can be done with one update. The first pass will update SpouseName. You can then check and make sure it ran properly, then run the second update to remove the SpouseName from FirstName.

As always, I recommend that you work with a backup copy of your table.

UPDATE tblPeople
SET SpouseName = Mid([FIRSTNAME], InStr([FIRSTNAME], "&") + 2)
WHERE InStr([FIRSTNAME],"&") > 0

UPDATE tblPeople
SET FirstName = LEFT([FIRSTNAME], InStr([FIRSTNAME], "&") - 1)
WHERE InStr([FIRSTNAME],"&") > 0

-- Both updates can be done at once in this manner:
UPDATE tblPeople
SET SpouseName = Mid([FIRSTNAME], InStr([FIRSTNAME], "&") + 2),
       FirstName = LEFT([FIRSTNAME], InStr([FIRSTNAME], "&") - 1)
WHERE InStr([FIRSTNAME],"&") > 0
0
 

Author Closing Comment

by:jaud
ID: 31512275
Great solution.  The explaination of the terms will help me learn how to do these things myself.
I will have more questions soon.   Answers such as these are great.
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now