changing of names

J

JustinO

We have an access database that I've designed to print statistics, reports,
and graphs based on sales data from our company. I use the men's names (no
numbers ids) to distinguish the men from each other in the reports. Now, one
of our men has changed the name used on his sales orders. I'm trying to
figure out a way to display all his results together without having to go
back and change all his name references to the new name. Does anyone know of
any easy way to do this?

Thanks in advance.
Justin
 
J

John Vinson

We have an access database that I've designed to print statistics, reports,
and graphs based on sales data from our company. I use the men's names (no
numbers ids) to distinguish the men from each other in the reports. Now, one
of our men has changed the name used on his sales orders. I'm trying to
figure out a way to display all his results together without having to go
back and change all his name references to the new name. Does anyone know of
any easy way to do this?

Thanks in advance.
Justin
Click to expand...

Well, you've now found the lesser of the two big reasons why it's
recommended to ALWAYS store names in a People table, and store only
links to the name. The bigger reason to do so is that names are not
unique; what would you do if you had two salesmen both named Jim
Smith?

You can run an Update query on every table where names have been
propagated. Not knowing the structure of your database, I don't know
which tables these would be. Unless you've done something very
strange, though, you needn't do anything with the Reports - just the
tables.

If you have a table named Sales, with a field Salesman, and you want
to change all occurances of "Fred Smythe" to "Fred Smith", create a
Query based on Sales; select only the Salesman field. Change the query
to an Update query using the Query menu option. Put

"Fred Smythe"

on the Criteria line to select only Fred's sales; then type

"Fred Smith"

on the Update To line. Click the ! icon to run the query.

If you store the data under both names, you have no good way to lump
them together.

John W. Vinson[MVP]
 
J

JustinO

Thanks. That's what I needed to know.

John Vinson said:
Well, you've now found the lesser of the two big reasons why it's
recommended to ALWAYS store names in a People table, and store only
links to the name. The bigger reason to do so is that names are not
unique; what would you do if you had two salesmen both named Jim
Smith?

You can run an Update query on every table where names have been
propagated. Not knowing the structure of your database, I don't know
which tables these would be. Unless you've done something very
strange, though, you needn't do anything with the Reports - just the
tables.

If you have a table named Sales, with a field Salesman, and you want
to change all occurances of "Fred Smythe" to "Fred Smith", create a
Query based on Sales; select only the Salesman field. Change the query
to an Update query using the Query menu option. Put

"Fred Smythe"

on the Criteria line to select only Fred's sales; then type

"Fred Smith"

on the Update To line. Click the ! icon to run the query.

If you store the data under both names, you have no good way to lump
them together.

John W. Vinson[MVP]
Click to expand...
 
Top