Access - Change Column Entries into Rows

Trying this again - with more understanding of the process and what works best for the experts.

I have a table, two columns per name, Column A is the name field, and column B is the test completed. There can be 1 or more row per name, but this does vary. The number of Tests indicated in Column B can also vary.

Example:
             Col A/B                          Col C
Row 1: F/L Name 1                  Test 1
Row 2: F/L Name 1                  Test 2
Row 3: F/L Name 2                  Test 1
Row 3 F/L Name 2                   Test 3
Row 4: F/L Name 2                  Test 4

Please offer how I can change this format so that there is only one row per name, where Column B is turned into multiple columns depending on the # of tests. In the example above

                         Col A/B                  Column C           Column D        Column E       Column F
Row 1 is now  F/L Name 1:            Test 1                   Test 2
Row 2 is now  F/L Name 2:           Test 1                                              Test 3              Test 4

I have tried variations of transpose without success.

Hope this makes sense to you.

Thank you.
exp vgAsked:
Who is Participating?
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.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I'd suggest you try again. You have two "Row 3" value in your first example, and you don't explain what "Column C" is in that example.
0
PatHartmanCommented:
The ColC values are what will become the column headings on the crosstab.  You have those showing as Column C, column D, etc.  What do you want as the intersection data (where you currently have test 1, test 2, etc)?

Your example only has two columns.  The recordset for a crosstab requires at least three columns to go into the three sections - Row Heading (the col a/b values), Column Heading ( the col c values), Value (min, max, etc of the third column). We may need to see some real data and you will need to tell us where it is wrong.  

You may have to create a dummy third column.  I would use a value of 1.  That way you could sum the value and end up with a count of the times test 1 was run for a given col A/B value.
0

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
exp vgAuthor Commented:
Row 3 was included twice unintentionally.

Column A is First Name
Column B is Last Name

Column C is for example types of school exams
Test 1 - Math Test
Test 2 - Spelling Test
Test 3 - Chemistry Test
Test 4 - History Test


      Col A/B                                                   Col C
Row 1: F/L Name 1 (John/Doe)               Test 1  (Math Test)
Row 2: F/L Name 1 (John/Doe)                Test 2 (Spelling Test)
Row 3: F/L Name 2 (Jane/Doe)                Test 1  (Math Test)
Row 4 F/L Name 2  (Jane/Doe)                 Test 3 (Chemistry Test)
Row 5: F/L Name 2  (Jane/Doe)                Test 4 (History Test)

ColA ColB                  Column C           Column D        Column E             Column F
John Doe:                  Math Test       Spelling Test
Jane Doe:                   Math Test                                  Chemistry Test      History Test

Hope this helps
0
exp vgAuthor Commented:
And, for now - I do this manually - it is the only way I know how.
0
exp vgAuthor Commented:
This worked beautifully as a cross tab once I had the chance to try it. Thanks so much.
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.

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.