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.
SteveL13Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.