MS Access version of a Python melt

Question

Assuming a table organized thus:

Row |  School | LocationCode2011 |  LocationCode2012 | LocationCode2013 

001      ABC        1000A                1000B                2000X
002      DEF        2000A                2000B                4000X

With the intent to change it to this:

Row |  School |    Location        | Value

001      ABC    LocationCode2011     1000A
001      ABC    LocationCode2012     1000B
001      ABC    LocationCode2013     2000X
002      DEF    LocationCode2011     2000A
002      DEF    LocationCode2012     2000B
002      DEF    LocationCode2013     4000X

Python is my preferred language but I need this to happen in MS Access. In Python I would do

df2 = df.melt(id_vars=["Row","School"], value_vars=["LocationCode2011", "LocationCode2012", "LocationCode2013""], var_name="Location",val_name="Value")

and receive a new table stored in df2. I've looked for documentation on "melting" in Access and so far and I don't know if Transform is quite what I need.

Answer 1

In Access, this rearrangement of data to normalized structure can be done with a UNION query.

SELECT Row, School, LocationCode2011 AS Value, "LocationCode2011" AS Location FROM tablename
UNION SELECT Row, School, LocationCode2012, "LocationCode2012" FROM tablename
UNION SELECT Row, School, LocationCode2013, "LocationCode2013" FROM tablename;

First SELECT line defines data type and field names. There is a limit of 50 SELECT lines.

Then if you want it committed to a new table, use this query as source for a SELECT INTO action SQL or if table already exists, an INSERT SELECT action.