transpose data in access report

[Nancy]

Hi all. I have a database with 100 records. Each record has a dozen fields
that can have a numeric value of 1 to 10. I created a query which shows me
all records which have at least one field with a value less than 5 (field1<5
or field2<5 or field 3<5 etc.). The results of the query look like this (as
you might expect):

ID-Top-Lt-Rt
096--5--4--6
097--4--8--3
099--3--7--4

However, the users need a report which lists every field with a value less
than 5 on a separate line (not values of 5 or higher) along with the field
name/caption like so:

ID-Rating-Direction
096--4--Left
097--4--Top
097--3--Right
099--3--Top
099--4--Right

I am not sure how to achieve this. Any thoughts would be very much
appreciated.

Thanks!

 

[kingston via AccessMonster.com]

Create an SQL query like this:

SELECT ID, Top AS Rating, "Top" FROM Table
UNION SELECT ID, Lt AS Rating, "Left" FROM Table
UNION SELECT ID, Rt AS Rating, "Right" FROM Table
...

 

[Nancy]

Thanks very much. The Union query did solve the problem. Here's a related
question: I have 50 of these databases that all have multiple field names,
none of them the same (I inherited this mess). Is there a way to do this,
perhaps using VBA in a report, to automatically iterate thru the field names
using collections? Otherwise I am going to be handwriting SQL statements for
quite a while. Even so, this is much better than where I started, so thanks
again!

- Nancy