Jumat, 30 Januari 2015

RE: [MS_AccessPros] Rows in columns

 

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
IDPanelDescription
adredrandom text
adyellowrandom text
adbluerandom text
baorangerandom text
bablackrandom text
c3grayrandom text
c3whiterandom text
c3purplerandom text
c3greenrandom text


and I want to convert it to 
IDPanel 1DSCR 1Panel 2DSCR 2Panel 3DSCR 3Panel 4DSCR 4
adredrandom textyellowrandom textbluerandom text  
baorangerandom textblackrandom text random text  
c3grayrandom textwhiterandom textpurplerandom textgreenrandom 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?
Seq#ID
1ad
2ad
3ad
1ba
2ba
1c3
2c3
3c3
4c3






__._,_.___

Posted by: Duane Hookom <duanehookom@hotmail.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (5)

.

__,_._,___

Tidak ada komentar:

Posting Komentar