Re: Corstabquery or normal query

You can create the crosstab similar to the suggestion by John. The Value can
be the results of a concatenation using the function that can be found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane. Check out
the Generic Function To Concatenate Child Records.
--
Duane Hookom
Microsoft Access MVP


"Frank Situmorang" wrote:

Yes, Duane we have of course, like deacons, we have many deacons in our
church, also church elders, we have many elders, only 1st elder is one person.

So if I could elaborate again that the report now that already works is like
below, but I want it side way per year, because the election committee have
the broad picure of who is more often serving as chruch elder for example, we
can see it from year to year. We can make it in excel manually, but since the
data base that I am building now could be multi functions, so I want to make
something like cross tab query but I have a problem,

Year 2005
No. Position Name
1, Elder Mr. A
2. clerck Mr. B
3. Deacon 1. Mr. C
2, Mr. P
3. Mr. Q
4. Mr. R

Could you please help me on this, my database is alreay 70% complete except
for tutorial, merge

Thanks in advance

--
H. Frank Situmorang


"Duane Hookom" wrote:

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: Access 2003 - Transpose
    ... John, I think your approach uses crosstab, which as I discovered yesterday, ... I want to transpose BOMPart#, ... Second query uses the above saved query and your table to get the desired ...
    (microsoft.public.access.queries)
  • Re: Crosstab query as data source for update query
    ... You can use the Crosstab in a make table query to make a temporary table with the values and then you probably can use the temporary table to update records in. ... As John has stated. ... This is my crosstab query to summarize labor hours by job number and operation number: ...
    (microsoft.public.access.queries)
  • Re: moving like column data to a new columns for related rows
    ... John, thanks again for looking at this. ... I saved the query as qRankQuery and it is the base for the crosstab ... In the Select View of the qRankQuery - The dataset looks good. ...
    (microsoft.public.access.queries)
  • 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)