Converting Excel to Access from Spreadsheet format

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jeffstahl - 27 Aug 2005 19:55 GMT

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?

Reply to this Message

Ken Snell [MVP] - 27 Aug 2005 20:18 GMT

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;

Signature

Ken Snell
<MS ACCESS MVP>

>I need to link to an excel file, but I need to normalize the spreadsheet
>data
[quoted text clipped - 12 lines]
>
> Any ideas?

Reply to this Message