Assistance requested

[Guest]

I have a database where in one field the users can enter multiple values,
separated by a comma. In other words, I have listed some of the possible
entries:

1
1,2
1,3
1,2,4
1,4

I need to figure out a way to translate that into English. Each number
represents a product code, 1 = phone, 2 = analog video, 3 = digital video, 4
= internet, etc... I want to create a field so that the code translates as
shown below:

1 = Phone
1,2 = Phone and Analog Video
1,3 = Phne and Digital Video
1,2,4 = Phone, Analog Video, Internet
1,4 = Phone, Internet

Any ideas on how I can accomplish this ?

Thanks !

 

[tina]

storing multiple values in one field is a violation of normalization
principles, and generally a bad idea. can you explain what you're trying to
accomplish, and why you're doing it that way? probably we can suggest
solutions that follow relational design principles.

hth

 

[Rick B]

First, you need to fix your data structure. It is not proper design to
store more than one piece of information in a file.

This is a classic one-to-many relationship. You need a separate related
table to store these attributes. If the item has only one attribute, then
there would be one record in the related table. If they have three, then
there would be three.

As far as displaying the name instead of the number, this is typically done
by having a separate table with two fields, the attribute number, and the
attribute name. You can then include this table in your queries and relate
the attribute number to the number in your existing records.


Sounds like you need to step back and rethink your design. It needs to be
"normalized" and it needs to use the features available in a relational
database.

 

[Guest]

Eric:

You could parse the field. However, have you considered simply using 4
checkboxes to identify the four communications types? Form usage would be
minimal, and the data is already separated for you.

HTH

Sharkbyte