[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

DB Convert Employee Name in fields

Posted on 2012-08-30
26
Medium Priority
?
350 Views
Last Modified: 2012-10-25
I have an Access 2007 DB and one of the main tables "TABLE-A" has a "Name Employee" field name and Text data type. Almost 98% are "LASTNAME, FIRSTNAME MIDDLENAME". The other 2% are either "LASTNAME, FIRSTNAME MI", "LASTNAME, FIRSTNAME".

I have another table "TABLE-B" that has a "Name Employee" field name and Text data type. Almost 98% are "Lastname, Firstname MI". The other 2% are "Lastname, Firstname".

TABLE-B is a new import table and everything that we go off of is in TABLE-A using "Name Employee". I wil be importing TABLE-B once a week.

How can I convert TABLE-B Name Employee to match TABLE-A Name Employee?

I am open to any and all ideas on how to best do this, I am very new to DB's.

-Thanks
0
Comment
Question by:CMILLER
  • 14
  • 8
  • 4
26 Comments
 
LVL 4

Expert Comment

by:jekautz
ID: 38352576
You described two different formats in Table-A and the same for Table-B.  Which format do you want to keep and which do you want to convert?
0
 
LVL 46

Expert Comment

by:aikimark
ID: 38352762
You should split this data out into separate columns for both tables.  You haven't finished normalizing your data relative to this column.

Note: Matching by name can cause problems, since there can be two or more people with the same (common) name.  Also, since you aren't containing suffixes, you can have duplicate names for inter-generational males (II, Jr, Sr, III, IV, etc.).  You need to think about a tie-breaker column for duplicate names (DOB, drivers license number, employee ID, etc.)
0
 

Author Comment

by:CMILLER
ID: 38352809
jekautz,

I want to keep the format in TABLE-A , field "Name Employee" like this,
"LASTNAME, FIRSTNAME MIDDLENAME" ( All Uppercase )

If you notice the format in TABLE-B is lower case with only the First letter and MI are Uppercase.

In the long run I dont know if it will ever matter if its upper or lower case, I just know I need it to be "lastname, firstname middlename" ( "smith, john q" ). Do either of you know if that would matter?

aikimark, I think I get what you are saying, good points.

Currently the only thing I can tie TABLE-B to TABLE-A with is a name.

I do like the idea of, if something doesnt match flag it, not quite sure how to do that though. I have another project that involves flagging and text changing colors. I will be posting that question tonight.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 46

Accepted Solution

by:
aikimark earned 1000 total points
ID: 38352908
Assuming you know now to parse the whole names into their components, you can approach this in a stepped manner
* look for duplicate names within each list - delete the duplicates or give them a zero strength-of-match value.

* look for partial duplicates within each list - flag them
These are rows where you have duplicate [firstname, lastname] combinations and the first letter of the middle name is the same as the MI of another row

* a strength-of-match approach would be to find exact matches on three fields (4), then [firstname, lastname] matches with a MI in one table = Left(Middlename, 1) of the other table (3),  then [firstname, lastname] matches with Null values in the middlename columns (2)
That should cover about 95% of your rows.  You can then look at the unmatched values and decide if you need to continue with a match on [lastname] and the first letter of the [firstname] (1) or check for name spelling errors.

* based on your validation, you might need to create and populate a new table that will help resolve weak matching strengths or duplicates or other errors.
0
 

Author Comment

by:CMILLER
ID: 38352915
I understand the approach you have layed out but I am not sure I know how to "parse the whole names into their components" other than going through 3000 records line by line.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 38352941
After adding new columns, you run a series of queries to populate the new columns
Update tblA
Set Lname = Trim(Left(Wholename, Instr(Wholename,",") -1)),
Fname = Trim(Mid(Wholename, Instr(Wholename,",") +1))

Update tblA
Set Mname = Trim(Mid(Fname, Instr(Fname," ") +11)),
Fname = Trim(Left(Wholename, Instr(Wholename," ") -1))
Where Instr(Wholename," ")<>0

Open in new window

Repeat the above queries for tblB
0
 

Author Comment

by:CMILLER
ID: 38352971
I am getting a "Syntax error in update statement
0
 
LVL 4

Assisted Solution

by:jekautz
jekautz earned 1000 total points
ID: 38352977
Access requires a semicolon at the end of each statement.  So try placing one at the end of line 3 and line 8.  You may need to separate the UPDATE statements into two seperate queries in Access.
0
 

Author Comment

by:CMILLER
ID: 38352988
Do i need to name the new columns Lname, Fname, etc.... I guess I am confused on how this pulls from TABLE-A.Name Employee
0
 

Author Comment

by:CMILLER
ID: 38352995
I am now getting an error on this line.

Set Mname = Trim(Mid(Fname, Instr(Fname," ") +11)),
0
 

Author Comment

by:CMILLER
ID: 38352999
And here also

Fname = Trim(Mid(Wholename, Instr(Wholename,",") +1))
0
 
LVL 4

Expert Comment

by:jekautz
ID: 38353003
You can name them anyway you want. If you use spaces in a field name you will need to put the name in brackets.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 38353056
They must be separate queries.  You can only have one of the SQL statements in a query.

There are errors in the second query
Update tblA
Set Mname = Trim(Mid(Fname, Instr(Fname," ") +1)),
Fname = Trim(Left(Fname, Instr(Fname," ") -1))
Where Instr(Fname," ")<>0

Open in new window

0
 

Author Comment

by:CMILLER
ID: 38353067
ok, I will now give it another try
0
 

Author Comment

by:CMILLER
ID: 38353075
ok,

Query1 created two columns ( Lname & Fname ) and there are 1098 BLANK rows, there are 1098 FILLED rows in TABLE-A.

Query2 created two columns ( Mname & Fname ) with only one BLANK row.
0
 

Author Comment

by:CMILLER
ID: 38353077
TABLE-A currently only has one column named Wholename with 1098 rows with names in them.
0
 

Author Comment

by:CMILLER
ID: 38353117
After looking at your SQL code, I am not sure we are on the same page.

*************************************************************************
TABLE-A has the names in one column.

Column1=SMITH, JOHN PUBLIC
*************************************************************************
TABLE-B

Column1=Smith, John P
*************************************************************************

What I need is for TABLE-B Column1 to look like TABLE-A Column1
0
 
LVL 46

Expert Comment

by:aikimark
ID: 38354189
I'm trying to get you to a point where you can join these two tables.  For that, you need to parse the data into separate columns.

What do you mean by 1098 blank rows?
0
 

Author Comment

by:CMILLER
ID: 38355913
When i run the query it creates a view with two columns that has 1098 blank rows ( 1-1098) no info showing in the query.  So, its seeing the table and it knows the table has 1098 names in it but the query doesnt produce any results other than it knows about 1098 objects.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 38355971
These are update queries that shouldn't return any rows.
0
 

Author Comment

by:CMILLER
ID: 38356324
I agree but thats what it does.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 38356380
Are you seeing a message that says something like "About to update # records"?  If not, then you are not running this as an update query.
0
 
LVL 4

Expert Comment

by:jekautz
ID: 38356416
In Access there is a red (or green) exclamation point that executes an action query.  Make sure you are doing this and not clicking on the view button on the left of the toolbar.
0
 

Author Comment

by:CMILLER
ID: 38406372
Sorry, about my delay. I couldnt get it to do a simple update, I even tried to do a basic update with a basic access database on my home pc and I couldnt get it to work either.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 38406401
@CMILLER

please answer my question about how you are trying to run this query.
0
 

Author Comment

by:CMILLER
ID: 38534512
Sorry, I thought I had closed this question.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
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: …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

834 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