0

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.

June7
  • 19,874
  • 8
  • 24
  • 34
DCUFan7
  • 385
  • 1
  • 3
  • 10

1 Answers1

3

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.

June7
  • 19,874
  • 8
  • 24
  • 34
  • If I pseudocode this so that I'm understanding it right: Select [id fields], [First Field to Melt] as Value, "First Field to Melt" as First from tablename Union Select [id fields], [Second Field to Melt], "Second Field to Melt" from tablename (etc. for other fields to melt) Is this correct? – DCUFan7 May 21 '20 at 18:17
  • That does seem to follow my suggestion, except for `First` alias name. First SELECT line defines data type and field names. – June7 May 21 '20 at 18:23
  • Works, also I should note that Access seemed to not like that I had the word "Row" as a column name, and I had to change it. – DCUFan7 May 21 '20 at 18:36
  • 1
    I would not have expected that to be a reserved word but apparently it is. Review http://allenbrowne.com/AppIssueBadWord.html – June7 May 21 '20 at 18:42