FIRST NAME LAST NAME

[pauld]

The names of people in my database are in normal first name / last name
format, ie, John Smith.

I need to create a report where the names are displayed in last/first
format, ie, Smith, John.

I'm reasonably adept at Access 2003 (which is what I use for my db), but
can't figure out how to do this. I don't want to change the NAME field.

Can anybody offer some ideas?

Thank you.

 

[Jeff Boyce]

A basic rule of database design is "one fact, one field". By stuffing
"FullName" (John Smith) into a single field, your database violates this
principle.

If only it were only a matter of principles!

As you are finding out, working with the separate facts (FirstName,
LastName) is considerably more difficult if you don't store your raw data as
two fields.

.... and it gets much, much worse! If you have "John Claude van Damm" in
your list of people, the way you'd re-format that would be different than
the way you'd re-format "Colonel John Sanders, Jr.", and "John Smith".

One more thing, if your field name is truely "NAME", you and Access will be
quite confused. Access treats this word ("NAME") as a reserved word, so
what you think it means and what Access thinks it means ...!?!

My first recommendation would be to create two new fields in your 'person'
table (FirstName, LastName) and get all your current NAMEs split into their
respective parts. Then it is quite easy to use a query and add a new field:

NewField: [LastName] & ", " & [FirstName]

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[channell]

What Jeff said is correct. Another option, that I prefer, is to do the
following-

If you have a report, based on a query, then in the query you would have-

ContactID
FName (First Name)
LName (Last Name)
....and other information.
Create a new field (or expression) in your query as follows-
LastNameFirstName: [LName] & ", " & [FName]

This way, when you go to drag fields to create your report,
LastNameFirstName will be an option, and it will prevent you from having to
continually replicate that formula on future reports.

-Scott Channell

 

[channell]

My apologies Jeff... I did not read ALL of your post and just assumed you
didn't include a query. My mistake

A basic rule of database design is "one fact, one field". By stuffing
"FullName" (John Smith) into a single field, your database violates this
principle.

If only it were only a matter of principles!

As you are finding out, working with the separate facts (FirstName,
LastName) is considerably more difficult if you don't store your raw data as
two fields.

.... and it gets much, much worse! If you have "John Claude van Damm" in
your list of people, the way you'd re-format that would be different than
the way you'd re-format "Colonel John Sanders, Jr.", and "John Smith".

One more thing, if your field name is truely "NAME", you and Access will be
quite confused. Access treats this word ("NAME") as a reserved word, so
what you think it means and what Access thinks it means ...!?!

My first recommendation would be to create two new fields in your 'person'
table (FirstName, LastName) and get all your current NAMEs split into their
respective parts. Then it is quite easy to use a query and add a new field:

NewField: [LastName] & ", " & [FirstName]

Regards

Jeff Boyce
Microsoft Office/Access MVP

The names of people in my database are in normal first name / last name
format, ie, John Smith.

I need to create a report where the names are displayed in last/first
format, ie, Smith, John.

I'm reasonably adept at Access 2003 (which is what I use for my db), but
can't figure out how to do this. I don't want to change the NAME field.

Can anybody offer some ideas?

Thank you.

 

[pauld]

Thanks you both for your responses.

The field containing the names of the people is "SkipperName" (boat owners).
99% of the names are straight first/last separated by a space (John Smith,
Frank Jones, etc).

Would it be possible to add a new field to my database (I'll call it
ReverseName) and then use an update query to simply reverse the contents of
the SkipperName field?

And...at the risk of asking too much, to be separated by a comma?

What Jeff said is correct. Another option, that I prefer, is to do the
following-

If you have a report, based on a query, then in the query you would have-

ContactID
FName (First Name)
LName (Last Name)
...and other information.
Create a new field (or expression) in your query as follows-
LastNameFirstName: [LName] & ", " & [FName]

This way, when you go to drag fields to create your report,
LastNameFirstName will be an option, and it will prevent you from having to
continually replicate that formula on future reports.

-Scott Channell

The names of people in my database are in normal first name / last name
format, ie, John Smith.

I need to create a report where the names are displayed in last/first
format, ie, Smith, John.

I'm reasonably adept at Access 2003 (which is what I use for my db), but
can't figure out how to do this. I don't want to change the NAME field.

Can anybody offer some ideas?

Thank you.

 

[Jeff Boyce]

Take another look at what "channell" and I offered as expressions. You
should be able to modify that, if you only had two fields...<g>

When you say "99%", are you willing to live with the remainder looking bad?

If you would consider going to the trouble of adding a field and parsing
what you have into a new version, why not bite the bullet and put TWO new
fields in, storing FirstName and LastName?

(If you are willing to live with the remainder being incorrect, you could
try using Left(), Mid() and InStr() functions, something like:

MyNewName: Mid([SkipperName],InStr([SkipperName]," ")+1) & ", " &
Left([SkipperName],InStr([SkipperName]," ")-1)

(untested expression - adjust as needed)

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks you both for your responses.

The field containing the names of the people is "SkipperName" (boat
owners).
99% of the names are straight first/last separated by a space (John Smith,
Frank Jones, etc).

Would it be possible to add a new field to my database (I'll call it
ReverseName) and then use an update query to simply reverse the contents
of
the SkipperName field?

And...at the risk of asking too much, to be separated by a comma?

What Jeff said is correct. Another option, that I prefer, is to do the
following-

If you have a report, based on a query, then in the query you would have-

ContactID
FName (First Name)
LName (Last Name)
...and other information.
Create a new field (or expression) in your query as follows-
LastNameFirstName: [LName] & ", " & [FName]

This way, when you go to drag fields to create your report,
LastNameFirstName will be an option, and it will prevent you from having
to
continually replicate that formula on future reports.

-Scott Channell

The names of people in my database are in normal first name / last name
format, ie, John Smith.

I need to create a report where the names are displayed in last/first
format, ie, Smith, John.

I'm reasonably adept at Access 2003 (which is what I use for my db),
but
can't figure out how to do this. I don't want to change the NAME field.

Can anybody offer some ideas?

Thank you.

 

[George Hepworth]

Pardon me for jumping in. The kind of names (boat owners, in this case)
doesn't make any real difference here. What you need to do is create the two
fields for first and last names, as was recommended. Then, split the boat
owner names out so the FIrst names are in the first name field and the last
names are in the last name field.

THEN, when you need create a query in which the names are formatted as
LastName, FirstName, you can concatenate them:

LastFirst: [LastName] & ", " & [FirstName]

and when you need them formatted as FirstName LastName, you can concatenate
them:

FirstLast: [FirstName] & " " & [LastName]


In fact, what I often do is create BOTH fields in a single query so that I
can pick one or the other as the occasion calls for, without having to
create a second query.

If you do that, a third field in the query could be InitialLastName:

InitialLastName: Left([FirstName],1) & " " & [LastName]

And so on.

Thanks you both for your responses.

The field containing the names of the people is "SkipperName" (boat
owners).
99% of the names are straight first/last separated by a space (John Smith,
Frank Jones, etc).

Would it be possible to add a new field to my database (I'll call it
ReverseName) and then use an update query to simply reverse the contents
of
the SkipperName field?

And...at the risk of asking too much, to be separated by a comma?

What Jeff said is correct. Another option, that I prefer, is to do the
following-

If you have a report, based on a query, then in the query you would have-

ContactID
FName (First Name)
LName (Last Name)
...and other information.
Create a new field (or expression) in your query as follows-
LastNameFirstName: [LName] & ", " & [FName]

This way, when you go to drag fields to create your report,
LastNameFirstName will be an option, and it will prevent you from having
to
continually replicate that formula on future reports.

-Scott Channell

The names of people in my database are in normal first name / last name
format, ie, John Smith.

I need to create a report where the names are displayed in last/first
format, ie, Smith, John.

I'm reasonably adept at Access 2003 (which is what I use for my db),
but
can't figure out how to do this. I don't want to change the NAME field.

Can anybody offer some ideas?

Thank you.

 

[George Hepworth]

I should have read your reply first. Same concept of a query with variations
on name combinations to suit different requirements.

What Jeff said is correct. Another option, that I prefer, is to do the
following-

If you have a report, based on a query, then in the query you would have-

ContactID
FName (First Name)
LName (Last Name)
...and other information.
Create a new field (or expression) in your query as follows-
LastNameFirstName: [LName] & ", " & [FName]

This way, when you go to drag fields to create your report,
LastNameFirstName will be an option, and it will prevent you from having
to
continually replicate that formula on future reports.

-Scott Channell

The names of people in my database are in normal first name / last name
format, ie, John Smith.

I need to create a report where the names are displayed in last/first
format, ie, Smith, John.

I'm reasonably adept at Access 2003 (which is what I use for my db), but
can't figure out how to do this. I don't want to change the NAME field.

Can anybody offer some ideas?

Thank you.

 

[pauld]

I should have also said that the database produces a lot of reports, so
taking the field "SkipperName" and breaking it into 2 fields (FirstName and
LastName) would generate a lot of work since I'd have to change the reports
and the queries on which they are based.

If I can do this in a query, it would be great.

What Jeff said is correct. Another option, that I prefer, is to do the
following-

If you have a report, based on a query, then in the query you would have-

ContactID
FName (First Name)
LName (Last Name)
...and other information.
Create a new field (or expression) in your query as follows-
LastNameFirstName: [LName] & ", " & [FName]

This way, when you go to drag fields to create your report,
LastNameFirstName will be an option, and it will prevent you from having to
continually replicate that formula on future reports.

-Scott Channell

The names of people in my database are in normal first name / last name
format, ie, John Smith.

I need to create a report where the names are displayed in last/first
format, ie, Smith, John.

I'm reasonably adept at Access 2003 (which is what I use for my db), but
can't figure out how to do this. I don't want to change the NAME field.

Can anybody offer some ideas?

Thank you.

 

[pauld]

Jeff,

The expression was perfect and it worked.

THANK YOU SO MUCH !!

Take another look at what "channell" and I offered as expressions. You
should be able to modify that, if you only had two fields...<g>

When you say "99%", are you willing to live with the remainder looking bad?

If you would consider going to the trouble of adding a field and parsing
what you have into a new version, why not bite the bullet and put TWO new
fields in, storing FirstName and LastName?

(If you are willing to live with the remainder being incorrect, you could
try using Left(), Mid() and InStr() functions, something like:

MyNewName: Mid([SkipperName],InStr([SkipperName]," ")+1) & ", " &
Left([SkipperName],InStr([SkipperName]," ")-1)

(untested expression - adjust as needed)

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks you both for your responses.

The field containing the names of the people is "SkipperName" (boat
owners).
99% of the names are straight first/last separated by a space (John Smith,
Frank Jones, etc).

Would it be possible to add a new field to my database (I'll call it
ReverseName) and then use an update query to simply reverse the contents
of
the SkipperName field?

And...at the risk of asking too much, to be separated by a comma?

What Jeff said is correct. Another option, that I prefer, is to do the
following-

If you have a report, based on a query, then in the query you would have-

ContactID
FName (First Name)
LName (Last Name)
...and other information.
Create a new field (or expression) in your query as follows-
LastNameFirstName: [LName] & ", " & [FName]

This way, when you go to drag fields to create your report,
LastNameFirstName will be an option, and it will prevent you from having
to
continually replicate that formula on future reports.

-Scott Channell

:

The names of people in my database are in normal first name / last name
format, ie, John Smith.

I need to create a report where the names are displayed in last/first
format, ie, Smith, John.

I'm reasonably adept at Access 2003 (which is what I use for my db),
but
can't figure out how to do this. I don't want to change the NAME field.

Can anybody offer some ideas?

Thank you.

 

[Graham Mandeno]

Hi Paul

My turn to jump in ;-)

It wouldn't necessarily involve much work at all, and would save you heaps
of hassle in the long run.

All you need to do is add a calculated field:

SkipperName: [FirstName] & " " & [LastName]

to the query(s) on which your reports are based.

The rest will look after itself.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I should have also said that the database produces a lot of reports, so
taking the field "SkipperName" and breaking it into 2 fields (FirstName
and
LastName) would generate a lot of work since I'd have to change the
reports
and the queries on which they are based.

If I can do this in a query, it would be great.

What Jeff said is correct. Another option, that I prefer, is to do the
following-

If you have a report, based on a query, then in the query you would have-

ContactID
FName (First Name)
LName (Last Name)
...and other information.
Create a new field (or expression) in your query as follows-
LastNameFirstName: [LName] & ", " & [FName]

This way, when you go to drag fields to create your report,
LastNameFirstName will be an option, and it will prevent you from having
to
continually replicate that formula on future reports.

-Scott Channell

The names of people in my database are in normal first name / last name
format, ie, John Smith.

I need to create a report where the names are displayed in last/first
format, ie, Smith, John.

I'm reasonably adept at Access 2003 (which is what I use for my db),
but
can't figure out how to do this. I don't want to change the NAME field.

Can anybody offer some ideas?

Thank you.