Query to revise data in a table

  • Thread starter Thread starter Randy
  • Start date Start date
R

Randy

I have inherited a database which I am revising. (Access 2000) A table has
employee information with the last and first name in the same field: ie
(Smith, John) I want to revise the field [Name] and change to [LastName] and
[FirstName]. The problem is I have almost a thousand records of last name
and first name in the same field. Can a query be created that will
seperate lets say the first name and append this info to the field
[FirstName] Thanks...Randy
 
Randy

Yes, ... and no!

If you are 100% confident that every [FullName] is of exactly the same
format -- i.e., LastName comma space FirstName, you should check again!

Odds are pretty good that you'll have at least one name that breaks this
pattern.

A typical approach is to assume that all do, write and run the query (after
first saving a backup, then adding the two new fields to your table). Then,
go through row by row and fix the few that break the pattern.

If you know ahead of time that the pattern is considerably less than 100%,
can you find a way to break the data into subsets with standard patterns.

You can check the mvps.org website for potential "parsing" routines, but
nothing seems to beat human eyeballs and brains for this!

By the way, the word "Name" is a reserved word in Access, and will only
cause both you and Access confusion. Get rid of it!
 
Randy

Take a look at the Left(), Mid() and InStr() functions. Create a query
(select query first) that returns the leftmost portion of the name string up
to the comma, and the "middle" portion of the string that follows the space.

When this select query is working, make sure you have the two new fields in
your table, and convert the query to an update query. You'll be updating
the FirstName field with the second value you "calculated" in the previous
paragraph, and vice versa for LastName.

If you aren't sure how to create a query, take a little time to check the
documentation...
 
Assuming you have two new fields in your table named LastName and FirstName.

UPDATE YourTable
SET LastName = Trim(Left([Name],InStr(1,[Name],",",1)-1)),
FirstName = Trim(Mid([Name],Instr(1,[Name],",",1)+1))
WHERE [Name] Like "*,*"

WARNING: This will mess up on a name like Spencer, Jr, John

You will end up with
LastName = Spencer
FirstName = Jr, John

I would just search for First Name with a comma in them and manually edit these.
How do I write a query for this...Thanks
Jeff Boyce said:
Randy

Yes, ... and no!

If you are 100% confident that every [FullName] is of exactly the same
format -- i.e., LastName comma space FirstName, you should check again!

Odds are pretty good that you'll have at least one name that breaks this
pattern.

A typical approach is to assume that all do, write and run the query (after
first saving a backup, then adding the two new fields to your table). Then,
go through row by row and fix the few that break the pattern.

If you know ahead of time that the pattern is considerably less than 100%,
can you find a way to break the data into subsets with standard patterns.

You can check the mvps.org website for potential "parsing" routines, but
nothing seems to beat human eyeballs and brains for this!

By the way, the word "Name" is a reserved word in Access, and will only
cause both you and Access confusion. Get rid of it!

--
Good luck

Jeff Boyce
<Access MVP>
Click to expand...
Click to expand...
 
Thanks for the help...Randy
John Spencer (MVP) said:
Assuming you have two new fields in your table named LastName and FirstName.

UPDATE YourTable
SET LastName = Trim(Left([Name],InStr(1,[Name],",",1)-1)),
FirstName = Trim(Mid([Name],Instr(1,[Name],",",1)+1))
WHERE [Name] Like "*,*"

WARNING: This will mess up on a name like Spencer, Jr, John

You will end up with
LastName = Spencer
FirstName = Jr, John

I would just search for First Name with a comma in them and manually edit these.
How do I write a query for this...Thanks
Randy

Yes, ... and no!

If you are 100% confident that every [FullName] is of exactly the same
format -- i.e., LastName comma space FirstName, you should check again!

Odds are pretty good that you'll have at least one name that breaks this
pattern.

A typical approach is to assume that all do, write and run the query (after
first saving a backup, then adding the two new fields to your table). Then,
go through row by row and fix the few that break the pattern.

If you know ahead of time that the pattern is considerably less than 100%,
can you find a way to break the data into subsets with standard patterns.

You can check the mvps.org website for potential "parsing" routines, but
nothing seems to beat human eyeballs and brains for this!

By the way, the word "Name" is a reserved word in Access, and will only
cause both you and Access confusion. Get rid of it!

--
Good luck

Jeff Boyce
<Access MVP>
Click to expand...
Click to expand...
Click to expand...
 
This may be a silly question, but do I place this in the criteria, update to
Etc. I'm still a beginner Thanks. "Randy" > > Assuming you have two new
fields in your table named LastName and
FirstName.
UPDATE YourTable
SET LastName = Trim(Left([Name],InStr(1,[Name],",",1)-1)),
FirstName = Trim(Mid([Name],Instr(1,[Name],",",1)+1))
WHERE [Name] Like "*,*"

WARNING: This will mess up on a name like Spencer, Jr, John

You will end up with
LastName = Spencer
FirstName = Jr, John

I would just search for First Name with a comma in them and manually
Click to expand...
edit
these.
How do I write a query for this...Thanks
Randy

Yes, ... and no!

If you are 100% confident that every [FullName] is of exactly the same
format -- i.e., LastName comma space FirstName, you should check again!

Odds are pretty good that you'll have at least one name that breaks this
pattern.

A typical approach is to assume that all do, write and run the query
(after
first saving a backup, then adding the two new fields to your table).
Then,
go through row by row and fix the few that break the pattern.

If you know ahead of time that the pattern is considerably less than 100%,
can you find a way to break the data into subsets with standard patterns.

You can check the mvps.org website for potential "parsing" routines, but
nothing seems to beat human eyeballs and brains for this!

By the way, the word "Name" is a reserved word in Access, and will only
cause both you and Access confusion. Get rid of it!

--
Good luck

Jeff Boyce
<Access MVP>
Click to expand...
Click to expand...
Click to expand...
 
Looks like you are using the query grid to do this.

Open a new query.
Select YourTable
Put the three fields - name, LastName, FirstName into the grid
In the criteria cell under Name enter
LIKE "*,*"
Select Query: Update Query from the menu

In the Update cell under Last Name enter
Trim(Left([Name],InStr(1,[Name],",",1)-1))
In the update cell under First Name enter
Trim(Mid([Name],Instr(1,[Name],",",1)+1))

Select Query: Run from the menu. Access should run the query and tell you it is
about to update a number of records.

This may be a silly question, but do I place this in the criteria, update to
Etc. I'm still a beginner Thanks. "Randy" > > Assuming you have two new
fields in your table named LastName and
FirstName.
UPDATE YourTable
SET LastName = Trim(Left([Name],InStr(1,[Name],",",1)-1)),
FirstName = Trim(Mid([Name],Instr(1,[Name],",",1)+1))
WHERE [Name] Like "*,*"

WARNING: This will mess up on a name like Spencer, Jr, John

You will end up with
LastName = Spencer
FirstName = Jr, John

I would just search for First Name with a comma in them and manually
Click to expand...
edit
these.

Randy wrote:

How do I write a query for this...Thanks
Randy

Yes, ... and no!

If you are 100% confident that every [FullName] is of exactly the same
format -- i.e., LastName comma space FirstName, you should check again!

Odds are pretty good that you'll have at least one name that breaks this
pattern.

A typical approach is to assume that all do, write and run the query
(after
first saving a backup, then adding the two new fields to your table).
Then,
go through row by row and fix the few that break the pattern.

If you know ahead of time that the pattern is considerably less than 100%,
can you find a way to break the data into subsets with standard patterns.

You can check the mvps.org website for potential "parsing" routines, but
nothing seems to beat human eyeballs and brains for this!

By the way, the word "Name" is a reserved word in Access, and will only
cause both you and Access confusion. Get rid of it!

--
Good luck

Jeff Boyce
<Access MVP>
Click to expand...
Click to expand...
Click to expand...
 
Back
Top