CODEWITHSUNDEEP
excelvbams-access
Avagut
Avagut

Reputation: 994

Switch Row Data into column headers

I have as below, an excel worksheet with data arranged in rows with currently with 3 columns having annual data year by year , a column describing the type of data (turnover or sales returns etc) and a product name.

Can anyone please assist me with some advice on how I can load this into an MS Access table with a Product Column, a Year Column and a Data Description Column. Or if you could recommend a better way to manage this data. More data will be loaded for both previous years and future years. 

2010       2009       2008     Description   Product Name
---------  ---------  -------  ------------  -------------
1,937,190  1,719,600  816,633  Turnover      Product Name1
  791,788    599,117  743,114  Sales Return  Product Name1
   13,601  1,719,600  816,633  Turnover      Product Name2
  791,788    599,117  743,114  Sales Return  Product Name2

Upvotes: 0

Views: 133

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123829

You can create a Linked Table in Access that will read the raw Excel data.

Import or link to data in an Excel workbook

Assuming that your Linked Table is named [ExcelData] 

2010     2009     2008    Description    Product Name 
-------  -------  ------  -------------  -------------
1937190  1719600  816633  Turnover       Product Name1
 791788   599117  743114  Sales Return   Product Name1
  13601  1719600  816633  Turnover       Product Name2
 791788   599117  743114  Sales Return   Product Name2

you could create the following saved query named [qryExcelDataUnpivoted] in Access:

SELECT [Product Name], 2008 AS Yr, [Description], [2008] AS Amount
FROM ExcelData
UNION ALL
SELECT [Product Name], 2009 AS Yr, Description, [2009] AS Amount
FROM ExcelData
UNION ALL
SELECT [Product Name], 2010 AS Yr, Description, [2010] AS Amount
FROM ExcelData

returning

Product Name   Yr    Description    Amount 
-------------  ----  -------------  -------
Product Name1  2008  Turnover        816633
Product Name1  2008  Sales Return    743114
Product Name2  2008  Turnover        816633
Product Name2  2008  Sales Return    743114
Product Name1  2009  Turnover       1719600
Product Name1  2009  Sales Return    599117
Product Name2  2009  Turnover       1719600
Product Name2  2009  Sales Return    599117
Product Name1  2010  Turnover       1937190
Product Name1  2010  Sales Return    791788
Product Name2  2010  Turnover         13601
Product Name2  2010  Sales Return    791788

Then, you could use that to create a crosstab query if you wanted a separate column for each [Description]

TRANSFORM SUM(Amount) AS SumOfAmount
SELECT Yr, [Product Name]
FROM qryExcelDataUnpivoted
GROUP BY Yr, [Product Name]
PIVOT Description

returning

Yr    Product Name   Sales Return  Turnover
----  -------------  ------------  --------
2008  Product Name1        743114    816633
2008  Product Name2        743114    816633
2009  Product Name1        599117   1719600
2009  Product Name2        599117   1719600
2010  Product Name1        791788   1937190
2010  Product Name2        791788     13601

Upvotes: 1

Related Questions