Re: Corstabquery or normal query

Tech-Archive recommends: Fix windows errors by optimizing your registry

John's idea should work. However, do you ever have a year with more than one
person in a position? If so, the crosstab would only return one of the names.

--
Duane Hookom
Microsoft Access MVP


"John Spencer" wrote:

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



.

Relevant Pages

  • Re: Corstabquery or normal query
    ... Do you have one table with fields like - fldYear, fldPosition, and fldName? ... Now I would use the saved query as the source of a crosstab query. ... Normal report based on the normal query: ...
    (microsoft.public.access.reports)
  • Re: TWO Crosstab Query Ouput Questions
    ... field very similar to the LOCATE field in the STATES2 that we created. ... I was certain that all I had to do was create a crosstab on RCROSS with RLOC ... do is to modify the main query to change your RFLY_RSTA to LOCATE and the ... locations for each species as rows with the ...
    (microsoft.public.access.queries)
  • Re: TWO Crosstab Query Ouput Questions
    ... we want to see and use those as the input to the crosstab. ... and only 1098 in the final query. ... locations for each species as rows with the ... concerning the Totals Query. ...
    (microsoft.public.access.queries)
  • Re: dynamic crosstab A2002 - Almost there
    ... I would set up the crosstab as ... Query 1: QryMonthlyIncidentsBase_Crosstab ... Base your report on this query. ... >>> Dim ReportLabelAs String ...
    (microsoft.public.access.queries)
  • Re: Cross Tab Query with two value=-[Result] & " - " & [Error] as the
    ... This solution uses a crosstab with a cartesian to create multiple values. ... Anyway, the Union Query now has 6 SELECT parts, not 3, and each one ... SELECT ID,Client,Date, ResultA as Value1, "ResultA" as Task ...
    (microsoft.public.access.queries)