TRANSFORM and PIVOT in Access 2013 SQL

Question

How can I get second table from first table using TRANSFORM and PIVOT functions:

TABLE_01

Config_ID  |  ConfigField  |  ConfigValue
-----------------------------------------
       11  |         Name  |        Basic
       11  |      Version  |         1.01
       11  |        Owner  |         Jack
       12  |         Name  |     Advanced
       12  |      Version  |         1.03
       12  |        Owner  |         Andy

TABLE_02

Config_ID  |      Name  |  Version  |  Owner
--------------------------------------------
       11  |     Basic  |     1.01  |  Jack
       12  |  Advanced  |     1.03  |  Andy

I'm trying this:

TRANSFORM ConfigValue
SELECT Config_ID
FROM TABLE_01
GROUP BY Config_ID
PIVOT ConfigField

but got an error:

"Your query does not include the specified expression 'ACValue' as part of aggregate function."

score 49 · Answer 1 · answered May 22 '13 at 13:30

49

It looks like you are missing the aggregate function in the TRANSFORM:

TRANSFORM Max(ConfigValue)
SELECT Config_ID
FROM TABLE_01
GROUP BY Config_ID
PIVOT ConfigField

answered May 22 '13 at 13:30

Taryn

242,637
56
362
405

score -9 · Accepted Answer · answered May 31 '13 at 06:42

-9

I have found solution by myself:

TRANSFORM FIRST(ConfigValue)
SELECT Config_ID
FROM TABLE_01
GROUP BY Config_ID
PIVOT ConfigField

Thanks everyone for help.

answered May 31 '13 at 06:42

Viktor Krykun

243
1
2
9

28

I've no idea why you posted this and accepted it instead of the answer that told you that you were missing an aggregate function 9 days previously. – Martin Smith Feb 11 '17 at 15:23

TRANSFORM and PIVOT in Access 2013 SQL

2 Answers2

Linked