2

I need some help with converting an MS Access table with data in Rows into Columns. Created some sample data table that suits my scenario. I need to convert "Table 1" to "Table 3". If possible using the "Table 2" to identify the column order of the Fruits. enter image description here

I think this can be done using Pivot option in SQL Server. How can I achieve the same in Access Database?

Thanks.

June7
  • 19,874
  • 8
  • 24
  • 34
Ravi
  • 55
  • 5

2 Answers2

1

Column Order is dictated by value in the column header. Could concatenate Fruit and Order fields:
Order & "-" & Fruit AS OrderFruit.

Use query designer to build query that joins Table1 and Table2 on Fruit fields with that calculated field and switch to CROSSTAB to complete.

June7
  • 19,874
  • 8
  • 24
  • 34
1

Consider MS Access' crosstab query which uses TRANSFORM and PIVOT clauses. The IN list in PIVOT specifies the order and appearance (omit or not) of columns.

TRANSFORM MAX(t1.Export) AS MaxExport
SELECT t1.Country
FROM table1 t1
GROUP BY t1.Country
PIVOT t1.Fruit IN ('Apple', 'Kiwi', 'Mango', 'Grapes', 'Pear')

For needed order with number period column headers, simply join the second table and concatenate Order and Fruit columns in PIVOT clause:

TRANSFORM MAX(t1.Export) AS MaxExport
SELECT t1.Country
FROM table1 t1
INNER JOIN table2 t2
   t1.Fruit = t2.Fruit
GROUP BY t1.Country
PIVOT t2.[Order] & "." & t1.Fruit
Parfait
  • 104,375
  • 17
  • 94
  • 125
  • Thanks Parfait. It did work. I just need to join the Table 2 to dictate the order. The original data has a long list of questions which will be transformed into columns. I replaced the Order column with FruitOrder with the Order number concatenated at first (e.g - "1.Apple") Any idea how to join and make this might work? TRANSFORM MAX(t1.Export) AS MaxExport SELECT t1.Country FROM table1 t1 INNER JOIN Table2 t2 ON t1.Fruit = t2.Fruit; GROUP BY t1.Country PIVOT t2.FruitOrder – Ravi Jun 15 '20 at 02:29
  • And what went wrong with your attempted query? See extended answer to concatenate two columns in `PIVOT`? – Parfait Jun 15 '20 at 16:32