Change a lot of words in a table

[marko]

Hi!

A have a problem. I have a table containing
ID,Brend,Model,Colour,Price.
The Colour field has colours like: YELLOW/BLACK/RED, YELLOW/RED, BLUE,
BLUE/NAVY/RED/CHARCOAL.
Now i would like to shorten them like this
YELLOW->YEL,BLACK->BLK,WHITE->WHT...
so that the colour field would look like this YEL/BLK/RED and so on.
Please help!

Marko

 

[Bob]

Marko -

A paramArray works pretty well in a situation where you have a number
of items to check for. It might be a tad slow for a large number of
records, but since this appears to be a one-time shot, it well may be
worth the wait.

******************** the code ********************
Public Function ReplHue(pStr As String, ParamArray varMyVals() As
Variant) As String
'*******************************************
'Re:
http://groups.google.com/group/comp.databases.ms-access/browse_
frm/thread/077757357b36d7da/44153ba1796871c6?hl=en#44153ba1796871c6
'Purpose: Replace specified portion of
' string with another designation.
'Coded by: raskew
'Inputs: from debug (immediate) window
' ? replHue("Green/White/Red/Black", "Yellow", "Yel",
"Black", "Blk", "White", "Wht")
'Output: Green/Wht/Red/Blk
'*******************************************

Dim i As Integer
Dim n As Integer
Dim strHold As String

strHold = pStr

For i = 0 To UBound(varMyVals) Step 2

n = InStr(strHold, varMyVals(i))

If n > 0 Then

strHold = Left(strHold, n - 1) & varMyVals(i + 1) &
Mid(strHold, n + Len(varMyVals(i)))

End If

Next i

ReplHue = strHold

End Function

******************** end code ********************

You can test it as shown in the comments. Once satisfied with its
operation,
with the options modified to meet your needs, create a test query with
a calculated field showing the results after applying ReplHue, e.g.
**********query starts**********
SELECT
tblColor2.ID
, tblColor2.Color
, replHue([color],"Yellow","Yel","Black","Blk","White","Wht") AS
Fixed
FROM
tblColor2;
**********query ends************
(replace table and field names as necessary)

Finally, after backing-up your table!!!, create an update query using
the logic shown above.

HTH - Bob

 

[pietlinden]

one way is to loop through the field in that table and update a piece
at a time. Use SPLIT to break out the values into single values and
then read them and sub out. then reassemble the thing when you're done.

 

[pietlinden]

one way is to loop through the field in that table and update a piece
at a time. Use SPLIT to break out the values into single values and
then read them and sub out. then reassemble the thing when you're
done.

of course, if you stored this in a more normalized way, none of this
would be necessary at all. You could run a few update queries and be
done with it.

 

[Bob]

Suspect that one update-query could sort it all out. What say you?

Bob

 

[Joseph Meehan]

Hi!

A have a problem. I have a table containing
ID,Brend,Model,Colour,Price.
The Colour field has colours like: YELLOW/BLACK/RED, YELLOW/RED, BLUE,
BLUE/NAVY/RED/CHARCOAL.
Now i would like to shorten them like this
YELLOW->YEL,BLACK->BLK,WHITE->WHT...
so that the colour field would look like this YEL/BLK/RED and so on.
Please help!

Marko

First note: It certainly looks like your table design is bad.

If you have a record that needs more than one color recorded for it,
almost always you then need a color table related to the original table. It
eliminates a lot of problems. That is the way a database works.

Say after me, Access is a database not a spreadsheet.

This technique is called normalization and there is information on it in
the Access help files or almost any book on databases.

 

[Bob]

Marko -

We've got the normalization 'mini-lectures' from Joseph and pietlin, et
al.
Let's wait and see if they provide a viable solution as to how to get
out of the problem.

It sounds good when you say it fast, but did you notice that they are
not providing any solutions towards solving the problem?

Bob

 

[Randy Harris]

Hi!

A have a problem. I have a table containing
ID,Brend,Model,Colour,Price.
The Colour field has colours like: YELLOW/BLACK/RED, YELLOW/RED, BLUE,
BLUE/NAVY/RED/CHARCOAL.
Now i would like to shorten them like this
YELLOW->YEL,BLACK->BLK,WHITE->WHT...
so that the colour field would look like this YEL/BLK/RED and so on.
Please help!

Marko

Marko,

If this is something you will need to do regularly, then you probably should
create a fancy function like others suggest. If, however, this is a one shot
deal, and you have A2K or later, you could do it very easily with some ugly
but effective brute force code.

DoCmd.RunSQL "Update tblName Set Colour=Replace(Colour,'YELLOW','YEL')"

DoCmd.RunSQL "Update tblName Set Colour=Replace(Colour,'BLACK','BLK')"

DoCmd.RunSQL "Update tblName Set Colour=Replace(Colour,'WHITE','WHT')"

etc...

And, as others suggest - make sure you backup BEFORE trying this.

HTH,
Randy

 

[Randy Harris]

Marko -

We've got the normalization 'mini-lectures' from Joseph and pietlin, et
al.
Let's wait and see if they provide a viable solution as to how to get
out of the problem.

It sounds good when you say it fast, but did you notice that they are
not providing any solutions towards solving the problem?

Bob

Normalization problems are generally much easier to prevent than correct.
;-)

 

[pietlinden]

Okay then. Use SPLIT to break out the field into individual values.
then when you get the stupid thing split out, do something like

For intCounter = 0 to UBound(varArray)
'write record to table
rs.Addnew
rs.Fields("SomeField")=varArray(intCounter)
...
rs.Update
Next IntCounter

is that what you wanted, smartass?
Gotta leave him SOMETHING to do! Ya know, exercise his brain.

Marko,
read up on SPLIT and playing with arrays in the VB help. that and
playing with recordsets AddNew and Update and all that stuff. Ho hum.

Happy now Bob?

 

[marko]

THANKS ALL!!

 

[Joseph Meehan]

Marko -

We've got the normalization 'mini-lectures' from Joseph and pietlin,
et al.
Let's wait and see if they provide a viable solution as to how to get
out of the problem.

It sounds good when you say it fast, but did you notice that they are
not providing any solutions towards solving the problem?

Bob

It is difficult to offer exact procedures when you don't have a good
knowledge of the data.

You might also note that assuming your procedure will work (I have no
reason to believe it would not) then Marko would have had that answer prior
to pietlin or my response was posted.

Sometimes just answering a direct question when there is a larger
underlying problem is not doing anyone a favor. It would appear that you
also believe that as you wisely added that making a backup first is a good
idea as it is.

 

[lylefair]

Sometimes just answering a direct question when there is a larger
underlying problem is not doing anyone a favor.

Yes. Doing so lessens the nature of CDMA as a body of knowledge. When
we search the archives of the newsgroup we could hope to find solutions
based on sound principles. This may be more important for beginners.

 

[Tim Marshall]

We've got the normalization 'mini-lectures' from Joseph and pietlin, et
al.
Let's wait and see if they provide a viable solution as to how to get
out of the problem.

There's not a lot of easy fixes. If this is a one time fix, Rick
Fisher's Find and Replace might be of use to Marko:

http://www.rickworld.com/

As to Bob's insistence on letting people continue with poor choices:

Sorry, Bob, but unless you are a spreadsheet designer, what's been said
about normalization simply can't be said enough. There is a bit of a
mental hump most of us have had to get over - we're not quite as
intelligent as you are, you see. The other thing to consider, is if one
just "provides a viable solution" as you are demanding, the concept of
learning about proper techniques, etc, is not served. From the tone of
your response, you are quite happy with letting people happily trot down
poorly chosen paths to oblivion. Presumeably you're one of these people
who don't give any warning and smile watches with amused intensity when
you see a car barrelling towards a washed out bridge.

 

[paii, Ron]

First note: It certainly looks like your table design is bad.

If you have a record that needs more than one color recorded for it,
almost always you then need a color table related to the original table. It
eliminates a lot of problems. That is the way a database works.

Say after me, Access is a database not a spreadsheet.

This technique is called normalization and there is information on it in
the Access help files or almost any book on databases.

Going the normalization route and assuming YELLOW/BLACK/RED is different
from YELLOW/RED/BLACK. You could run a make table query exporting all the
combinations of color from your data table, then create a one to many link
from this new color table to color in your old table allowing cascade
updates. Now all you would have to do is edit the new color table to change
the color in the data table.