ms-accessstackfieldstacked

stacking multiple Access fields into a label & a data field

I have a table that looks like this: oldTab I'd like to create a table that looks like this: newTab

I can run only the first line of this query: -

INSERT INTO newTab (PairID, Code) SELECT PairID, MaleCode FROM oldTab

INSERT INTO newTab (PairID, Code) SELECT PairID, FemaleCode FROM oldTab

With the addition of the second giving "Syntax error in FROM clause."

I suspect I'm missing some assumed, basic SQL syntax, as am very much a beginner. Can anyone suggest what I'm doing wrong or missing out?

Many thanks :-)

Solution

  • With Access SQL, you need two queries.

    First execute:

    INSERT INTO newTab (PairID, Code) SELECT PairID, MaleCode FROM oldTab

    Next, execute:

    INSERT INTO newTab (PairID, Code) SELECT PairID, FemaleCode FROM oldTab