ACCESS FEED: Re: [MS_AccessPros] Rows in columns

Sabtu, 31 Januari 2015

Re: [MS_AccessPros] Rows in columns

While not perfect, you might try the following which is based on a single query and no new tables.

First, add a field to your table, autoID, autonumber.

Then:

TRANSFORM Max([Panel] & ", " & [Desc]) AS Expr

SELECT ID

FROM myTable

GROUP BY ID

PIVOT "Desc" & DSum("1","myTable","AutoID >=" & [autoid] & " And ID = '" & [id] & "'");

On Friday, January 30, 2015 8:24 PM, "Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

You can create the columns in strictly queries.

First create a ranking query [qrnkfguasp] to sequence the records within each ID.

SELECT Count(fguasp.ID) AS Rank, fguasp.ID, fguasp.Panel, fguasp.Description
FROM fguasp INNER JOIN fguasp AS fguasp_1 ON fguasp.ID = fguasp_1.ID
WHERE (((fguasp_1.Panel)<=[fguasp].[Panel]))
GROUP BY fguasp.ID, fguasp.Panel, fguasp.Description;

Create a table [tblNums] with a number field [Num] and values 1 - 10.

Then create a cartesian/crosstab query with the ranking table and tblNums:

TRANSFORM Min(Choose([Num],[Panel],[Description])) AS Expr2
SELECT qrnkfguasp.ID
FROM qrnkfguasp, tblNums
WHERE (((tblNums.Num) Between 1 And 2))
GROUP BY qrnkfguasp.ID
PIVOT Choose([Num],"Panel","Desc") & [Rank];

Duane Hookom MVP
MS Access

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Fri, 30 Jan 2015 15:14:09 -0800
Subject: [MS_AccessPros] Rows in columns

Is there an better/easier way to convert rows into columns?

I have the following data

ID	Panel	Description
ad	red	random text
ad	yellow	random text
ad	blue	random text
ba	orange	random text
ba	black	random text
c3	gray	random text
c3	white	random text
c3	purple	random text
c3	green	random text

and I want to convert it to

ID	Panel 1	DSCR 1	Panel 2	DSCR 2	Panel 3	DSCR 3	Panel 4	DSCR 4
ad	red	random text	yellow	random text	blue	random text
ba	orange	random text	black	random text		random text
c3	gray	random text	white	random text	purple	random text	green	random text

I put a sequential number for each item in that group and I am going to do it in with a nested loop where each sequential # points to a field group... Is there a better way?