adding new rows

J

jacobe2008

I have a table that has 2 fields with the following data

field1 field2
department1 a, b, c
department2 d, e, f

I would like to rearrange the data and create a table that is set up like the
following:

field1 field2
department1 a
department1 b
department1 c
department2 d
department2 e
department2 f

Any clue?
 
J

Jerry Whittle

Export the data into a CSV text file. Make the delimiter a comma and make
sure that the Text Qualifier is set to none.

Then reimport this data into a new table name which I called Departments2.
After importing open the table and see how many columns there are. If you are
lucky there should only be fields for the departments and three others for
the a, b, c, stuff.

Create a UNION ALL query like below. If you have a Field4, Field5, etc.,
you'll need to add more select statements.

SELECT Departments2.Field1, Departments2.Field2
FROM Departments2
UNION ALL
SELECT Departments2.Field1, Departments2.Field2
FROM Departments2
UNION ALL
SELECT Departments2.Field1, Departments2.Field3
FROM Departments2 ;

After it runs correctly, change it to a make table query:

SELECT Field1, Field2
INTO tblDepartmentsNew
FROM (SELECT Departments2.Field1, Departments2.Field2
FROM Departments2
UNION ALL
SELECT Departments2.Field1, Departments2.Field2
FROM Departments2
UNION ALL SELECT Departments2.Field1, Departments2.Field3
FROM Departments2);
 
J

jacobe2008

Worked like a charm. Thanks Jerry!

Jerry said:
Export the data into a CSV text file. Make the delimiter a comma and make
sure that the Text Qualifier is set to none.

Then reimport this data into a new table name which I called Departments2.
After importing open the table and see how many columns there are. If you are
lucky there should only be fields for the departments and three others for
the a, b, c, stuff.

Create a UNION ALL query like below. If you have a Field4, Field5, etc.,
you'll need to add more select statements.

SELECT Departments2.Field1, Departments2.Field2
FROM Departments2
UNION ALL
SELECT Departments2.Field1, Departments2.Field2
FROM Departments2
UNION ALL
SELECT Departments2.Field1, Departments2.Field3
FROM Departments2 ;

After it runs correctly, change it to a make table query:

SELECT Field1, Field2
INTO tblDepartmentsNew
FROM (SELECT Departments2.Field1, Departments2.Field2
FROM Departments2
UNION ALL
SELECT Departments2.Field1, Departments2.Field2
FROM Departments2
UNION ALL SELECT Departments2.Field1, Departments2.Field3
FROM Departments2);
I have a table that has 2 fields with the following data
Click to expand...
[quoted text clipped - 14 lines]
Any clue?
Click to expand...
Click to expand...
 
Top