How can I convert a table with horizontal data to a table with vertical data ? (1 Viewer)

How can I convert a table with horizontal data to a table with vertical data ?

This example illustrates one row (Table 1) of data to many rows of data (Table 2).

Source
Table 1
Name Type Field1 Field2 Field3 Field4 Field5 Field6 Field7

Results
Table 2
Name Field1 Type
Name Field2 Type
Name Field3 Type
Name Field4 Type
Name Field5 Type
Name Field6 Type
Name Field7 Type

UNION query.

SELECT Name, Type, Field1 AS Data, 1 AS SourceField FROM table1
UNION SELECT Name, Type, Field2, 2 FROM table1
UNION SELECT Name, Type, Field3, 3 FROM table1
UNION SELECT Name, Type, Field4, 4 FROM table1
UNION SELECT Name, Type, Field5, 5 FROM table1
UNION SELECT Name, Type, Field6, 6 FROM table1
UNION SELECT Name, Type, Field7, 7 FROM table1;

UNION will not allow duplicate records. If you want all data even if duplicates, use UNION ALL.

There is no query wizard or designer for UNION, must type or copy/paste into SQLWindow of the query builder.

lhooker said:

How can I convert a table with horizontal data to a table with vertical data ?

This example illustrates one row (Table 1) of data to many rows of data (Table 2).

Source
Table 1
Name Type Field1 Field2 Field3 Field4 Field5 Field6 Field7

Results
Table 2
Name Field1 Type
Name Field2 Type
Name Field3 Type
Name Field4 Type
Name Field5 Type
Name Field6 Type
Name Field7 Type

Click to expand...

I call this Excel data, or "flat file" data...

I have done a comprehensive explanation in text and YouTube's here:-

http://www.niftyaccess.com/excel-in-access/

Where I demonstrate the problem and the solution.

I also provide a tool to effect the process.

Sent from my SM-G925F using Tapatalk

June7/Uncle Gizmo,

Thanks ! ! !