Query to insert column name as field value

[TeriB]

I have a table with columns for User ID, Username, and then 76 columns for
function. My table has 2031 records. Each record has an X in certain Function
columns. Some records may only have an X in one column, but some have up to 7.

I need to create a query that lists each record's User Name, Uers ID & then
one new column - where I want to insert the name of the function, based on
the column the X is in. Other than a nested IIF statement, I can't think of a
way to do this.

Please help

 

[KARL DEWEY]

That is how your table should be structured.
Use a union query --
SELECT User ID, Username, "Function_A" AS [Function]
FROM YourTable
WHERE [Function_A] = "X"
UNION SELECT User ID, Username, "Function_B" AS [Function]
FROM YourTable
WHERE [Function_B] = "X"
UNION SELECT User ID, Username, "Function_C" AS [Function]
FROM YourTable
WHERE [Function_C] = "X"
....
UNION SELECT User ID, Username, "Function_76" AS [Function]
FROM YourTable
WHERE [Function_76] = "X";

 

[Jerry Whittle]

I'm sorry to say that the problem is because the table is set up incorrectly.
By having 76 columns for functions and putting an X in them, you've commited
spreadsheet which is a serious database crime.

Seriously what you need is a second table which looks something like:

User ID Function
1 Date
1 Year
3 DCount
2 Date

You would link this table with the User table by the User ID field. Then a
simple query with the two tables joined would give you what you wish.

To use the table that you have now, you'll need to do a query on each of the
76 function fields and join them together with UNION.

Select User ID, Username, "Date"
From YourTable
Where [Date] = "x"
UNION ALL
Select User ID, Username, "DCount"
From YourTable
Where [DCount] = "x"
UNION ALL
and so on for all 76 fields.

This is going to be one LONG query and have a lot of chances to induce typos
and other errors. Also what happens when someone adds a 77th function? Back
to the drawling board!

BTW: You could create the new table that I described above and populate it
with something like the query above but without the Username field.

A nested IIf would be much, much worse.