Solved: how can I transpose a column to a row?

[ironj32]

I need to transform an Access Table with 1 row w/ many columns into 1 column with many rows. Does anyone have suggestions for me? I put an example below. You help is greatly appreciated!


This:
Y N N Y Y N

Needs to look like this:
Y
N
N
Y
Y
N

[OBP]

What do you need to do with the data afterwards?
Display them?
What you have suggests that your Table design is not optomised and that those Y N s should be in a separate table in separate records.

[ironj32]

You are correct...the table I have is not optomised for my needs at all! Unfortunately, I have don't have the ability to change that one.

Once I have the columns moved into rows I am thinking I will create a new table. Once the new table is created I will run a match query so I can export a report that I need.

Thanks!

[OBP]

Well I would create a Table now with this type of structure
CurrentTablesRecordID - Type Number
TypeOfResponse - Type text
Response - Type Tick Box

You can then run a set of queries for each of your current Check Box Fields that will append the data to your new table.
The current Tables recordID would go in the CurrentTablesRecordID field.
The Current Field Name would go in the TypeOfResponse field
The Response would be the current Y or N

When you have finished running the queries you will have a completely restructred table that you can link Via CurrentTablesRecordID to the original table.

You can do the same thing using a VBA Recordset instead of Queries, but with queries you can check each stage.

[ironj32]

Thanks for you help OBP. However, I will admit that I'm pretty confused (I'm pretty novice with this stuff), so I'm not sure if what you described will provide the result I need. I attached a photo of exactly how the information will look, and needs to look. Notice that one of the columns is blank in the current table, and I would like to not move that one to the new table.

Again, thanks for your help!

[OBP]

iron, here is how the queries work, I have created one for each column you can just ignore the blank column and not create or run the Query.
I have run the queries so that you can see the data. You can just delete the data in the NewTable and run them again to see them working.

[ironj32]

Thanks for the example OBP! It pointed me in the right direction, and I think I should be all set now.