Transpose Rows to Columns in Access Query

[Compiling and Confused]

Hello,

I am trying to transpose multiple columns into a specified column as shown
below.

What it looks like:

1(Q1_A_1) 1(Q1_1-2) 1(Q1_A_3) 1(Q1_B_1) 1(Q1_B_2) 1(Q1_B_3)
Low High - 1 Yr. Now
2 Yrs.

What it needs to look like:

Risk Readiness
Low 1 Yr.
High Now
- 2 Yrs.

I am hoping there is a simple expression that I can type in the 'field' row
of the query where I can just call out the fields I want to inlcude in that
one column on seperate rows. Also I am dealing with over 100 fields.

Thanks,
Doug

 

[Compiling and Confused]

Sorry I forgot to mention that I will have various spreadsheets similar and
more complex and the thought of building make tables and append queries is
overwhelming. Any other feedback would be awesome!

 

[John W. Vinson]

Hello,

I am trying to transpose multiple columns into a specified column as shown
below.

What it looks like:

1(Q1_A_1) 1(Q1_1-2) 1(Q1_A_3) 1(Q1_B_1) 1(Q1_B_2) 1(Q1_B_3)
Low High - 1 Yr. Now
2 Yrs.

What it needs to look like:

Risk Readiness
Low 1 Yr.
High Now
- 2 Yrs.

I am hoping there is a simple expression that I can type in the 'field' row
of the query where I can just call out the fields I want to inlcude in that
one column on seperate rows. Also I am dealing with over 100 fields.

Thanks,
Doug

I think word wrap made a real hash of this! I don't understand what it looks
like now. Could you repost in the syntax

Fieldname: Value
Fieldname: Value

Going from spreadsheet to relational database can be a real pain, since the
logical structure is so different. I'm GUESSING that your fieldnames encode
quarters and - what? years in the future? But what are the A and B?

 

[Compiling and Confused]

Hi John,

The fields are results within a survey tool that I am using to gather
succession information. Even more confusing is the fields' naming schemes.
I will try to simplify it for you below.

Column1 - Low
Column2 - Med
Column3 - High
Column4 - Now
Column5 - 1 Yr.
Column6 - 2 Yrs.
Column7 - 3 Yrs.

Need to look like this:

Risk Readiness
Candidate 1 Low Now
Candidate 2 Med 2 Yrs.
Candidate 3 High 3 Yrs.

I hope this helps.

 

[John W. Vinson]

Hi John,

The fields are results within a survey tool that I am using to gather
succession information. Even more confusing is the fields' naming schemes.
I will try to simplify it for you below.

Column1 - Low
Column2 - Med
Column3 - High
Column4 - Now
Column5 - 1 Yr.
Column6 - 2 Yrs.
Column7 - 3 Yrs.

Need to look like this:

Risk Readiness
Candidate 1 Low Now
Candidate 2 Med 2 Yrs.
Candidate 3 High 3 Yrs.

I hope this helps.

Well... that apparently oversimplified it. Where is the Candidate number field
in your data? You say you have over 100 fields (*very* spreadsheetish!); what
are some of the actual fieldnames in your Access table? How do you ascertain
from the seven rows above that Candidate1 has low risk and Now readiness?

 

[Compiling and Confused]

Hi John,
When I import the data from excel into Access I keep the generic titles that
are pulled from the survey because it is systematic. The fields are named
based on their location on the table question of the survey.

Example: 1 (Q1_A_1) = Section 1, Question 1, column A, Row 1

This data is pulled from a table question. Hence All data in row 1 of the
table question is associated with candidate 1... row 2 is candidate 2.. and
so forth. Unfortunately I don't have the ease of pulling the suvey data with
the same table question formatting as it is on the survey tool into Excel.
Below is the data as it is pulled directly into a spreadsheet which I then
import into an access database.

Column 1: 1 (Q1_A_1) - John Doe
Column 2: 1 (Q1_A_2) - Jane Smith
Column 3: 1 (Q1_A_3) - Ben Barker
Column 4: 1 (Q1_A_4) - Don Juan
Column 5: 1 (Q1_B_1) - Low
Column 6: 1 (Q1_B_2) - Med
Column 7: 1 (Q1_B_3) - Low
Column 8: 1 (Q1_B_4) - High
Column 9: 1 (Q1_C_1) - Now
Column 10: 1 (Q1_C_2) - 1 Yr.
Column 11: 1 (Q1_C_3) - 1 Yr.
Column 12: 1 (Q1_C_4) - 3 Yrs.

With the explanation above about how information for each candidate is
ascertained, I would like for the data to look like this in the query (This
is how the data looks in the table question):

Candidates Risk Readiness
John Doe Low Now
Jane Smith Med 1 Yr.
Ben Barker Low 1 Yr.
Don Juan High 3 Yrs.

I hope this is a little clearer.

 

[John W. Vinson]

Hi John,
When I import the data from excel into Access I keep the generic titles that
are pulled from the survey because it is systematic. The fields are named
based on their location on the table question of the survey.

Example: 1 (Q1_A_1) = Section 1, Question 1, column A, Row 1

This data is pulled from a table question. Hence All data in row 1 of the
table question is associated with candidate 1... row 2 is candidate 2.. and
so forth. Unfortunately I don't have the ease of pulling the suvey data with
the same table question formatting as it is on the survey tool into Excel.
Below is the data as it is pulled directly into a spreadsheet which I then
import into an access database.

Column 1: 1 (Q1_A_1) - John Doe
Column 2: 1 (Q1_A_2) - Jane Smith
Column 3: 1 (Q1_A_3) - Ben Barker
Column 4: 1 (Q1_A_4) - Don Juan
Column 5: 1 (Q1_B_1) - Low
Column 6: 1 (Q1_B_2) - Med
Column 7: 1 (Q1_B_3) - Low
Column 8: 1 (Q1_B_4) - High
Column 9: 1 (Q1_C_1) - Now
Column 10: 1 (Q1_C_2) - 1 Yr.
Column 11: 1 (Q1_C_3) - 1 Yr.
Column 12: 1 (Q1_C_4) - 3 Yrs.

With the explanation above about how information for each candidate is
ascertained, I would like for the data to look like this in the query (This
is how the data looks in the table question):

Candidates Risk Readiness
John Doe Low Now
Jane Smith Med 1 Yr.
Ben Barker Low 1 Yr.
Don Juan High 3 Yrs.

I hope this is a little clearer.

Much. That's got to be the wierdest layout for questionnaires that I've ever
seen. I presume that one time there might be four rows, the next time eleven?

If it's JUST as you describe a Normalizing Union query will do the job. I'll
assume that the first row of the spreadsheet has your Q names and that you
have used them as the fieldnames for a (linked or imported, it shouldn't
matter) temporary table, which I'll call tblTemp.

You will need to go into the SQL window of a new Query, and edit or copy and
paste:

SELECT [Q1_A_1] AS Candidate, [Q1_B_1] AS Risk, [Q1_C_1] AS Readiness
FROM tblTemp
WHERE [Q1_A_1] IS NOT NULL
UNION ALL
SELECT [Q1_A_2] AS Candidate, [Q1_B_2] AS Risk, [Q1_C_2] AS Readiness
FROM tblTemp
WHERE [Q1_A_2] IS NOT NULL
UNION ALL
SELECT [Q1_A_3] AS Candidate, [Q1_B_3] AS Risk, [Q1_C_3] AS Readiness
FROM tblTemp
WHERE [Q1_A_3] IS NOT NULL
UNION ALL
SELECT [Q1_A_4] AS Candidate, [Q1_B_4] AS Risk, [Q1_C_4] AS Readiness
FROM tblTemp
WHERE [Q1_A_4] IS NOT NULL


This query will "unravel" the spreadsheet into a tall-thin table. I'm sure you
can extend the logic to more columns as needed.

You can then base a MakeTable or (better) Append query - into a predefined
normalized empty table - on this stored UNION query. Running it will pull the
data out of the spreadsheet into the new table.

 

[Compiling and Confused]

John, thanks for you help, I got the just of the coding that I needed and
actually was able to make up the rest to get it to do what I needed it to. I
really appreciate your feedback. This is an attempt of our company to use a
survey as an interim tool to gather succession data before we implement ERP.
Yes it's got to be the worst format we can get information in and requires
customized surveys for each position identified to succession planning. But
it is pretty on the front end, which is what matters to our execs.

This was my first time writing a code for a union query and I believe this
will help my overall process of translating survey succession data into a
meaningful report in Access. Thanks again!

Hi John,
When I import the data from excel into Access I keep the generic titles that
are pulled from the survey because it is systematic. The fields are named
based on their location on the table question of the survey.

Example: 1 (Q1_A_1) = Section 1, Question 1, column A, Row 1

This data is pulled from a table question. Hence All data in row 1 of the
table question is associated with candidate 1... row 2 is candidate 2.. and
so forth. Unfortunately I don't have the ease of pulling the suvey data with
the same table question formatting as it is on the survey tool into Excel.
Below is the data as it is pulled directly into a spreadsheet which I then
import into an access database.

Column 1: 1 (Q1_A_1) - John Doe
Column 2: 1 (Q1_A_2) - Jane Smith
Column 3: 1 (Q1_A_3) - Ben Barker
Column 4: 1 (Q1_A_4) - Don Juan
Column 5: 1 (Q1_B_1) - Low
Column 6: 1 (Q1_B_2) - Med
Column 7: 1 (Q1_B_3) - Low
Column 8: 1 (Q1_B_4) - High
Column 9: 1 (Q1_C_1) - Now
Column 10: 1 (Q1_C_2) - 1 Yr.
Column 11: 1 (Q1_C_3) - 1 Yr.
Column 12: 1 (Q1_C_4) - 3 Yrs.

With the explanation above about how information for each candidate is
ascertained, I would like for the data to look like this in the query (This
is how the data looks in the table question):

Candidates Risk Readiness
John Doe Low Now
Jane Smith Med 1 Yr.
Ben Barker Low 1 Yr.
Don Juan High 3 Yrs.

I hope this is a little clearer.

Much. That's got to be the wierdest layout for questionnaires that I've ever
seen. I presume that one time there might be four rows, the next time eleven?

If it's JUST as you describe a Normalizing Union query will do the job. I'll
assume that the first row of the spreadsheet has your Q names and that you
have used them as the fieldnames for a (linked or imported, it shouldn't
matter) temporary table, which I'll call tblTemp.

You will need to go into the SQL window of a new Query, and edit or copy and
paste:

SELECT [Q1_A_1] AS Candidate, [Q1_B_1] AS Risk, [Q1_C_1] AS Readiness
FROM tblTemp
WHERE [Q1_A_1] IS NOT NULL
UNION ALL
SELECT [Q1_A_2] AS Candidate, [Q1_B_2] AS Risk, [Q1_C_2] AS Readiness
FROM tblTemp
WHERE [Q1_A_2] IS NOT NULL
UNION ALL
SELECT [Q1_A_3] AS Candidate, [Q1_B_3] AS Risk, [Q1_C_3] AS Readiness
FROM tblTemp
WHERE [Q1_A_3] IS NOT NULL
UNION ALL
SELECT [Q1_A_4] AS Candidate, [Q1_B_4] AS Risk, [Q1_C_4] AS Readiness
FROM tblTemp
WHERE [Q1_A_4] IS NOT NULL


This query will "unravel" the spreadsheet into a tall-thin table. I'm sure you
can extend the logic to more columns as needed.

You can then base a MakeTable or (better) Append query - into a predefined
normalized empty table - on this stored UNION query. Running it will pull the
data out of the spreadsheet into the new table.