Query a Table with many fields

[ACCESSDUMMY]

The table that I'm querying has one key field and 100 other fields. For each of those 100 data fields that has a value greater than zero, I will insert a record into another table, that contains the key field and the "greater than zero" field.

This an example of a table I'm trying to query from:

Key field | DataField1 | DataField2 | DataField3 | etc...
emp1 | 25 | | 10 |
emp2 | 35 | 20 | |
emp3 | | 10 | |
emp4 | 25 | 50 | 25 |


I need to the results to go to a table similar to this.

Key field | ColumnName | DataInColumn |
emp1 | DataField1 | 25 |
emp1 | DataField3 | 10 |
emp2 | DataField1 | 35 |
emp2 | DataField2 | 20 |
emp3 | DataField2 | 10 |
emp4 | DataField1 | 25 |
emp4 | DataField2 | 50 |
emp4 | DataField3 | 25 |


As you can see, I actually need to retrieve the field name as a result in my answer table. I don't even know if this is possible, but any help would be greatly appreciated.
Thanks in advance!

 

[BobJacksonNCI]

Suggest looking at crosstab queries.
You can't use crosstab to make a table, but it might be the easiest way to get the data and manually copy/paste.

HTH,
Bob
Your mileage may vary, but following the guidelines in faq181-2886 will help you reach your goal.