Transpose Form

Question

This might not be possible, but there are some pretty smart, creative people here, so I thought I'd ask:

I have data in the following normalized table:

                 Field1     Field2     Field3     Field4     Field5

Record1

Record2

Record3

I want to display this in an editable form in a transposed configuration:

                Record1        Record2       Record3

Field1

Field2

Field3

Field4

Field5

I've researched this question here and on the web and most answers for non-editable data point to using crosstab queries, pivot table forms or using vba to transpose the data into a new table.  I've explored these to my ability and have found that none of them appear to meet my needs.  The problem is one of display, not data structure.

The ideal solution would be a form that simply reverses the positions of rows and columns, but has no other functionality different from a standard datasheet or continuous form.  Forms have an Orientation Property that can be set to "Left to Right" or "Right to Left".  I need the equivalent of being able to set this property to "Top to Bottom".  And as mentioned, I do need the resulting form to allow editing of the table data.

Thanks for any ideas on this.

Answer 1

This is the data, union query, crosstab query and results --

1	1A	1B	1C	1D	1E
2	2A	2B	2C	2D	2E
3	3A	3B	3C	3D	3E

SELECT Table1.RecordNUM, Table1.Field1, 1 AS NewField

FROM Table1

UNION ALL SELECT Table1.RecordNUM, Table1.Field2, 2 AS NewField

FROM Table1

UNION ALL SELECT Table1.RecordNUM, Table1.Field3, 3 AS NewField

FROM Table1

UNION ALL SELECT Table1.RecordNUM, Table1.Field4, 4 AS NewField

FROM Table1

UNION ALL SELECT Table1.RecordNUM, Table1.Field5, 5 AS NewField

FROM Table1;

TRANSFORM First(Query10.Field1) AS FirstOfField1

SELECT Query10.NewField

FROM Query10

GROUP BY Query10.NewField

PIVOT Query10.RecordNUM;

1	1A	2A	3A
2	1B	2B	3B
3	1C	2C	3C
4	1D	2D	3D
5	1E	2E	3E

Answer 2

Thanks for the reply John - yes your suggestions do sound difficult.

The real-life situation/problem is larger than what I've described to this point, but I thought I could make it work if I could do this part.  I have two tables of data - one representing header info and one representing detail info that is manufacturing results data.  I need to present that data in the following manner because of how the data is visually analyzed and selected.  I've made up this data, but hopefully it provides additional insight.

The header info must be in the same columns as the detail info, but they are each arranged differently:  (DetailField1 values are a union of all values in the recordset, DetailField2 is repeated with each column holding the data of a specific Production.  Yes, this part is like a pivot table)

HeaderField1     Production1                     Production2                           Production3

HeaderField2     Plant1                              Plant2                                    Plant1

HeaderField3     20,000                             10,0000                                  2,000

HeaderField4     $2.25                               $1.26                                     $1.85

HeaderField5     90.3%                              86.2%                                    89.0%

HeaderField6     Yes                                  Yes                                        No

DetailField1        DetailField2(from Prod1)   DetailField2(from Prod2)         DetailField2(from Prod3)

Actual Descr1     5.0%                               10.2%                                   

Actual Descr2     54.3%                                                                          75.4%

Actual Descr3     31.0%                             76.0%                                    13.6%

Any clearer?

Answer 3

Thanks for your reply Karl.

A union query when all the data is in one table?  Please explain.

The SQL is very simple:

SELECT Field1, Field2, Field3, Field4, Field5

FROM Table1

The layout desired is in my original post.

Answer 4

Well, since data is not stored in forms but only in tables, this is going to be a bit challenging!

What's the real-life situation? What does the data represent?

Since crosstabs are never updateable, the only solution that comes to mind is to have an unbound form with recordcount*fieldcount unbound textboxes with code to populate it and to move data back into the tables; the form might be static or might need to be dynamically created on the fly. The first option is going to be a tedious chore and the second a much MORE tedious and difficult mess!

Answer 5

I think you need a union query followed by a crosstab.

Post the SQL of a select query that has your fields.   What layout do you want from those fields?