Converting Excel to Access from Spreadsheet format

[Jeffstahl]

I need to link to an excel file, but I need to normalize the spreadsheet data
that contains columns of data that needs to be converted to relational
records. I think I said that right.

Excel file:
Name1 Name2 Name3
File A X X
File B X

Convert to;
File A Name1
File A Name3
File B Name2

Any ideas?

 

[Ken Snell [MVP]]

Use a UNION query to present the data that way.

SELECT T.Field1, T.Name1
FROM TableName AS T
UNION ALL
SELECT U.Field1, U.Name2
FROM TableName AS U
UNION ALL
SELECT V.Field1, V.Name3
FROM TableName AS V;