Copying partial field data to a masked field

[Guest]

Hi,

I have a database of about 10,000 items. It contains about products from
multiple manufacturers.

The product ID field has a bunch of characters in it.

I want to pull the first 7 characters only of this field, and put them in a
new column within the table. Also, I would like to add a two character code
to the begining of each item to signify the manufacturer.

Is this possible? I tried searching through the knowledgebase, but I
couldn't figure out what this would be called.

Thank you for the help!!

 

[John Vinson]

Hi,

I have a database of about 10,000 items. It contains about products from
multiple manufacturers.

The product ID field has a bunch of characters in it.

I want to pull the first 7 characters only of this field, and put them in a
new column within the table. Also, I would like to add a two character code
to the begining of each item to signify the manufacturer.

Is this possible? I tried searching through the knowledgebase, but I
couldn't figure out what this would be called.

Thank you for the help!!

It's possible. It's also a Very Bad Idea to store data redundantly!

You can create a Query based on your table with a calculated field

Left([ProductID], 7)

to extract the first seven characters. Of course, with no control over
the contents of the ID, you have no guarantee that this value will be
unique - it probably won't, I suspect you may have quite a few values
like 0000000 or 0000001.

If you have a table of Manufacturers, you can add a two-character
field to (uniquely? might be tough) identify each one; you can - again
- dynamically concatenate this ID to the ProductID in a query:

ShowID: ManufacturerCode & ProductID

Storing the code redundantly in the ProductID will give you far more
pain than benefit!

John W. Vinson[MVP]