Access 2010 Transpose a Simple Table

Question

I have a simple table:

Operation	CMFStatus	PMAStatus	QEFStatus
MTV	Yellow	Red	Red
VH1	Red	Yellow	Yellow
BET	Green	Red	Green
MTV2	Yellow	Yellow	Red
FUSE	Green	Yellow	Green
CMT	Red	Green	Red

I would like that table to look like this:

Operation	MTV	VH1	BET	MTV2	FUSE	CMT
CMFStatus	Yellow	Red	Green	Yellow	Green	Red
PMAStatus	Red	Yellow	Red	Yellow	Yellow	Green
QEFStatus	Red	Yellow	Green	Red	Green	Red

I'm sort of shocked that this isn't a built in function in Access.  It's as simple as doing a copy and paste special in Excel but apparently in Access this requires VBA or some configuration of a Crosstab that I can't seem to figure out.  I'm concerned that this is so obvious that I should know how to do it, but I do not.  Can someone explain?

Answer 1

It is not obvious because both of those views of your data are spreadsheets, not properly designed database tables.  And, Access does not provide simple ways of working on tables that violate the fundamental rules of databases.

Your simple "table" needs to be three tables.

The Operations table should look like:

OperationID     AutoNumber  primary key

OperationName   Text            unique index

   . . .  other fields about operations

The Status table:

StatusID           AutoNumber primary key

StatusLevel      Text

   . . .  other fields about status

And, the OperationStatus table:

OperationStatusID   AutoNumber  primary key

OperationID             Long               foreign key

StatusID                   Long               foreign key

   . . .  other fields about an operation's status

Then the result you want can be presented to users in a read only form or report by using a crosstab query.   You can collect all the data you want to see by using a query that joins those tables:

SELECT OperationName, StatusLevel

FROM  Operations INNER JOIN (OperationStatus

                INNER JOIN Status ON OperationStatusStatusID = Status.StatusID)

           ON Operations.OperationID = OperationStatus.OperationID

With that list of values in a query you can use that query as the basis for a Crosstab query to reorganize the data to what you want to present to users.  I think the only non obvious thing you need is to use First or Last instead of Count or Sum.

Important:  You, and especially your users, should never use a table or query's datasheet to add/edit/delete information in your database.  Always create forms for manipulating data and/or reports for viewing the data in a user friendly layout with nicely formatted values.

Answer 2

Access is a relationaly database and your table is not normalized. In a normalized database structure each color/status would create its own record like:

Oper       Check      Status

MTV     CMF    Yellow

MTV     PMA    Red

MTB     QEF    Red

You can create a normalizing union query (assumes table named tblRevidium13)

=== quniRevidium13 ====

SELECT "CMFStatus" AS RowHead, Operation AS ColHead, [CMFStatus] AS TheVal

FROM tblRevidium13

UNION ALL

SELECT "PMAStatus", Operation, PMAStatus

FROM tblRevidium13

UNION ALL

SELECT "QEFStatus", Operation, QEFStatus

FROM tblRevidium13;

Then create a crosstab query based on the union query:

TRANSFORM First(TheVal) AS FirstOfTheVal

SELECT RowHead

FROM quniRevidium13

GROUP BY RowHead

PIVOT ColHead;

Answer 3

Hi,

You must define Table1 and Table2.

Table1 x fields y records

Table2 y fields

try with this code (paste it in a module):

Option Compare Database

Option Explicit

Sub TransposeTable()

    Dim myRsIn As Recordset

    Dim myRsOut As Recordset

    Dim i As Integer

    Dim j As Integer

    Dim Matr() As String

    Set myRsIn = CurrentDb.OpenRecordset("Table1")

    Set myRsOut = CurrentDb.OpenRecordset("Table2")

    ReDim Matr(myRsIn.Fields.Count - 1, DCount("*", "Table1") - 1)

    j = 0

    Do While Not myRsIn.EOF

       For i = 0 To myRsIn.Fields.Count - 1

           Matr(i, j) = myRsIn(i)

       Next i

       myRsIn.MoveNext

       j = j + 1

    Loop

    For j = 0 To myRsIn.Fields.Count - 1

       myRsOut.AddNew

       For i = 0 To DCount("*", "Table1") - 1

           myRsOut(i) = Matr(j, i)

       Next i

       myRsOut.Update

    Next j

    myRsIn.Close

    myRsOut.Close

    Set myRsIn = Nothing

    Set myRsOut = Nothing

End Sub

Mimmo