Creating A Query Using Column Headings as First Column Data

[grantwilliams]

Hi,

I'm trying to create a query that will display its results vertically. I currently have a table with a RegisterID column and then a column for each year back to 1965. i.e.

RegisterID   2004   2003   2002  ........  1965
    1         X      X      X               X
    2         X      X      X               X

where X represents the data stored.

I would like to put a query together which outputs the year and data for a particular RegisterID. i.e. For RegisterID = 1, I would get the following result:

Year    Team
2004     X
2003     X
2002     X
.
.
.
1965     X

Is there a way of doing this?

Thanks,

Grant

 

[JonFer]

If you can program in VBA you can do it fairly easily. I believe you'll find examples if you search the posts.

If not, there is another method using an additional table but it gets messy with that many columns. Is the data for each year numeric or text?

 

[grantwilliams]

The data is text and numeric... the database is for a sporting club, where teams are assigned a number for the Age Group and a letter for the Division e.g. 11A

The query itself doesn't need to be in this form, but I would like it to diplay like this in a text box (or similar) on a form.