Convert Spreadsheet Array to MS Access

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.

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" <rod@rodmacpherson.com> wrote in message
news:88d5d17f.0411011301.63a7de79@posting.google.c om...[color=blue]
> 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.[/color]