Transpose Table

[brendanolear]

I have a basic table I wish to transpose but can't seem to do so in access.
I use the normalization method excel, but can't see how it can be achieved in access.

Current Table at top and outcome I seek is below

Product Code	Product Name	P01	P02	P03
AA01	Lundry	Tom	Tom	Eric
AA02	Presto	Bob	Tom	Bob
AA03	Parsa	Eric	Tom	Eric
Product Code	Product Name	Period
AA01	Lundry	Tom
AA01	Lundry	Tom
AA01	Lundry	Eric
AA02	Presto	Bob
AA02	Presto	Tom
AA02	Presto	Bob
AA03	Parsa	Eric
AA03	Parsa	Tom
AA03	Parsa	Eric

<colgroup><col width="90" style="width: 67pt; mso-width-source: userset; mso-width-alt: 3185;"> <col width="166" style="width: 124pt; mso-width-source: userset; mso-width-alt: 5888;"> <col width="64" style="width: 48pt;" span="3"> <tbody> </tbody>

 

[Joe4]

You can create a "UNION ALL" query in Access to do this.
So, if your Table was named "Table1", the SQL code for the query would look like:

SELECT Table1.[Product Code], Table1.[Product Name], Table1.[P01] AS Period
FROM Table1
UNION ALL
SELECT Table1.[Product Code], Table1.[Product Name], Table1.[P02] AS Period
FROM Table1
UNION ALL
SELECT Table1.[Product Code], Table1.[Product Name], Table1.[P03] AS Period
FROM Table1
ORDER BY Table1.[Product Code];

 

[brendanolear]

Thanks Joe4 - much appreciated.

 

[Joe4]

You are welcome.

 

[brendanolear]

I've missed out a critical requirement. I would also like to see a 4th column, as per example above, P01 would be present to show Month etc. Is that possible in a select query?

 

[Joe4]

I've missed out a critical requirement. I would also like to see a 4th column, as per example above, P01 would be present in first 3 rows, P02 in rows 4 to 6 etc. Is that possible in a select query?

I am not sure I follow what you are saying your structure is. If there are really 4 "P0" columns, just continue on with another "UNION ALL" block in the SQL code.
If that is not what you mean, please show examples of data and expected results like you did in your original post.

 

[brendanolear]

Sorry Joe4 - didn't explain properly. I've resolved. I just needed to add a "P01" within your code at Period P01 to record a flag in the fourth column. Copy that through "P02" etc. Thanks again.

 

[Joe4]

Ah yes, I see now.
Yes, you would just hard-code in "P01", "P02", etc at the end of the SELECT lines in each block.