how to join (or append) 4 fields of the same table into 1 field in MS Access

Question

I have the following table:

Source Plate1   Plate2   Plate3   Plate4

which I want to transform to this table:

Source Plate1
Source plate2
source Plate3
source Plate4

so basically appending all the data from the 4 Plate fields into 1 column

How do I do this in ms acces?

thanks

Answer 1

You can use a Union query, for example:

INSERT INTO NewTable (Source, Plate)
SELECT Source, Plate FROM
  (SELECT Source, Plate1 As Plate FROM Table
   UNION ALL 
   SELECT Source, Plate2 As Plate FROM Table
   UNION ALL 
   SELECT Source, Plate3 As Plate FROM Table
   UNION ALL 
   SELECT Source, Plate4 As Plate FROM Table) As t

EDIT: the Union part by itself:

   SELECT Source, Plate1 As Plate FROM Table
   UNION ALL 
   SELECT Source, Plate2 As Plate FROM Table
   UNION ALL 
   SELECT Source, Plate3 As Plate FROM Table
   UNION ALL 
   SELECT Source, Plate4 As Plate FROM Table

Or

 SELECT Source, Plate FROM 
  (SELECT Source, Plate1 As Plate FROM Table
   UNION ALL 
   SELECT Source, Plate2 As Plate FROM Table
   UNION ALL 
   SELECT Source, Plate3 As Plate FROM Table
   UNION ALL 
   SELECT Source, Plate4 As Plate FROM Table) As s