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

[TABLE="width: 335"]
<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>[TR]
[TD="width: 90, bgcolor: transparent"]Product Code [/TD]
[TD="width: 166, bgcolor: transparent"]Product Name[/TD]
[TD="width: 64, bgcolor: transparent"]P01[/TD]
[TD="width: 64, bgcolor: transparent"]P02[/TD]
[TD="width: 64, bgcolor: transparent"]P03[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]AA01[/TD]
[TD="bgcolor: transparent"]Lundry[/TD]
[TD="bgcolor: transparent"]Tom[/TD]
[TD="bgcolor: transparent"]Tom[/TD]
[TD="bgcolor: transparent"]Eric[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]AA02[/TD]
[TD="bgcolor: transparent"]Presto[/TD]
[TD="bgcolor: transparent"]Bob[/TD]
[TD="bgcolor: transparent"]Tom[/TD]
[TD="bgcolor: transparent"]Bob[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]AA03[/TD]
[TD="bgcolor: transparent"]Parsa[/TD]
[TD="bgcolor: transparent"]Eric[/TD]
[TD="bgcolor: transparent"]Tom[/TD]
[TD="bgcolor: transparent"]Eric[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]

[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Product Code [/TD]
[TD="bgcolor: transparent"]Product Name[/TD]
[TD="bgcolor: transparent"]Period[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]AA01[/TD]
[TD="bgcolor: transparent"]Lundry[/TD]
[TD="bgcolor: transparent"]Tom[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]AA01[/TD]
[TD="bgcolor: transparent"]Lundry[/TD]
[TD="bgcolor: transparent"]Tom[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]AA01[/TD]
[TD="bgcolor: transparent"]Lundry[/TD]
[TD="bgcolor: transparent"]Eric[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]AA02[/TD]
[TD="bgcolor: transparent"]Presto[/TD]
[TD="bgcolor: transparent"]Bob[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]AA02[/TD]
[TD="bgcolor: transparent"]Presto[/TD]
[TD="bgcolor: transparent"]Tom[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]AA02[/TD]
[TD="bgcolor: transparent"]Presto[/TD]
[TD="bgcolor: transparent"]Bob[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]AA03[/TD]
[TD="bgcolor: transparent"]Parsa[/TD]
[TD="bgcolor: transparent"]Eric[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]AA03[/TD]
[TD="bgcolor: transparent"]Parsa[/TD]
[TD="bgcolor: transparent"]Tom[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]AA03[/TD]
[TD="bgcolor: transparent"]Parsa[/TD]
[TD="bgcolor: transparent"]Eric[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

 

[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.