Split data in one column and convert them into column headers in MS Access

I’m trying to convert a column of data into headers on a new table. This question has a good answer but the SQL doesn’t work in Access, I get a syntax error on the very first part of the SELECT statement. Here’s the code, with example names:

comp_name,
nvl(data1,0)data1,
nvl(data2,0)data2,
nvl(data3,0)data3,
nvl(data4,0)data4
from
main_table 
PIVOT
(
sum(amount) for (code) in (
'data1' as data1,
'data2' as data2,
'data3' as data3,
'data4' as data4
) 
)

Should this code work or is it not compatible with Access? How would I achieve something like this in Access?

Answer

Use conditional aggregation instead:

select comp_name,
       sum(iif(code = 'data1', amount, 0)) as data1,
       sum(iif(code = 'data2', amount, 0)) as data2,
       sum(iif(code = 'data3', amount, 0)) as data3,
       sum(iif(code = 'data4', amount, 0)) as data4
from main_table 
group by comp_name;