Convert a Row to one column.

[vrk1]

Hi,

I have a query that outputs results as follows:

Expr1 Expr2 Expr3 Expr4 Expr5
1 2 3 4 5


I want to write another query to output the above result as follows:

Expr
1
2
3
4
5

Does anyone know how to do this please? Thanks!

 

[vrk1]

I tried a UNION query as follows:

select expr1 from qry1 UNION
select expr2 from qry2 UNION
select expr3 from qry3 UNION
select expr4 from qry4 UNION
select expr5 from qry5

This only works if all the expr values are different. If 2 or more expr have the same value they just show up once. Also, if the expr value is 0, they just show up once.

I want all the values shown under one column. Any one know how?

 

[pbaldy]

Try UNION ALL instead of UNION.

 

[vrk1]

that worked. thank you!

 

[vrk1]

Query is too complex Error

When I run the above query now, I get the error "Query is too complex" due to the multiple Union statements that I made (I think).

Any ideas now to resolve this problem?

 

[The_Doc_Man]

Part of this is that you are trying to run what is essentially a spreadsheet function. Access is not a spreadsheet.

If you were trying to end your process with a query, I would suggest that there is another way to do this by taking it one more step. Using the forms wizard, build a form that displays the items vertically arranged.

 

[vrk1]

Thanks for your response.

I am feeding the output of this query to a Piechart in Access. This only takes the data as one series.

Part of this problem arises due to the fact that I do not know how to create Cross Tab query in this situation. However, I will let you be the judge.

I have a query that outputs results like this:

Expr1Expr2Expr3
123

I want the query to show the output like this:
Expr
1
2
3

and I don't know how to do this without using the Union query as above. Your suggestion to take this into a form - I am not sure if I understood what you meant.

 

[The_Doc_Man]

As a not-so-quick and extremely-dirty method...

Analyze the query with Excel.

Then use the Excel TRANSPOSE function.

Then graph it with Excel.

 

[Bat17]

you could use code to loop through the field and store the data in a temp table. should be fast enough.

something like this

Dim rs As DAO.Recordset
Dim rsNew As DAO.Recordset
Dim j As Integer

DoCmd.RunSQL "DELETE * FROM tblTest" ' clear temp table
Set rs = CurrentDb().OpenRecordset("SELECT * FROM [YourQuery] ;", dbOpenSnapshot)
Set rsNew = CurrentDb.OpenRecordset("tblTest", dbOpenDynaset)
With rsNew
    For j = 0 To 3 ' or how ever many you need
        .AddNew
        !YourField = rs.Fields(j).Value
        .Update
    Next j
End With
Set rs = Nothing
Set rsNew = Nothing

HTH

Peter

 

[vrk1]

I did something like this just before you posted and it worked. Your code looks much neater with a For Loop. I will make use of this.

Thank you sharing a code sample!

 

[Bat17]

glad I could be of help