Access - Tranposing rows into columns?

In the data below there could be many more Salesman. Is there away to transpose this data into the one that follows? And how could one run an Access report on the transposed data?

Item      Salesman      Sales      Orders
1220      Sue                    $390       29
1220      Fred            $590      40
3030      Gary            $300      20
3399      Sue                    $400      40
3399      Fred            $289      20
3399      Andy            $290      10

             **Sue                    **Fred                    **Gary                    **Andy
Item      Sales      Orders      Sales      Orders      Sales      Orders      Sales      Orders
1220      $390      29           $590      40      
3030                                                              $300      20
3399      $400      40      $289      20                                                   $290      10
Who is Participating?

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

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.

Rey Obrero (Capricorn1)Commented:
try using the Query wizard

Create > Query wizard > crosstab query wizard
Jess31Author Commented:
For starters crosstab query wizard only allows me to select 3 columns and in my example I show 4 columns.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Note that Crosstabs have to be re-run if you add more "columns" - so if you add more Salesmen, you'd have to recreate the crosstab. You'll also have troubles with those "columns", since you essentially have two distinct sets of data (Sales and Orders) for each Salesman. You'd very likely have to include a non-joined table, and work with the query SQL to create it correctly. Allen Browne has a good tutorial on this:

If that doesn't work you can always use the Temporary Table method. This involves creating a table that looks like your desired results, and then using VBA to fill the rows. So in your case, you'd have a table that looked like this:

etc etc

You'd then add Rows to that table for each "Item", so something like this:

Dim amt As Double
Dim orders as Single
Dim rst As DAO.Recordset
Set rst = Currentdb.OpenRecordset("SELECT Item FROM SomeTable")
Dim rstS as DAO.Recordset
Set rstS = Currentdb.OpenRecordset("SELECT SalesRep FROM SomeTable")
Dim header as string
header = "INSERT INTO YourTempTable(Item, S1_Amt, S1_Orders, S2_Amt, S2, Orders, etc etc)"
Dim detail as string
'/ Loop through each Item
Do Until rst.EOF
  '/ Loop through each SalesRep
  Do Until rstS.EOF
    '/ for each SalesRep, get the Amt and OrderQty for each Item
    amt = Nz(DSUM("OrderQty", "YourOrdersTable", "Item='" & rst("Item") & "' AND SalesRep='" & rstS("SalesRep") & "'"), 0)
    orders = Nz(DSUM("Amount", "YourOrdersTable", "Item='" & rst("Item") & "' AND SalesRep='" & rstS("SalesRep") & "'"), 0)
    detail =     sql = sql & "," & amt & "," & orders     
    '/ Get the next Sales Rep
  '/ now write that row to the temporary table
  '/ strip off the leading comma
  sql = Right(sql, Len(sql)-1)
  sql = sql & " VALUES(" & detail & ")"
  Currentdb.Execute sql
  '/ Get the next Item

Open in new window

This is air-code, but it shows how to do loops and nested loops, and how to write to your table.
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
For starters crosstab query wizard only allows me to select 3 columns and in my example I show 4 columns.
You can select more than 3 columns in the Wizard ... but you're showing 8 columns, not 4.

Note too that using the wizard assumes your data is normalized. If it's not, then all bets are off, and you'll almost certainly have to use the temporary table method described above.
Dale FyeOwner, Developing Solutions LLCCommented:
Actually, you can generate something similar to that with a cross-tab query, but your data is not properly to do it.

To start with, create a normalizing query to get the data into a format which we can use in the CrossTab query

SELECT Item, Salesman, "Sales" as Action, [Sales] as Amount
FROM yourTable
SELECT Item, Salesman, "Orders" as Action, [Orders]
FROM yourTable

Then, with the data formatted this way, create a new cross-tab query that uses the [Item] column as the RowHeader,
concatenates the [Salesman] and [Action] columns as the Column Header, and sums the [Amount] column as the Value.  That query would look like:

TRANSFORM Sum(qryNormalizedData.Amount) AS SumOfAmount
FROM qryNormalizedData
PIVOT [Salesman] & " | " & [Action];

This would give you results that look like:crosstabThe downside of this is that the column headers are in alphabetical order, so if that is OK, then you have your output, which you could then send to Excel or to a dynamic report based on a crosstab query.  The dynamic report based on a crosstab can be a challenge, take a look at the solutions provided in this other EE link on a similar topic

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
Jeffrey CoachmanMIS LiasonCommented:
Also note that you are looking for a bit more than a true transposition.
A true transposition literally just swaps the rows for the columns
So your original data:
t1...would end up looking like this, (literally transposed)
For starters crosstab query wizard only allows me to select 3 columns and in my example I show 4 columns.
Correct.  Once the wizard has built the basic query, open it in design view and add as many "Row Heading" columns as you want.  Just make sure to set the value in the crosstab row to "Row Heading" or you won't see them.

The wizards are a simple way of building basic objects.  In all cases, you can modify the object after the wizard is finished and embellish it as needed.  The objects are not tied to the wizards and you can't rerun a wizard on an existing object.  They only build new objects.
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.