Convert multiple rows to single column in Ms access

Question

I have multiple rows which i want to convert it into single column: For exa : Table1

ID      Field1         Field2        Field3
1         A              B            C
2         D              A           null
3         B             null         null

Result should be like below :

Field1
  A
  B
  C
  D
  A
  B

Please suggest how can we convert it into Ms access query or VBScript or any other way we can get the result in Ms access. Thanks in advance.

What happened to the ID values? A single column in the resultant table would be pretty meaningless. And why are the values A and B repeated? — Andy G
– Andy G, Commented Feb 26, 2018 at 10:41
@andy g Let me do it more meaningful , we don't have id field, only 3 fields are there and the values come repetitive that's fix. now we want to get the result which is mentioned above — A.Hemrajani
– A.Hemrajani, Commented Feb 26, 2018 at 12:46

ashleedawg · Accepted Answer · 2018-02-26 11:19:43Z

1

A UNION query combines results of other queries.

Select field1
From table1
Where field1 Is Not Null

UNION

Select field2 as field1
From table1
Where field2 Is Not Null

UNION

Select field3 as field1
From table1
Where field3 Is Not Null

More Information:

Microsoft.com : Use a union query to combine multiple queries into a single result
Wikipedia : Set Operations (SQL): UNION Operator

answered Feb 26, 2018 at 11:19

ashleedawg

21.9k9 gold badges82 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

A.Hemrajani Over a year ago

thanks for help , it works proper as u suggested , thanks a lot.

Collectives™ on Stack Overflow

Convert multiple rows to single column in Ms access

1 Answer 1

More Information:

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

More Information:

1 Comment

Related