• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 51
  • Last Modified:

Trying to transpose data in a query

I need to transpose data that is in a query I've created from an Excel file.  The query looks like this:

StartWith
And needs to end up looking like this:

EndUpWith
How can I do this?  Note:  I have no control over the original table as it is coming from an Excel file.
0
SteveL13
Asked:
SteveL13
  • 3
1 Solution
 
Mark EdwardsChief Technology OfficerCommented:
Steve:  Based on the info provided of needing to take the data from an Excel file that is displayed in an Access query, here's what I would have to do.  It's not the only solution, but the simplest:

Create a Select query for each ID/Code/Description set in your query and append (stack) then on top of one another with a UNION  or UNION ALL query.  Something like this:

[Query1] SQL:  SELECT [ID], [CODE#1], [DESCRIPTION#1] FROM MyQuery
[Query2] SQL:  SELECT [ID], [CODE#2], [DESCRIPTION#2] FROM MyQuery
etc....

Stack them with a UNION ALL query if you don't want to filter out duplicate records (same values as in another record), or with a UNION query if you DO want to filter out duplicates:

Query1
UNION ALL
Query2
UNION ALL
Query3....

Note:  You can't put an ORDER BY clause in the queries participating in a UNION query.  If you want to further process the results of the UNION query, you'll need to use it as a source in another Select query, where you can put filters and sort clauses in the query.

Hope this helps.
1
 
Mark EdwardsChief Technology OfficerCommented:
Sorry, should have put:

Query1.*
UNION ALL
Query2.*
UNION ALL
Query3.*
etc.......

You can use .* in the UNION query if the fields in a union query are all aligned (column1, column 2, column 3) are the same in each query.  Otherwise you'll need to specify the fields to use from each of the queries.
0
 
aikimarkCommented:
Depending on the number of columns, you might be able to use a single union query (as recommended by Mark Edwards).

What are the actual column names?
0
 
Mark EdwardsChief Technology OfficerCommented:
Steve:  I don't want to try and make this sound too confusing, but when I use the word "query", I'm also referring to Access SQL, not just a querydef container (query object in the Query nav pane.)  You can put multiple query SQL statements in a UNION query instead of a query name and .*
1
 
PatHartmanCommented:
I don't know if you posted this question on another forum or I just forgot to hit send when I answered it yesterday but since I don't see my answer, I'll post again.  

I did not suggest a union query because depending on how many queries you'll have inside it, you could end up with a "too complex" error.  I suggested simply creating however many append queries you need based on the number of sets of data and then creating a function that runs all the append queries.

If you go with the union and end up with a "too complex" error, just break the union and use a smaller set of tables.  You can also union a union.  So if you have 20 queries and that is too many, you can create two unions of 10 each and a third that unions the two unions and use that as the source for a single append query.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now