Changing names

[Guest]

Hello there,

I have a table with names, Service codes, and Dollar amounts. For example,
Jimmy Smith has service codes 111 and 222 and the Dollar amounts $50 for 111
and $100 for 222; therefore, his name was listed twice. However, the names
were misspelling by the data entry persons. For example, his name was listed
as Jimmy Smith and James Smith.

I am trying to combine data by the names and by the total Dollar amount, not
by the codes. Is there a way to change or replace the James Smith with Jimmy
Smith? There are several customer names (42 of them) were misspelling like
that.

I wrote a SQL "IIf([Customer name]="James Smith", "Jimmy Smith",
IIf([Customer name]="Billy Wilson", "William Wilson", IIf([.......)). But I
cannot use IIf for 42 times because Access will give me error.

Please someone smarter helps me out. Thanks.

 

[Jeff Boyce]

Are you saying that your table has PersonA's name (with misspellings)
repeated as many times as there are ?ServiceRecords? That would work well
in a spreadsheet, but not so well in Access.

An alternate approach would be to step back from the database and normalize
your data.

In the mean while, to solve your immediate problem, couldn't you just update
all of the Jimmy Smith names to James Smith? An update query would handle
this. You would have to run it ?42 times to get all your customers' names
consistent.

The downside of doing this is that the very next data entry could force you
to re-run all 42 queries. And the more serious risk is that you have two
customers, father & son, James AND Jimmy Smith, and you can't tell them
apart!

Now we're back to normalizing your data. To make better use of the
functions and features of Access, consider the following ideas for tables:

tblPerson
PersonID (Primary Key - a unique identifier - for persons, often an
Autonumber)
FirstName
LastName
DOB
StreetAddress
City
State/Province
PostalCode
... (other facts about the person)

tblService
ServiceID (see above)
PersonID (the ID from above identifying which person -- don't use their
name here!)
ServiceCode (?your 111, ...)
ServiceDate (don't use "Date", as this is a reserved word in Access)
... (other facts about the service)

 

[Guest]

Thanks!

I used IIf statement to create 7 fields: "Name1", "Name2", "Name3",...
because there is a limit on how many IIf statement I can use. I also created
a last field "newname" that I concatenate Name1, 2, 3...to get a final field.
It works for this month report, but I don't know if next month there will be
spelling problems like that. I will follow Jeff's advice to create a more
detail table. Many thanks.

Hello there,

I have a table with names, Service codes, and Dollar amounts. For example,
Jimmy Smith has service codes 111 and 222 and the Dollar amounts $50 for 111
and $100 for 222; therefore, his name was listed twice. However, the names
were misspelling by the data entry persons. For example, his name was listed
as Jimmy Smith and James Smith.

I am trying to combine data by the names and by the total Dollar amount, not
by the codes. Is there a way to change or replace the James Smith with Jimmy
Smith? There are several customer names (42 of them) were misspelling like
that.

I wrote a SQL "IIf([Customer name]="James Smith", "Jimmy Smith",
IIf([Customer name]="Billy Wilson", "William Wilson", IIf([.......)). But I
cannot use IIf for 42 times because Access will give me error.

Please someone smarter helps me out. Thanks.

Bltony,

Jeff's advice is good, but if you're stuck with your current db
design, you may also want to look into the Switch() function. There
is still a limit on how many expressions you can place inside it, but
the contents are far more manageable than a big IIf() statment.


Sincerely,

Chris O.

 

[Chris2]

Hello there,

I have a table with names, Service codes, and Dollar amounts. For example,
Jimmy Smith has service codes 111 and 222 and the Dollar amounts $50 for 111
and $100 for 222; therefore, his name was listed twice. However, the names
were misspelling by the data entry persons. For example, his name was listed
as Jimmy Smith and James Smith.

I am trying to combine data by the names and by the total Dollar amount, not
by the codes. Is there a way to change or replace the James Smith with Jimmy
Smith? There are several customer names (42 of them) were misspelling like
that.

I wrote a SQL "IIf([Customer name]="James Smith", "Jimmy Smith",
IIf([Customer name]="Billy Wilson", "William Wilson", IIf([.......)). But I
cannot use IIf for 42 times because Access will give me error.

Please someone smarter helps me out. Thanks.

Bltony,

Jeff's advice is good, but if you're stuck with your current db
design, you may also want to look into the Switch() function. There
is still a limit on how many expressions you can place inside it, but
the contents are far more manageable than a big IIf() statment.


Sincerely,

Chris O.