Re: Corstabquery or normal query

Tech-Archive recommends: Speed Up your PC by fixing your registry

What does your data look like? What tables? What Fields in the tables?

Do you have one table with fields like - fldYear, fldPosition, and fldName?

Assuming that is the structure, I would first build a query to get the data
and a ranking order for the data (qOrdered). WARNING: Untested SQL
statements follow - they may have syntax errors or they may give undesired
results.


SELECT Ta.fldYear, Ta.fldPosition, Ta.fldName,
Count(Tb.fldPosition) as RankOrder
FROM [Your Table] as Ta LEFT JOIN [Your Table] as Tb
ON Ta.fldYear=Tb.FldYear AND Ta.fldPosition = Tb.fldPosition
AND Ta.fldName < Tb.fldName
GROUP BY Ta.fldYear, Ta.fldPosition, Ta.fldName

Now I would use the saved query as the source of a crosstab query.

TRANSFORM First(FldName) as theName
SELECT fldPosition, fldRankOrder
FROM qOrdered
GROUP BY fldPosition, fldRankOrder
PIVOT fldYear

Hope this helps.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Frank Situmorang" <hfsitumo2001@xxxxxxxxx> wrote in message
news:38811B1B-7550-4343-8F16-0D6D299E0DC0@xxxxxxxxxxxxxxxx
Hello,

Maybe I have a langguage problem in expressing my difficulties, but here
is
what I wanted

Normal report based on the normal query:

Year 2005
No. Position Name
1, Elder Mr. A
2. clerck Mr. B
3. Deacon Mr. C
Year 2006
1, Elder Mr. D
2. clerck Mr. E
3. Deacon Mr. F
Year 2007...same way downward

I wanted it that the year is Sideway"

No. Position Name Year 2005 Year 2006 Year 2007
1, Elder Mr. A Mr. D
.......
2. clerck Mr. B Mr. E
.......
3. Deacon Mr. C Mr. F
.........


In other words no grouping in rowheading, and only grouping by year for
column heading, and should be able goes side way whenever we have next
year
and on.

We need your help

Thanks

--
H. Frank Situmorang


.