Convert a Matrix Table to a Vertical Table

[countingguru]

Dear Friends

I am trying to convert a linked matrix excel worksheet to a Vertical tabulated sheet in Access with all the same content intact.
Is there a code that can execute this conversion automatically?
Or is there a query function that can perform this task as well?
What is the best approach?
Thanks in advance !

The linked excel matrix table:

Biz Unit: Unit 1 Unit 2 Unit 3 Unit 4 Sum
Project: A 10% 90% 0% 0% 100%
B 0% 40% 60% 0% 100%
C 0% 10% 30% 60% 100%



Converted Sheet:

Project Biz Unit Pct


A Unit 1 10%
A Unit 2 90%
B Unit 2 40%
B Unit 3 60%
C Unit 2 10%
C Unit 3 30%
C Unit 4 60%

[pbaldy]

You can use a UNION query to get that result.

[countingguru]

You can use a UNION query to get that result.

Dear Baldy

Can you script the union query please?

Thanks

[June7]

The matrix table looks odd. Is there a Header for the Project column?

There is no wizard for UNION query, must type into the SQL window. The structure would be:

SELECT "A" As Project, "Unit1" As BizUnit, [Unit 1] As Pct FROM tablename WHERE Project = "A"
UNION SELECT "A", "Unit2", [Unit 2] FROM tablename WHERE Project = "A"
UNION SELECT "B", "Unit2", [Unit 2] FROM tablename WHERE Project = "B"
UNION SELECT "B", "Unit3", [Unit 3] FROM tablename WHERE Project = "B"
UNION SELECT "C", "Unit2", [Unit 2] FROM tablename WHERE Project = "C"
UNION SELECT "C", "Unit3", [Unit 3] FROM tablename WHERE Project = "C"
UNION SELECT "C", "Unit4", [Unit 4] FROM tablename WHERE Project = "C";

There is a limit of 50 lines in UNION.

[countingguru]

Yes. "Project" is the Header for the Project columns that have A,B,C.

One question, how can I automate the SQL to consider all/any "Unit.." columns that have a value per each project....Despite of the fact that Project A (per illustration above) only has values for Unit 1 and 2.

[June7]

Can't be dynamic. Include a UNION row for each project and UnitX regardless if percent is 0 or greater. So every project will have 4 UnitX rows. Are there only the 3 projects? Remember, only 50 lines in UNION query. Also, recordsets in VBA don't like UNION queries. I had to redesign a procedure to eliminate dependence on UNION query. The alternative is code to write records to a temp table.

EDIT: I have since learned that must be DAO recordset to manipulate UNION query.