Create a new table based on existing table

Question

I'm trying to write a query in Access 2003 that creates a new table based on the existing one. I'm using a Make Table Query.

Before:

NAME  A_1   A_2  B_1    B_2
X      1    2    3      4
Y      5    6    7      8

AFTER

NAME    Section Value1  Value2  
X          A    1        2  
X          B    3        4  
Y          A    5        6  
Y          B    7        8  

Answer 1

Create a SELECT query which retrieves the A_1 and A_2 values from each row of your existing table:

SELECT e1.NAME, 'A' AS [Section], e1.A_1 AS Value1, e1.A_2 AS Value2
FROM tblExisting AS e1

Then create a similar query which retrieves the B_1 and B_2 values, and UNION the two SELECT statements:

SELECT e1.NAME, 'A' AS [Section], e1.A_1 AS Value1, e1.A_2 AS Value2
FROM tblExisting AS e1
UNION ALL
SELECT e2.NAME, 'B' AS [Section], e2.B_1 AS Value1, e2.B_2 AS Value2
FROM tblExisting AS e2

From there, you can build a make table query based on the UNION query:

SELECT sub.*
    INTO tblNew
FROM
    (
        SELECT e1.NAME, 'A' AS [Section], e1.A_1 AS Value1, e1.A_2 AS Value2
        FROM tblExisting AS e1
        UNION ALL
        SELECT e2.NAME, 'B' AS [Section], e2.B_1 AS Value1, e2.B_2 AS Value2
        FROM tblExisting AS e2
    ) AS sub;