query to combine columns

[deb]

I am working with a database that has many columns with the same data type. I
need to compile this data into a single column - replicating the data in the
other columns to create a series of separate records for each of the other
columns that are not null.

For example:

Original Table:
NAME RED YEL GRN
John Smith -1 -1 0
Sue Jones 0 0 -1

End Table:
NAME RED YEL GRN
John Smith -1
John Smith -1
John Smith 0
Sue Jones 0
Sue Jones 0
Sue Jones -1


Thank you in advance,

 

[Jeff Boyce]

Deb

I'm not clear from your description, but it sounds like you are trying to
create a spreadsheet! In Access you don't "name" the rows and columns.

Is there a reason you are trying to use Access instead?

In Access, a well-normalized relational table might look more like:

tblSomething
SomethingID
PersonID (not the name, the ID)
ColorID (not the color, the name)

And you'd only have three rows in this table, based on the "original table"
you provided.

Are you confusing how you store the data in Access with how you'd display
it? You don't need to make your table "look" like how you want your report
to come out.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[KARL DEWEY]

It looks like you have Yes/No data stored in field label colors.
Do you maybe really want something like this ---
NAME COLOR
John Smith RED
John Smith YEL
Sue Jones GRN
If so, then try this --
SELECT [Name], "Red" AS COLOR
WHERE [RED] = -1
FROM [Original Table]
UNION ALL SELECT [Name], "YEL" AS COLOR
WHERE [YEL] = -1
FROM [Original Table]
UNION ALL SELECT [Name], "GRN" AS COLOR
WHERE [GRN] = -1
FROM [Original Table];