Help with logic/algorithm for Excel Data table

[davyre]

Hi,

In Access I have query to display top 5 units sold each year. I put those in a recordset, and I want it to be placed in the excel file that I wanted.
The problem is the structure of a recordset is different with data table in Excel that I need. It is being used as data source for Bar Graph.
As we know, tables/recordset in Access has structure like this:

|Year   |Unit      |QuantitySold|
|2010   |A         |300         |
|2010   |D         |250         |
|2010   |E         |210         |
|2010   |G         |200         |
|2010   |I         |195         |
|2011   |A         |340         |
...
|2013   |H         |210         |

The data table structure that I need in Excel is like this:

|Year   |A     |D     |E     |G     |...
|2010   |300   |250   |210   |200   |...
|2011   |340   |      |      |      |...
...

How do I do this (changing the structure of the table) in VBA?

Here is the query to get the first table(Access)

Top5UnitQry = "SELECT COUNT(OU.UnitID) AS [Number], U.UnitName, YEAR(O.CustOrderDate) AS [Year] " _
            & "FROM TblUnits U INNER JOIN (TblCustOrder O INNER JOIN TblCustOrderUnit OU ON O.CustOrderID = OU.OrderID) ON U.UnitID = OU.UnitID " _
            & "GROUP BY YEAR(O.CustOrderDate), U.UnitName, OU.UnitID " _
            & "HAVING Format(COUNT(OU.UnitID),'0000000') & OU.UnitID IN (SELECT TOP 5 Format(COUNT(*),'0000000') & B.UnitID " _
            & "FROM TblCustOrder A INNER JOIN TblCustOrderUnit B ON A.CustOrderID = B.OrderID " _
            & "WHERE Year(A.CustOrderDate) = Year(O.CustOrderDate) " _
            & "GROUP BY B.UnitID ORDER BY 1 DESC) " _
            & "ORDER BY 3, 1 DESC "

 

[SkipVought]

Hi

Use a PivotTable

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[davyre]

ah great! Not familiar with PivotTable but it solves my prob! THanks!