How do I transpose First and Last name

[NickTheBatMan]

I've several tables where the name is Joe Bloggs and I want it to be
Bloggs Joe.

I'm hoping there's going to be a simple way of doing this with SQL ?

TIA

Nick - a real beginner !

 

[George]

Actually, you need to have( at least) two separate fields: FirstName and
LastName. Possibly, MiddleName, Suffix(for things like "Jr.") or Title. You
should not have multiple values (both first and last names) in a single
field.

Also, you should NOT have names repeated in several tables. They should all
be listed together in a single table which is a roster of all people.
Whenever you need to refer to one of those people in a different table, you
do so by referance to the Primary Key for that record.

So, this looks like it's going to involve some major table changes first,
followed by a clean up in which you must move data into the proper roster
table.

Finally, since Access will have no reliable way to know whether the sequence
"Bloggs Joe" is the proper sequence, or whether it should be "Joe Bloggs",
the clean up is going to be largely manual. Take for example, the name
"Daniel Francis". Either of those could be a first name, and either of them
could be a last name. Not even a well-trained human could know which without
refering to external criteria.

However, once you do have a properly designed set of tables, this whole
issue vanishes.

HTH

George

 

[NickTheBatMan]

Actually, you need to have( at least) two separate fields: FirstName and
LastName. Possibly, MiddleName, Suffix(for things like "Jr.") or Title. You
should not have multiple values (both first and last names) in a single
field.

Also, you should NOT have names repeated in several tables. They should all
be listed together in a single table which is a roster of all people.
Whenever you need to refer to one of those people in a different table, you
do so by referance to the Primary Key for that record.

So, this looks like it's going to involve some major table changes first,
followed by a clean up in which you must move data into the proper roster
table.

Finally, since Access will have no reliable way to know whether the sequence
"Bloggs Joe" is the proper sequence, or whether it should be "Joe Bloggs",
the clean up is going to be largely manual. Take for example, the name
"Daniel Francis". Either of those could be a first name, and either of them
could be a last name. Not even a well-trained human could know which without
refering to external criteria.

However, once you do have a properly designed set of tables, this whole
issue vanishes.

HTH

George

Oh dear, I've described my problem wrongly again George - sorry

I am using someone else's db and can't change its design.
I am trying to import data from so some tables of names and associated
phone numbers in Word files and the easiest way I can think to do it
is drag and drop the table into an excel spreadsheet and import
that...

Very basically the format is Name = First name Last name in one
column, TelNr in the other column.
What I want sadly is Last Name in the first column...

I don't know how to do it, but is it possible to chop the name field
into 2 fields then transpose them then recombine them ?

I guess I'm asking too much of the software to dig me out of this
hole

Nick - total novice trying to learn rapidly...

 

[John W. Vinson]

Very basically the format is Name = First name Last name in one
column, TelNr in the other column.
What I want sadly is Last Name in the first column...

I don't know how to do it, but is it possible to chop the name field
into 2 fields then transpose them then recombine them ?

Yes... but there are names for which this is a problem.

Janet Lee Keyser's first name is Janet Lee. Her last name is Keyser.

Karl van der Steen's first name is Karl. His last name is van der Steen.

Any records with three or more words in the name field WILL need special
processing, using a human brain. Even there, some names are ambiguous.

For the simple cases, you can use a calculated field for first and last names:

FirstName: Left([fullname], InStr([fullname], " ") - 1)
LastName: Mid([fullname], InStr([fullname], " ") + 1)

The InStr function finds the position of the first blank in the name (thereby
giving the wrong result for "Lee Keyser, Janet"!!!), and the Left and Mid
functions extract the appropriate substring.

 

[roccogrand]

Nick,

Access doesn't care which columns contain your data. You could store the
FirstName in column 23 and the LastName in column 145, if you wanted to do
so.

You build your forms and reports to display your data in whatever way you
and your users want to see them. If you have the first name and the last
name in different columns and want to see them together use an & or +
operator to concatenate them.

And don't forget that you can use Excel to clean data if you are more
famliar with it. For example, you could use Excel's Text-to-Column feature
to split fullnames into a first name and a last name. Yes, you can do this
in Access but if Excel is more familiar to you, use that tool. (The Excel to
Access operation is pretty easy.)

Good luck.

LDN

 

[NickTheBatMan]

Very basically the format is Name = First name Last name in one
column, TelNr in the other column.
What I want sadly is Last Name in the first column...

I don't know how to do it, but is it possible to chop the name field
into 2 fields then transpose them then recombine them ?

Yes... but there are names for which this is a problem.

Janet Lee Keyser's first name is Janet Lee. Her last name is Keyser.

Karl van der Steen's first name is Karl. His last name is van der Steen.

Any records with three or more words in the name field WILL need special
processing, using a human brain. Even there, some names are ambiguous.

For the simple cases, you can use a calculated field for first and last names:

FirstName: Left([fullname], InStr([fullname], " ") - 1)
LastName: Mid([fullname], InStr([fullname], " ") + 1)

The InStr function finds the position of the first blank in the name (thereby
giving the wrong result for "Lee Keyser, Janet"!!!), and the Left and Mid
functions extract the appropriate substring.

Thank you John but I haven't a clue how to get the above SQL to
work...
I've done it LDN's way below - though I didn't know how too until he
mentioned it - and imported that and swapped it that way as I've been
shown how to do that in Access and have kept the query...

Nick - learning VERY rapidly !

 

[NickTheBatMan]

Nick,

Access doesn't care which columns contain your data.  You could store the
FirstName in column 23 and the LastName in column 145, if you wanted to do
so.

You build your forms and reports to display your data in whatever way you
and your users want to see them.  If you have the first name and the last
name in different columns and want to see them together use an & or +
operator to concatenate them.  

And don't forget that you can use Excel to clean data if you are more
famliar with it.  For example, you could use  Excel's Text-to-Column feature
to split fullnames into a first name and a last name.  Yes, you can do this
in Access but if Excel is more familiar to you, use that tool.  (The Excel to
Access operation is pretty easy.)  

Good luck.

LDN











- Show quoted text -

Thank you for the pointer Used it to do that and imported it and
used Access query to do the combination that way...

Nick - going through a MASSIVE learning curve !