importing matrix table in excel into access database

[thread]

Hi All
I have a matrix table in excel that i need to import to ACCESS in a
relevant format
this is the structure:
horizontal grade
vertical country
the combination between the twoo gives som amount
is there any way to import this matrix to ACCESS in suitable format to
ACCESS so it will be easy to update other tables with this table?
country and grade are primary keys of course

 

[John W. Vinson]

Hi All
I have a matrix table in excel that i need to import to ACCESS in a
relevant format
this is the structure:
horizontal grade
vertical country
the combination between the twoo gives som amount
is there any way to import this matrix to ACCESS in suitable format to
ACCESS so it will be easy to update other tables with this table?
country and grade are primary keys of course

A "Normalizing Union Query" is the ticket here.

If you have fewer than 255 columns (and I don't think there are that many
countries), use File... Get External Data... Link to connect to the Excel
spreadsheet. Create a query in the SQL window:

SELECT [Grade], "ABW" AS Country, [ABW] AS YourValue
WHERE [ABW] IS NOT NULL
UNION ALL
SELECT [Grade], "AFG", [AFG] WHERE [AFG] IS NOT NULL
UNION ALL
SELECT [Grade], "AGO", [AGO] WHERE [AFG] IS NOT NULL
<etc etc>

You might have to split this into a couple of queries if you get the "Query
Too Complex" error. I'm using the UN country codes (for Aruba, Afghanistan and
Angola) - use your own country names of course.

You can then base an Append query upon this query to populate a normalized
tall-thin table with fields [Grade], [Country] and [YourValue].

John W. Vinson [MVP]