Convert Spreadsheet Array to MS Access

[Rod MacPherson]

I frequently have to convert spreadsheet arrays into relational
tables, and was wondering if anyone had a trick for doing so.
Example:

Apr May Jun
Entity 1 10 20 30
Entity 2 15 25 35
Entity 3 18 28 38
Needs to be converted toL

Entity 1, Apr, 10
Entity 1, May, 20
Entity 1, Jun, 30
Entity 2, Apr, 15
Entity 2, May, 25
etc.
etc.

[Douglas J. Steele]

When you say "spreadsheet array" are you linking to Excel? If so, you should
be able to write a Union query to give what you want:

Assuming that the fields in the linked Excel table are named Field1, Apr,
May and Jun, your query would be something like:

SELECT Field1, "Apr" As WhatMonth, Apr FROM MyLinkedExcelTable
UNION
SELECT Field1, "May" As WhatMonth, May FROM MyLinkedExcelTable
UNION
SELECT Field1, "Jun" As WhatMonth, Jun FROM MyLinkedExcelTable

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Rod MacPherson" <ro*@rodmacpherson.com> wrote in message
news:88**************************@posting.google.c om...

I frequently have to convert spreadsheet arrays into relational
tables, and was wondering if anyone had a trick for doing so.
Example:

Apr May Jun
Entity 1 10 20 30
Entity 2 15 25 35
Entity 3 18 28 38
Needs to be converted toL

Entity 1, Apr, 10
Entity 1, May, 20
Entity 1, Jun, 30
Entity 2, Apr, 15
Entity 2, May, 25
etc.
etc.