Import From Excel AND Transpose

  • Thread starter Thread starter quirkyjoe
  • Start date Start date
Q

quirkyjoe

Say I have data in Excel like this:

Num Test1 Test2 Test3
1 44 55 66
2 103 102 101
3 2 8 4

I want to import into Access 2003 and get it to look like this in my
Access table:

Num Test Value
1 1 44
1 2 55
1 3 66
2 1 103
2 2 102
2 3 101

you get the point

How can I import this excel data into Access without having to
transpose the data in Excel first then import into Access?

Thanks.
 
Use a union query to transpose like this --
SELECT quirkyjoe.Num, 1 AS Test, quirkyjoe.Test1 AS [VALUE]
FROM quirkyjoe
UNION ALL SELECT quirkyjoe.Num, 2 AS Test, quirkyjoe.Test2 AS [VALUE]
FROM quirkyjoe
UNION ALL SELECT quirkyjoe.Num, 3 AS Test, quirkyjoe.Test3 AS [VALUE]
FROM quirkyjoe;
 
Use a union query to transpose like this --
SELECT quirkyjoe.Num, 1 AS Test, quirkyjoe.Test1 AS [VALUE]
FROM quirkyjoe
UNION ALL SELECT quirkyjoe.Num, 2 AS Test, quirkyjoe.Test2 AS [VALUE]
FROM quirkyjoe
UNION ALL SELECT quirkyjoe.Num, 3 AS Test, quirkyjoe.Test3 AS [VALUE]
FROM quirkyjoe;

--
KARL DEWEY
Build a little - Test a little



quirkyjoe said:
Say I have data in Excel like this:
Click to expand...
Num Test1 Test2 Test3
1 44 55 66
2 103 102 101
3 2 8 4
Click to expand...
I want to import into Access 2003 and get it to look like this in my
Access table:
Click to expand...
Num Test Value
1 1 44
1 2 55
1 3 66
2 1 103
2 2 102
2 3 101
Click to expand...
you get the point
Click to expand...
How can I import this excel data into Access without having to
transpose the data in Excel first then import into Access?
Click to expand...
Thanks.- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Thanks Karl,

One question. When you use "quirkyjoe" in the example syntax is that
the table name before the period?
 
quirkyjoe said:
Use a union query to transpose like this --
SELECT quirkyjoe.Num, 1 AS Test, quirkyjoe.Test1 AS [VALUE]
FROM quirkyjoe
UNION ALL SELECT quirkyjoe.Num, 2 AS Test, quirkyjoe.Test2 AS [VALUE]
FROM quirkyjoe
UNION ALL SELECT quirkyjoe.Num, 3 AS Test, quirkyjoe.Test3 AS [VALUE]
FROM quirkyjoe;
Click to expand...

Thanks Karl,

One question. When you use "quirkyjoe" in the example syntax is that
the table name before the period?
Click to expand...

Yes.
 
Use a union query to transpose like this --
SELECT quirkyjoe.Num, 1 AS Test, quirkyjoe.Test1 AS [VALUE]
FROM quirkyjoe
UNION ALL SELECT quirkyjoe.Num, 2 AS Test, quirkyjoe.Test2 AS [VALUE]
FROM quirkyjoe
UNION ALL SELECT quirkyjoe.Num, 3 AS Test, quirkyjoe.Test3 AS [VALUE]
FROM quirkyjoe;

--
KARL DEWEY
Build a little - Test a little



quirkyjoe said:
Say I have data in Excel like this:
Click to expand...
Num Test1 Test2 Test3
1 44 55 66
2 103 102 101
3 2 8 4
Click to expand...
I want to import into Access 2003 and get it to look like this in my
Access table:
Click to expand...
Num Test Value
1 1 44
1 2 55
1 3 66
2 1 103
2 2 102
2 3 101
Click to expand...
you get the point
Click to expand...
How can I import this excel data into Access without having to
transpose the data in Excel first then import into Access?
Click to expand...
Thanks.- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

OK. I have successfully used the union query that Karl recommended to
produce a new table with the data in the proper format for a
relational database as listed above. Using the example above, now I
want to do a query and find the max Value for each Num and ignore the
ones that are not maxes. So how do I do a query and get this result
using the example:

Num Test Value
1 3 66
2 1 103

Basically I want to go through each Num and find the max Value and
have it so I can identify which Test the max Value came from.

Thanks!
 
Back
Top