Columns to Rows in Ms Access

Columns to Rows in MS Access

First create a UNION query to normalize your table, then a crosstab to display the data:

SELECT [Name], "Cat 1" As Cat, [Cat 1] As CatVal FROM Table
UNION ALL
SELECT [Name], "Cat 2" As Cat, [Cat 2] As CatVal FROM Table
<...>

The query wizard will guide you through creating the crosstab.

EDIT re comments

To normalize the table of sample data, you can say:

SELECT [Name], "X1" As Cat, [X1] As CatVal FROM Table
UNION ALL
SELECT [Name], "X2" As Cat, Nz([X2A],0)+Nz([X2B],0)+Nz([X2C],0) As CatVal 
   FROM Table
UNION ALL
SELECT [Name], "X3" As Cat, Nz([X3A],0)+Nz([X3B],0)+Nz([X3C],0) As CatVal 
   FROM Table
UNION ALL
SELECT [Name], "X4" As Cat, [X4] As CatVal FROM Table
UNION ALL
SELECT [Name], "X5" As Cat, [X5] As CatVal FROM Table

Let us say the union query is QueryX:

TRANSFORM First(QueryX.CatVal) AS FirstOfCatVal
SELECT QueryX.Cat
FROM QueryX
GROUP BY QueryX.Cat
PIVOT QueryX.Name;

Transform/Transpose rows to columns in MS Access

With DCount you can get close using this query that also will sort the column values:

TRANSFORM 
    First(QueryQ.AT_cd)
SELECT 
    QueryQ.ID
FROM
    (SELECT 
        ID, 
        AT_cd, 
        "AT_cd" & CStr(DCount("*", "TableQ", "[ID] = '" & ID & "' And [AT_cd] <= '" & AT_cd & "'")) AS Sequence
    FROM 
        TableQ) As QueryQ 
GROUP BY 
    QueryQ.ID
PIVOT 
    QueryQ.Sequence;

Edit: From Duane Hookom I was suggested this alternative method using SQL only, thus perhaps faster:

TRANSFORM 
    First(QueryQ.AT_cd)
SELECT 
    QueryQ.ID
FROM 

    (SELECT TableQ.ID, TableQ.AT_cd, Count(TableQ.AT_cd) AS Sequence
    FROM TableQ 
    INNER JOIN TableQ AS TableQ_1 ON TableQ.ID = TableQ_1.ID
    WHERE (((TableQ.AT_cd)>=[TableQ_1].[AT_cd]))
    GROUP BY TableQ.ID, TableQ.AT_cd) As QueryQ

GROUP BY 
    QueryQ.ID
PIVOT 
    "AT_cd" & QueryQ.Sequence;

Access - transpose some column data into row records

Alternatively, consider an SQL union query which can be run in either Excel or Access, both using the Jet/ACE engine (Windows .dll files):

SELECT Col1, Col2, Col3
FROM TableOrSheet$Name
UNION ALL
SELECT Col1, Col2, Col4
FROM TableOrSheet$Name
UNION ALL
SELECT Col1, Col2, Col5
FROM TableOrSheet$Name
UNION ALL
SELECT Col1, Col2, Col6
FROM TableOrSheet$Name

converting column names to use as row fields in Access

Since you are using MS Access, there is no UNPIVOT function so you can use a UNION ALL query:

select 'SC' as Characteristic_Name, SC as Val
from yourtable
union all
select 'DO' as Characteristic_Name, DO as Val
from yourtable
union all
select 'temp' as Characteristic_Name, temp as Val
from yourtable
union all
select 'pH' as Characteristic_Name, pH as Val
from yourtable
union all
select 'etc' as Characteristic_Name, etc as Val
from yourtable;

As a side note, when you are doing a UNION ALL or UNPIVOT, the datatypes must be the same so you might have to convert the data in the val column so it is the same.

Microsoft Access new columns for data based on matching values

You can use a Crosstab Query

TRANSFORM Max(Resident.ResidentID) AS MaxOfResidentID
SELECT Resident.BldID, Resident.UnitID
FROM Resident
GROUP BY Resident.BldID, Resident.UnitID
ORDER BY Resident.BldID, Resident.UnitID
PIVOT "Res" & (DCount("*",
               "Resident",
               "BldID=" & [BldID] & " AND UnitID=" & [UnitID] &
               " AND ResidentID<'" & [ResidentID] & "'") + 1);

If you need a constant number of columns (e.g. if you want to create an Access report), then you can add an In clause to this query (before the ;):

In ("Res1","Res2","Res3","Res4","Res5","Res6")

This always creates 6 columns with the same names.

The difficulty is to get the row number per BldID/UnitID group. This is achieved by

DCount(1,
       "Resident",
       "BldID=" & [BldID] & 
       " AND UnitID=" & [UnitID] &
       " AND ResidentID<'" & [ResidentID] & "'") + 1

where Resident is the name of your table or query. It counts residents having the same BldID and UnitID but having a smaller ResidentID. Adding 1 to this count yields the row number starting at 1. Then the string "Res" is prepended to build the column name.

Note that the residents are listed in alphabetical order form left to right.

---

Related Topics