selecting distinct fields in a record

[Jerry Crosby]

Say I have a table with 12 fields, named color1 through color12.
Each field has the name of a color, and there can be duplicates.
So, if you were to view the 12 fields in a record, it might look like this:
red, yellow, black, yellow, orange, blue, red, purple, black, yellow, green,
red

Now, here's the challenge. I want a list (could be a new field in a
make-a-table query) that has all the colors in the record. So, for the
above, it would be:

red, yellow, black, orange, blue, purple, green

Before I delve into writing a complex If...then monstrosity, I thought
someone might have a better idea.

Thanks in advance.

Jerry

 

[PC Datasheet]

A table with 12 fields named with a color is not normalized! The better idea
starts with a proper design of your table.
TblColor
ColorID
Color

 

[Jerry Crosby]

Didn't think I'd get chastised, but here's the "rest of the story" as Paul
Harvey would say.

The data table came over from another program, a major donor/accounting
program, and the fields, though not actually colors, are the designations of
the last twelve gifts the individual has given. I used colors in the
example to save having to explain everything.

Now, can you help me?

Thanks in advance.

Jerry

 

[John Spencer (MVP)]

Ouch,

You might try a normalizing query to break the data out and then Duane Hookom's
concatenate fields function to combine the data back into one field. Can't say
that I see a good reason to recombine them, but if you need to then you need to.

SELECT PrimaryKey, Field1
FROM YourTable
WHERE Field1 is not null
UNION
SELECT PrimaryKey,Field2
FROM YourTable
WHERE Field2 is not null
....
UNION
SELECT PrimaryKey,Field12
FROM YourTable
WHERE Field12 is Not Null

That would normalize the data into something like:
1111, Red
1111, Yellow
1111, Black
1111, Orange
1111, blue
1111, Purple
1111, Green
2222, Red
....

 

[Jamie Collins]

A table with 12 fields named with a color is not normalized! The better idea
starts with a proper design of your table.
TblColor
ColorID
Color

Steve, You have yourself committed a design flaw called 'attribute
splitting'. A color is usually an attribute of an entity rather than
an entity in itself (alarm bells should always sound in your head when
you are making up IDs that don't exist in reality). Exceptions are
made e.g. for data that changes frequently but the color names are
pretty stable <g>. It's a bit like having a table such as this:

tblSex
sexID
Sex

If you follow this line you end up slitting your database too much and
the entities lose their meaning in the data model (hey, sometimes
denormalization just makes sense <g>).

The correct design is to have a text column for the color name with a
CHECK constraint to ensure valid values. And to pick up on the sex
example above, there is a four value ISO standard (0=unknown, 1=male,
2=female, 9=lawful entity e.g. a company) which could be used in a
column's CHECK constraint.

Jamie.

--