Corstabquery or normal query

Frank Situmorang · Feb 18, 2008

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

John Spencer · Feb 18, 2008

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
..

Duane Hookom · Feb 18, 2008

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 said:
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 said:

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

Click to expand...

Frank Situmorang · Feb 19, 2008

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 said:
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 said:

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 said:

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

Click to expand...

Click to expand...

Duane Hookom · Feb 19, 2008

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 said:
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 said:

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 said:

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
..

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

Click to expand...

Click to expand...

John Spencer · Feb 19, 2008

Duane,
I thought by adding the Ranking that I would get multiple lines for each
position in the Crosstab query. OR did I miss something with that.

Year Position Name RankOrder
2007 Deacon Bob 1
2007 Deacon Tom 2
2007 Deacon Zeara 3
2008 Deacon Bob 1
2008 Deacon Zeara 2

Then crosstab would return
Position Rank 2007 2008
Deacon 1 Bob Bob
Deacon 2 Tom Zeara
Decaon 3 Zeara (Null)

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

Duane Hookom said:
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 said:

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 said:

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

Click to expand...

Click to expand...

Frank Situmorang · Feb 25, 2008

John,

I want to use your suggestion, but can you explain again on how can we make
the query you said ( SQL) and then how can we make the TRANSFOR and Pivot. I
appreciate your help on how can we build that query. Normally I can use grid
query and also someone told me in this thread how to make SQL but we must in
in the form first and then in the data record source, we can build the SQL.

Thanks in advance
--
H. Frank Situmorang

John Spencer said:
Duane,
I thought by adding the Ranking that I would get multiple lines for each
position in the Crosstab query. OR did I miss something with that.

Year Position Name RankOrder
2007 Deacon Bob 1
2007 Deacon Tom 2
2007 Deacon Zeara 3
2008 Deacon Bob 1
2008 Deacon Zeara 2

Then crosstab would return
Position Rank 2007 2008
Deacon 1 Bob Bob
Deacon 2 Tom Zeara
Decaon 3 Zeara (Null)

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

Duane Hookom said:

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 said:

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
..

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

Click to expand...

Click to expand...

John Spencer · Feb 25, 2008

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

Open a new query
-- Add your table to the query two times (I'll call those two - Table 1 and
Table 2)
-- Drag from the Year field in table 1 to the year field in table 2
-- Drag from the position field in table 1 to the position field in table 2
-- Drag from the Name field in table 1 to the name field in table 2
(That should set up three join lines) between the tables
-- Add the fields year, position, and name from table 1 to the grid
-- Add the position field from table 2 to the grid
-- SELECT View: Totals from the menu
-- Change GROUP BY to Count under table 2 position field
-- SELECT View: SQL View from the menu
In the text that is now visible,
-- Change INNER JOIN to LEFT JOIN
-- Change table1.Name field = Table2.Name Field to
table1.Name field < Table2.Name Field

Try to run the query. If it runs, then save it and use that as the source
of a crosstab query. IF you have problems with the crosstab query, post
back with any error messages, so someone can help you.

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

Frank Situmorang said:
John,

I want to use your suggestion, but can you explain again on how can we
make
the query you said ( SQL) and then how can we make the TRANSFOR and
Pivot. I
appreciate your help on how can we build that query. Normally I can use
grid
query and also someone told me in this thread how to make SQL but we must
in
in the form first and then in the data record source, we can build the
SQL.

Thanks in advance
--
H. Frank Situmorang

John Spencer said:

Duane,
I thought by adding the Ranking that I would get multiple lines for each
position in the Crosstab query. OR did I miss something with that.

Year Position Name RankOrder
2007 Deacon Bob 1
2007 Deacon Tom 2
2007 Deacon Zeara 3
2008 Deacon Bob 1
2008 Deacon Zeara 2

Then crosstab would return
Position Rank 2007 2008
Deacon 1 Bob Bob
Deacon 2 Tom Zeara
Decaon 3 Zeara (Null)

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

Duane Hookom said:

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

:

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
..

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

Click to expand...

Click to expand...

Duane Hookom · Feb 25, 2008

John,
I was initially skeptical about being able to create a crosstab based off
your first "totals" query since my usual attempts to do this used a subquery
to create the RankOrder column. I tried your solution against the employees
table in Northwind and it worked. My first query:
=== qselRankTitle ===============
SELECT Employees.Title, Employees.LastName, Count(Employees_1.EmployeeID) AS
RankOrder
FROM Employees LEFT JOIN Employees AS Employees_1 ON Employees.Title =
Employees_1.Title
WHERE (((Employees.EmployeeID)<=[Employees_1].[EmployeeID]))
GROUP BY Employees.Title, Employees.LastName
ORDER BY Employees.Title;
==============================
Then my crosstab
=== qxtbEmployeeTitle ==============
TRANSFORM First(qselRankTitle.LastName) AS FirstOfLastName
SELECT qselRankTitle.Title
FROM qselRankTitle
GROUP BY qselRankTitle.Title
PIVOT qselRankTitle.RankOrder;
===============================

This placed the "names" into individual columns while the solution that I
suggested concatenates the names into a single column.

Thanks for the alternative solution!
--
Duane Hookom
Microsoft Access MVP

John Spencer said:
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

Open a new query
-- Add your table to the query two times (I'll call those two - Table 1 and
Table 2)
-- Drag from the Year field in table 1 to the year field in table 2
-- Drag from the position field in table 1 to the position field in table 2
-- Drag from the Name field in table 1 to the name field in table 2
(That should set up three join lines) between the tables
-- Add the fields year, position, and name from table 1 to the grid
-- Add the position field from table 2 to the grid
-- SELECT View: Totals from the menu
-- Change GROUP BY to Count under table 2 position field
-- SELECT View: SQL View from the menu
In the text that is now visible,
-- Change INNER JOIN to LEFT JOIN
-- Change table1.Name field = Table2.Name Field to
table1.Name field < Table2.Name Field

Try to run the query. If it runs, then save it and use that as the source
of a crosstab query. IF you have problems with the crosstab query, post
back with any error messages, so someone can help you.

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

Frank Situmorang said:

John,

I want to use your suggestion, but can you explain again on how can we
make
the query you said ( SQL) and then how can we make the TRANSFOR and
Pivot. I
appreciate your help on how can we build that query. Normally I can use
grid
query and also someone told me in this thread how to make SQL but we must
in
in the form first and then in the data record source, we can build the
SQL.

Thanks in advance
--
H. Frank Situmorang

John Spencer said:

Duane,
I thought by adding the Ranking that I would get multiple lines for each
position in the Crosstab query. OR did I miss something with that.

Year Position Name RankOrder
2007 Deacon Bob 1
2007 Deacon Tom 2
2007 Deacon Zeara 3
2008 Deacon Bob 1
2008 Deacon Zeara 2

Then crosstab would return
Position Rank 2007 2008
Deacon 1 Bob Bob
Deacon 2 Tom Zeara
Decaon 3 Zeara (Null)

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

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

:

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
..

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

Click to expand...

Click to expand...

Frank Situmorang · Feb 26, 2008

John, when I followed step by step procedures like you suggested, I still do
not know how to make it in rank order and how to make a transform....

This is my SQL when I followed your suggestion.
SELECT PelayanJemaat.TahunPel, PelayanJemaat.BidangPelayanan,
PelayanJemaat.NamaPel, Count(PelayanJemaat2.BidangPelayanan) AS
CountOfBidangPelayanan
FROM PelayanJemaat LEFT JOIN PelayanJemaat2 ON
(PelayanJemaat.TahunPel=PelayanJemaat2.TahunPel) AND
(PelayanJemaat.BidangPelayanan=PelayanJemaat2.BidangPelayanan) AND
(PelayanJemaat.NamaPel<PelayanJemaat2.NamaPel)
GROUP BY PelayanJemaat.TahunPel, PelayanJemaat.BidangPelayanan,
PelayanJemaat.NamaPel;

Thanks for anybody's idea to solve this problem
--
H. Frank Situmorang

John Spencer said:
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

Open a new query
-- Add your table to the query two times (I'll call those two - Table 1 and
Table 2)
-- Drag from the Year field in table 1 to the year field in table 2
-- Drag from the position field in table 1 to the position field in table 2
-- Drag from the Name field in table 1 to the name field in table 2
(That should set up three join lines) between the tables
-- Add the fields year, position, and name from table 1 to the grid
-- Add the position field from table 2 to the grid
-- SELECT View: Totals from the menu
-- Change GROUP BY to Count under table 2 position field
-- SELECT View: SQL View from the menu
In the text that is now visible,
-- Change INNER JOIN to LEFT JOIN
-- Change table1.Name field = Table2.Name Field to
table1.Name field < Table2.Name Field

Try to run the query. If it runs, then save it and use that as the source
of a crosstab query. IF you have problems with the crosstab query, post
back with any error messages, so someone can help you.

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

Frank Situmorang said:

John,

I want to use your suggestion, but can you explain again on how can we
make
the query you said ( SQL) and then how can we make the TRANSFOR and
Pivot. I
appreciate your help on how can we build that query. Normally I can use
grid
query and also someone told me in this thread how to make SQL but we must
in
in the form first and then in the data record source, we can build the
SQL.

Thanks in advance
--
H. Frank Situmorang

John Spencer said:

Duane,
I thought by adding the Ranking that I would get multiple lines for each
position in the Crosstab query. OR did I miss something with that.

Year Position Name RankOrder
2007 Deacon Bob 1
2007 Deacon Tom 2
2007 Deacon Zeara 3
2008 Deacon Bob 1
2008 Deacon Zeara 2

Then crosstab would return
Position Rank 2007 2008
Deacon 1 Bob Bob
Deacon 2 Tom Zeara
Decaon 3 Zeara (Null)

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

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

:

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
..

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

Click to expand...

Click to expand...

Duane Hookom · Feb 26, 2008

You missed some of John's suggestion. The PelayanJemaat table should be added
to the query twice.

Try something like:
SELECT PelayanJemaat.TahunPel, PelayanJemaat.BidangPelayanan,
PelayanJemaat.NamaPel, Count(B.BidangPelayanan) AS
CountOfBidangPelayanan
FROM PelayanJemaat LEFT JOIN PelayanJemaat B ON
(PelayanJemaat.TahunPel=B.TahunPel) AND
(PelayanJemaat.BidangPelayanan=B.BidangPelayanan) AND
(PelayanJemaat.NamaPel<B.NamaPel)
GROUP BY PelayanJemaat.TahunPel, PelayanJemaat.BidangPelayanan,
PelayanJemaat.NamaPel;

--
Duane Hookom
Microsoft Access MVP

Frank Situmorang said:
John, when I followed step by step procedures like you suggested, I still do
not know how to make it in rank order and how to make a transform....

This is my SQL when I followed your suggestion.
SELECT PelayanJemaat.TahunPel, PelayanJemaat.BidangPelayanan,
PelayanJemaat.NamaPel, Count(PelayanJemaat2.BidangPelayanan) AS
CountOfBidangPelayanan
FROM PelayanJemaat LEFT JOIN PelayanJemaat2 ON
(PelayanJemaat.TahunPel=PelayanJemaat2.TahunPel) AND
(PelayanJemaat.BidangPelayanan=PelayanJemaat2.BidangPelayanan) AND
(PelayanJemaat.NamaPel<PelayanJemaat2.NamaPel)
GROUP BY PelayanJemaat.TahunPel, PelayanJemaat.BidangPelayanan,
PelayanJemaat.NamaPel;

Thanks for anybody's idea to solve this problem
--
H. Frank Situmorang

John Spencer said:

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

Open a new query
-- Add your table to the query two times (I'll call those two - Table 1 and
Table 2)
-- Drag from the Year field in table 1 to the year field in table 2
-- Drag from the position field in table 1 to the position field in table 2
-- Drag from the Name field in table 1 to the name field in table 2
(That should set up three join lines) between the tables
-- Add the fields year, position, and name from table 1 to the grid
-- Add the position field from table 2 to the grid
-- SELECT View: Totals from the menu
-- Change GROUP BY to Count under table 2 position field
-- SELECT View: SQL View from the menu
In the text that is now visible,
-- Change INNER JOIN to LEFT JOIN
-- Change table1.Name field = Table2.Name Field to
table1.Name field < Table2.Name Field

Try to run the query. If it runs, then save it and use that as the source
of a crosstab query. IF you have problems with the crosstab query, post
back with any error messages, so someone can help you.

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

Frank Situmorang said:

John,

I want to use your suggestion, but can you explain again on how can we
make
the query you said ( SQL) and then how can we make the TRANSFOR and
Pivot. I
appreciate your help on how can we build that query. Normally I can use
grid
query and also someone told me in this thread how to make SQL but we must
in
in the form first and then in the data record source, we can build the
SQL.

Thanks in advance
--
H. Frank Situmorang

:

Duane,
I thought by adding the Ranking that I would get multiple lines for each
position in the Crosstab query. OR did I miss something with that.

Year Position Name RankOrder
2007 Deacon Bob 1
2007 Deacon Tom 2
2007 Deacon Zeara 3
2008 Deacon Bob 1
2008 Deacon Zeara 2

Then crosstab would return
Position Rank 2007 2008
Deacon 1 Bob Bob
Deacon 2 Tom Zeara
Decaon 3 Zeara (Null)

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

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

:

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
..

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

Click to expand...

Click to expand...

Frank Situmorang · Feb 26, 2008

Thanks Duane, the query now works now, but how can we make it into
crosstabquery so that it shows name under the year ( TahunPel/Year of service)

I appreciate your help. this is the only thing left, and writing something
like simple user manual, then we can go live.

--
H. Frank Situmorang

Duane Hookom said:
You missed some of John's suggestion. The PelayanJemaat table should be added
to the query twice.

Try something like:
SELECT PelayanJemaat.TahunPel, PelayanJemaat.BidangPelayanan,
PelayanJemaat.NamaPel, Count(B.BidangPelayanan) AS
CountOfBidangPelayanan
FROM PelayanJemaat LEFT JOIN PelayanJemaat B ON
(PelayanJemaat.TahunPel=B.TahunPel) AND
(PelayanJemaat.BidangPelayanan=B.BidangPelayanan) AND
(PelayanJemaat.NamaPel<B.NamaPel)
GROUP BY PelayanJemaat.TahunPel, PelayanJemaat.BidangPelayanan,
PelayanJemaat.NamaPel;

--
Duane Hookom
Microsoft Access MVP

Frank Situmorang said:

John, when I followed step by step procedures like you suggested, I still do
not know how to make it in rank order and how to make a transform....

This is my SQL when I followed your suggestion.
SELECT PelayanJemaat.TahunPel, PelayanJemaat.BidangPelayanan,
PelayanJemaat.NamaPel, Count(PelayanJemaat2.BidangPelayanan) AS
CountOfBidangPelayanan
FROM PelayanJemaat LEFT JOIN PelayanJemaat2 ON
(PelayanJemaat.TahunPel=PelayanJemaat2.TahunPel) AND
(PelayanJemaat.BidangPelayanan=PelayanJemaat2.BidangPelayanan) AND
(PelayanJemaat.NamaPel<PelayanJemaat2.NamaPel)
GROUP BY PelayanJemaat.TahunPel, PelayanJemaat.BidangPelayanan,
PelayanJemaat.NamaPel;

Thanks for anybody's idea to solve this problem
--
H. Frank Situmorang

John Spencer said:

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

Open a new query
-- Add your table to the query two times (I'll call those two - Table 1 and
Table 2)
-- Drag from the Year field in table 1 to the year field in table 2
-- Drag from the position field in table 1 to the position field in table 2
-- Drag from the Name field in table 1 to the name field in table 2
(That should set up three join lines) between the tables
-- Add the fields year, position, and name from table 1 to the grid
-- Add the position field from table 2 to the grid
-- SELECT View: Totals from the menu
-- Change GROUP BY to Count under table 2 position field
-- SELECT View: SQL View from the menu
In the text that is now visible,
-- Change INNER JOIN to LEFT JOIN
-- Change table1.Name field = Table2.Name Field to
table1.Name field < Table2.Name Field

Try to run the query. If it runs, then save it and use that as the source
of a crosstab query. IF you have problems with the crosstab query, post
back with any error messages, so someone can help you.

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

John,

I want to use your suggestion, but can you explain again on how can we
make
the query you said ( SQL) and then how can we make the TRANSFOR and
Pivot. I
appreciate your help on how can we build that query. Normally I can use
grid
query and also someone told me in this thread how to make SQL but we must
in
in the form first and then in the data record source, we can build the
SQL.

Thanks in advance
--
H. Frank Situmorang

:

Duane,
I thought by adding the Ranking that I would get multiple lines for each
position in the Crosstab query. OR did I miss something with that.

Year Position Name RankOrder
2007 Deacon Bob 1
2007 Deacon Tom 2
2007 Deacon Zeara 3
2008 Deacon Bob 1
2008 Deacon Zeara 2

Then crosstab would return
Position Rank 2007 2008
Deacon 1 Bob Bob
Deacon 2 Tom Zeara
Decaon 3 Zeara (Null)

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

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

:

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
..

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

Click to expand...

Click to expand...

Duane Hookom · Feb 26, 2008

Frank,
Go back to John's first post or my example of the crosstab and apply the
solution to your recent query. Use the CountOfBidangPelayanan as the the
Column Heading. The value would be the first of your name field.

--
Duane Hookom
Microsoft Access MVP

Frank Situmorang said:
Thanks Duane, the query now works now, but how can we make it into
crosstabquery so that it shows name under the year ( TahunPel/Year of service)

I appreciate your help. this is the only thing left, and writing something
like simple user manual, then we can go live.

--
H. Frank Situmorang

Duane Hookom said:

You missed some of John's suggestion. The PelayanJemaat table should be added
to the query twice.

Try something like:
SELECT PelayanJemaat.TahunPel, PelayanJemaat.BidangPelayanan,
PelayanJemaat.NamaPel, Count(B.BidangPelayanan) AS
CountOfBidangPelayanan
FROM PelayanJemaat LEFT JOIN PelayanJemaat B ON
(PelayanJemaat.TahunPel=B.TahunPel) AND
(PelayanJemaat.BidangPelayanan=B.BidangPelayanan) AND
(PelayanJemaat.NamaPel<B.NamaPel)
GROUP BY PelayanJemaat.TahunPel, PelayanJemaat.BidangPelayanan,
PelayanJemaat.NamaPel;

--
Duane Hookom
Microsoft Access MVP

Frank Situmorang said:

John, when I followed step by step procedures like you suggested, I still do
not know how to make it in rank order and how to make a transform....

This is my SQL when I followed your suggestion.
SELECT PelayanJemaat.TahunPel, PelayanJemaat.BidangPelayanan,
PelayanJemaat.NamaPel, Count(PelayanJemaat2.BidangPelayanan) AS
CountOfBidangPelayanan
FROM PelayanJemaat LEFT JOIN PelayanJemaat2 ON
(PelayanJemaat.TahunPel=PelayanJemaat2.TahunPel) AND
(PelayanJemaat.BidangPelayanan=PelayanJemaat2.BidangPelayanan) AND
(PelayanJemaat.NamaPel<PelayanJemaat2.NamaPel)
GROUP BY PelayanJemaat.TahunPel, PelayanJemaat.BidangPelayanan,
PelayanJemaat.NamaPel;

Thanks for anybody's idea to solve this problem
--
H. Frank Situmorang

:

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

Open a new query
-- Add your table to the query two times (I'll call those two - Table 1 and
Table 2)
-- Drag from the Year field in table 1 to the year field in table 2
-- Drag from the position field in table 1 to the position field in table 2
-- Drag from the Name field in table 1 to the name field in table 2
(That should set up three join lines) between the tables
-- Add the fields year, position, and name from table 1 to the grid
-- Add the position field from table 2 to the grid
-- SELECT View: Totals from the menu
-- Change GROUP BY to Count under table 2 position field
-- SELECT View: SQL View from the menu
In the text that is now visible,
-- Change INNER JOIN to LEFT JOIN
-- Change table1.Name field = Table2.Name Field to
table1.Name field < Table2.Name Field

Try to run the query. If it runs, then save it and use that as the source
of a crosstab query. IF you have problems with the crosstab query, post
back with any error messages, so someone can help you.

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

John,

I want to use your suggestion, but can you explain again on how can we
make
the query you said ( SQL) and then how can we make the TRANSFOR and
Pivot. I
appreciate your help on how can we build that query. Normally I can use
grid
query and also someone told me in this thread how to make SQL but we must
in
in the form first and then in the data record source, we can build the
SQL.

Thanks in advance
--
H. Frank Situmorang

:

Duane,
I thought by adding the Ranking that I would get multiple lines for each
position in the Crosstab query. OR did I miss something with that.

Year Position Name RankOrder
2007 Deacon Bob 1
2007 Deacon Tom 2
2007 Deacon Zeara 3
2008 Deacon Bob 1
2008 Deacon Zeara 2

Then crosstab would return
Position Rank 2007 2008
Deacon 1 Bob Bob
Deacon 2 Tom Zeara
Decaon 3 Zeara (Null)

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

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

:

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
..

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

Click to expand...

Click to expand...

Frank Situmorang · Feb 27, 2008

Ok John and Duane,

The query works now to have the year sideway, but the sequance of the
position is not like what I expected.

We need to that in my church officers table, I have the Position sequece in
order to present as follows:

Seq.No Posision Name
1. First Elder Mr. A
2. Elders Mr. B
Mr. C
Mr. D
3. Dean of Deacon Mr.E
4. Deacons Mr. F
Mr. G
Mr. H.

How can we make it the position is in sequence like that

Thanks in advance

--
H. Frank Situmorang

Duane Hookom said:
You missed some of John's suggestion. The PelayanJemaat table should be added
to the query twice.

Try something like:
SELECT PelayanJemaat.TahunPel, PelayanJemaat.BidangPelayanan,
PelayanJemaat.NamaPel, Count(B.BidangPelayanan) AS
CountOfBidangPelayanan
FROM PelayanJemaat LEFT JOIN PelayanJemaat B ON
(PelayanJemaat.TahunPel=B.TahunPel) AND
(PelayanJemaat.BidangPelayanan=B.BidangPelayanan) AND
(PelayanJemaat.NamaPel<B.NamaPel)
GROUP BY PelayanJemaat.TahunPel, PelayanJemaat.BidangPelayanan,
PelayanJemaat.NamaPel;

--
Duane Hookom
Microsoft Access MVP

Frank Situmorang said:

John, when I followed step by step procedures like you suggested, I still do
not know how to make it in rank order and how to make a transform....

This is my SQL when I followed your suggestion.
SELECT PelayanJemaat.TahunPel, PelayanJemaat.BidangPelayanan,
PelayanJemaat.NamaPel, Count(PelayanJemaat2.BidangPelayanan) AS
CountOfBidangPelayanan
FROM PelayanJemaat LEFT JOIN PelayanJemaat2 ON
(PelayanJemaat.TahunPel=PelayanJemaat2.TahunPel) AND
(PelayanJemaat.BidangPelayanan=PelayanJemaat2.BidangPelayanan) AND
(PelayanJemaat.NamaPel<PelayanJemaat2.NamaPel)
GROUP BY PelayanJemaat.TahunPel, PelayanJemaat.BidangPelayanan,
PelayanJemaat.NamaPel;

Thanks for anybody's idea to solve this problem
--
H. Frank Situmorang

John Spencer said:

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

Open a new query
-- Add your table to the query two times (I'll call those two - Table 1 and
Table 2)
-- Drag from the Year field in table 1 to the year field in table 2
-- Drag from the position field in table 1 to the position field in table 2
-- Drag from the Name field in table 1 to the name field in table 2
(That should set up three join lines) between the tables
-- Add the fields year, position, and name from table 1 to the grid
-- Add the position field from table 2 to the grid
-- SELECT View: Totals from the menu
-- Change GROUP BY to Count under table 2 position field
-- SELECT View: SQL View from the menu
In the text that is now visible,
-- Change INNER JOIN to LEFT JOIN
-- Change table1.Name field = Table2.Name Field to
table1.Name field < Table2.Name Field

Try to run the query. If it runs, then save it and use that as the source
of a crosstab query. IF you have problems with the crosstab query, post
back with any error messages, so someone can help you.

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

John,

I want to use your suggestion, but can you explain again on how can we
make
the query you said ( SQL) and then how can we make the TRANSFOR and
Pivot. I
appreciate your help on how can we build that query. Normally I can use
grid
query and also someone told me in this thread how to make SQL but we must
in
in the form first and then in the data record source, we can build the
SQL.

Thanks in advance
--
H. Frank Situmorang

:

Duane,
I thought by adding the Ranking that I would get multiple lines for each
position in the Crosstab query. OR did I miss something with that.

Year Position Name RankOrder
2007 Deacon Bob 1
2007 Deacon Tom 2
2007 Deacon Zeara 3
2008 Deacon Bob 1
2008 Deacon Zeara 2

Then crosstab would return
Position Rank 2007 2008
Deacon 1 Bob Bob
Deacon 2 Tom Zeara
Decaon 3 Zeara (Null)

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

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

:

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
..

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

Click to expand...

Click to expand...

John Spencer · Feb 27, 2008

If you mean you want the positions to appear in specific order then you will
need to sort by the column that contains Seq No. Add an ORDER BY clause to
your query.

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

Frank Situmorang said:
Ok John and Duane,

The query works now to have the year sideway, but the sequance of the
position is not like what I expected.

We need to that in my church officers table, I have the Position sequece
in
order to present as follows:

Seq.No Posision Name
1. First Elder Mr. A
2. Elders Mr. B
Mr. C
Mr. D
3. Dean of Deacon Mr.E
4. Deacons Mr. F
Mr. G
Mr. H.

How can we make it the position is in sequence like that

Thanks in advance

--
H. Frank Situmorang

Duane Hookom said:

You missed some of John's suggestion. The PelayanJemaat table should be
added
to the query twice.

Try something like:
SELECT PelayanJemaat.TahunPel, PelayanJemaat.BidangPelayanan,
PelayanJemaat.NamaPel, Count(B.BidangPelayanan) AS
CountOfBidangPelayanan
FROM PelayanJemaat LEFT JOIN PelayanJemaat B ON
(PelayanJemaat.TahunPel=B.TahunPel) AND
(PelayanJemaat.BidangPelayanan=B.BidangPelayanan) AND
(PelayanJemaat.NamaPel<B.NamaPel)
GROUP BY PelayanJemaat.TahunPel, PelayanJemaat.BidangPelayanan,
PelayanJemaat.NamaPel;

--
Duane Hookom
Microsoft Access MVP

Frank Situmorang said:

John, when I followed step by step procedures like you suggested, I
still do
not know how to make it in rank order and how to make a transform....

This is my SQL when I followed your suggestion.
SELECT PelayanJemaat.TahunPel, PelayanJemaat.BidangPelayanan,
PelayanJemaat.NamaPel, Count(PelayanJemaat2.BidangPelayanan) AS
CountOfBidangPelayanan
FROM PelayanJemaat LEFT JOIN PelayanJemaat2 ON
(PelayanJemaat.TahunPel=PelayanJemaat2.TahunPel) AND
(PelayanJemaat.BidangPelayanan=PelayanJemaat2.BidangPelayanan) AND
(PelayanJemaat.NamaPel<PelayanJemaat2.NamaPel)
GROUP BY PelayanJemaat.TahunPel, PelayanJemaat.BidangPelayanan,
PelayanJemaat.NamaPel;

Thanks for anybody's idea to solve this problem
--
H. Frank Situmorang

:

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

Open a new query
-- Add your table to the query two times (I'll call those two - Table
1 and
Table 2)
-- Drag from the Year field in table 1 to the year field in table 2
-- Drag from the position field in table 1 to the position field in
table 2
-- Drag from the Name field in table 1 to the name field in table 2
(That should set up three join lines) between the tables
-- Add the fields year, position, and name from table 1 to the grid
-- Add the position field from table 2 to the grid
-- SELECT View: Totals from the menu
-- Change GROUP BY to Count under table 2 position field
-- SELECT View: SQL View from the menu
In the text that is now visible,
-- Change INNER JOIN to LEFT JOIN
-- Change table1.Name field = Table2.Name Field to
table1.Name field < Table2.Name Field

Try to run the query. If it runs, then save it and use that as the
source
of a crosstab query. IF you have problems with the crosstab query,
post
back with any error messages, so someone can help you.

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

John,

I want to use your suggestion, but can you explain again on how can
we
make
the query you said ( SQL) and then how can we make the TRANSFOR
and
Pivot. I
appreciate your help on how can we build that query. Normally I can
use
grid
query and also someone told me in this thread how to make SQL but
we must
in
in the form first and then in the data record source, we can build
the
SQL.

Thanks in advance
--
H. Frank Situmorang

:

Duane,
I thought by adding the Ranking that I would get multiple lines
for each
position in the Crosstab query. OR did I miss something with
that.

Year Position Name RankOrder
2007 Deacon Bob 1
2007 Deacon Tom 2
2007 Deacon Zeara 3
2008 Deacon Bob 1
2008 Deacon Zeara 2

Then crosstab would return
Position Rank 2007 2008
Deacon 1 Bob Bob
Deacon 2 Tom Zeara
Decaon 3 Zeara (Null)

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

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

:

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
..

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

Click to expand...

Click to expand...

Frank Situmorang · Feb 28, 2008

Thanks John for your advice, The sequence already shows in sequeece but I do
not understand why each position only shows one record/one line

This is my SQL for Query:
SELECT PelayanJemaat.TahunPel, PelayanJemaat.NamaPel, PelayanJemaat.Nurut,
PelayanJemaat.BidangPelayanan, Count(PelayanJemaat_1.BidangPelayanan) AS
CountOfBidangPelayanan
FROM PelayanJemaat LEFT JOIN PelayanJemaat AS PelayanJemaat_1 ON
(PelayanJemaat.BidangPelayanan=PelayanJemaat_1.BidangPelayanan) AND
(PelayanJemaat.NamaPel<PelayanJemaat_1.NamaPel) AND
(PelayanJemaat.Nurut=PelayanJemaat_1.Nurut) AND
(PelayanJemaat.TahunPel=PelayanJemaat_1.TahunPel)
GROUP BY PelayanJemaat.TahunPel, PelayanJemaat.NamaPel, PelayanJemaat.Nurut,
PelayanJemaat.BidangPelayanan
ORDER BY PelayanJemaat.Nurut;

AND THIS IS MY SQL FOR CROSSTABQUERY:
TRANSFORM First(PelayanJemaatQrybyYear.NamaPel) AS FirstOfNamaPel
SELECT PelayanJemaatQrybyYear.Nurut, PelayanJemaatQrybyYear.BidangPelayanan,
First(PelayanJemaatQrybyYear.NamaPel) AS [Total Of NamaPel]
FROM PelayanJemaatQrybyYear
GROUP BY PelayanJemaatQrybyYear.Nurut, PelayanJemaatQrybyYear.BidangPelayanan
PIVOT PelayanJemaatQrybyYear.TahunPel;

Thanks for your help

--
H. Frank Situmorang

John Spencer said:
If you mean you want the positions to appear in specific order then you will
need to sort by the column that contains Seq No. Add an ORDER BY clause to
your query.

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

Frank Situmorang said:

Ok John and Duane,

The query works now to have the year sideway, but the sequance of the
position is not like what I expected.

We need to that in my church officers table, I have the Position sequece
in
order to present as follows:

Seq.No Posision Name
1. First Elder Mr. A
2. Elders Mr. B
Mr. C
Mr. D
3. Dean of Deacon Mr.E
4. Deacons Mr. F
Mr. G
Mr. H.

How can we make it the position is in sequence like that

Thanks in advance

--
H. Frank Situmorang

Duane Hookom said:

You missed some of John's suggestion. The PelayanJemaat table should be
added
to the query twice.

Try something like:
SELECT PelayanJemaat.TahunPel, PelayanJemaat.BidangPelayanan,
PelayanJemaat.NamaPel, Count(B.BidangPelayanan) AS
CountOfBidangPelayanan
FROM PelayanJemaat LEFT JOIN PelayanJemaat B ON
(PelayanJemaat.TahunPel=B.TahunPel) AND
(PelayanJemaat.BidangPelayanan=B.BidangPelayanan) AND
(PelayanJemaat.NamaPel<B.NamaPel)
GROUP BY PelayanJemaat.TahunPel, PelayanJemaat.BidangPelayanan,
PelayanJemaat.NamaPel;

--
Duane Hookom
Microsoft Access MVP

:

John, when I followed step by step procedures like you suggested, I
still do
not know how to make it in rank order and how to make a transform....

This is my SQL when I followed your suggestion.
SELECT PelayanJemaat.TahunPel, PelayanJemaat.BidangPelayanan,
PelayanJemaat.NamaPel, Count(PelayanJemaat2.BidangPelayanan) AS
CountOfBidangPelayanan
FROM PelayanJemaat LEFT JOIN PelayanJemaat2 ON
(PelayanJemaat.TahunPel=PelayanJemaat2.TahunPel) AND
(PelayanJemaat.BidangPelayanan=PelayanJemaat2.BidangPelayanan) AND
(PelayanJemaat.NamaPel<PelayanJemaat2.NamaPel)
GROUP BY PelayanJemaat.TahunPel, PelayanJemaat.BidangPelayanan,
PelayanJemaat.NamaPel;

Thanks for anybody's idea to solve this problem
--
H. Frank Situmorang

:

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

Open a new query
-- Add your table to the query two times (I'll call those two - Table
1 and
Table 2)
-- Drag from the Year field in table 1 to the year field in table 2
-- Drag from the position field in table 1 to the position field in
table 2
-- Drag from the Name field in table 1 to the name field in table 2
(That should set up three join lines) between the tables
-- Add the fields year, position, and name from table 1 to the grid
-- Add the position field from table 2 to the grid
-- SELECT View: Totals from the menu
-- Change GROUP BY to Count under table 2 position field
-- SELECT View: SQL View from the menu
In the text that is now visible,
-- Change INNER JOIN to LEFT JOIN
-- Change table1.Name field = Table2.Name Field to
table1.Name field < Table2.Name Field

Try to run the query. If it runs, then save it and use that as the
source
of a crosstab query. IF you have problems with the crosstab query,
post
back with any error messages, so someone can help you.

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

John,

I want to use your suggestion, but can you explain again on how can
we
make
the query you said ( SQL) and then how can we make the TRANSFOR
and
Pivot. I
appreciate your help on how can we build that query. Normally I can
use
grid
query and also someone told me in this thread how to make SQL but
we must
in
in the form first and then in the data record source, we can build
the
SQL.

Thanks in advance
--
H. Frank Situmorang

:

Duane,
I thought by adding the Ranking that I would get multiple lines
for each
position in the Crosstab query. OR did I miss something with
that.

Year Position Name RankOrder
2007 Deacon Bob 1
2007 Deacon Tom 2
2007 Deacon Zeara 3
2008 Deacon Bob 1
2008 Deacon Zeara 2

Then crosstab would return
Position Rank 2007 2008
Deacon 1 Bob Bob
Deacon 2 Tom Zeara
Decaon 3 Zeara (Null)

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

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

:

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
..

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

Click to expand...

Click to expand...

Duane Hookom · Feb 28, 2008

It's a bit difficult to understand your SQL since most of us don't understand
your field names. I think your issue might be that you didn't follow my
instruction a while back to "Use the CountOfBidangPelayanan as the the Column
Heading." I don't know what your column heading field "TahunPel" stores.
--
Duane Hookom
Microsoft Access MVP

Frank Situmorang said:
Thanks John for your advice, The sequence already shows in sequeece but I do
not understand why each position only shows one record/one line

This is my SQL for Query:
SELECT PelayanJemaat.TahunPel, PelayanJemaat.NamaPel, PelayanJemaat.Nurut,
PelayanJemaat.BidangPelayanan, Count(PelayanJemaat_1.BidangPelayanan) AS
CountOfBidangPelayanan
FROM PelayanJemaat LEFT JOIN PelayanJemaat AS PelayanJemaat_1 ON
(PelayanJemaat.BidangPelayanan=PelayanJemaat_1.BidangPelayanan) AND
(PelayanJemaat.NamaPel<PelayanJemaat_1.NamaPel) AND
(PelayanJemaat.Nurut=PelayanJemaat_1.Nurut) AND
(PelayanJemaat.TahunPel=PelayanJemaat_1.TahunPel)
GROUP BY PelayanJemaat.TahunPel, PelayanJemaat.NamaPel, PelayanJemaat.Nurut,
PelayanJemaat.BidangPelayanan
ORDER BY PelayanJemaat.Nurut;

AND THIS IS MY SQL FOR CROSSTABQUERY:
TRANSFORM First(PelayanJemaatQrybyYear.NamaPel) AS FirstOfNamaPel
SELECT PelayanJemaatQrybyYear.Nurut, PelayanJemaatQrybyYear.BidangPelayanan,
First(PelayanJemaatQrybyYear.NamaPel) AS [Total Of NamaPel]
FROM PelayanJemaatQrybyYear
GROUP BY PelayanJemaatQrybyYear.Nurut, PelayanJemaatQrybyYear.BidangPelayanan
PIVOT PelayanJemaatQrybyYear.TahunPel;

Thanks for your help

--
H. Frank Situmorang

John Spencer said:

If you mean you want the positions to appear in specific order then you will
need to sort by the column that contains Seq No. Add an ORDER BY clause to
your query.

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

Frank Situmorang said:

Ok John and Duane,

The query works now to have the year sideway, but the sequance of the
position is not like what I expected.

We need to that in my church officers table, I have the Position sequece
in
order to present as follows:

Seq.No Posision Name
1. First Elder Mr. A
2. Elders Mr. B
Mr. C
Mr. D
3. Dean of Deacon Mr.E
4. Deacons Mr. F
Mr. G
Mr. H.

How can we make it the position is in sequence like that

Thanks in advance

--
H. Frank Situmorang

:

You missed some of John's suggestion. The PelayanJemaat table should be
added
to the query twice.

Try something like:
SELECT PelayanJemaat.TahunPel, PelayanJemaat.BidangPelayanan,
PelayanJemaat.NamaPel, Count(B.BidangPelayanan) AS
CountOfBidangPelayanan
FROM PelayanJemaat LEFT JOIN PelayanJemaat B ON
(PelayanJemaat.TahunPel=B.TahunPel) AND
(PelayanJemaat.BidangPelayanan=B.BidangPelayanan) AND
(PelayanJemaat.NamaPel<B.NamaPel)
GROUP BY PelayanJemaat.TahunPel, PelayanJemaat.BidangPelayanan,
PelayanJemaat.NamaPel;

--
Duane Hookom
Microsoft Access MVP

:

John, when I followed step by step procedures like you suggested, I
still do
not know how to make it in rank order and how to make a transform....

This is my SQL when I followed your suggestion.
SELECT PelayanJemaat.TahunPel, PelayanJemaat.BidangPelayanan,
PelayanJemaat.NamaPel, Count(PelayanJemaat2.BidangPelayanan) AS
CountOfBidangPelayanan
FROM PelayanJemaat LEFT JOIN PelayanJemaat2 ON
(PelayanJemaat.TahunPel=PelayanJemaat2.TahunPel) AND
(PelayanJemaat.BidangPelayanan=PelayanJemaat2.BidangPelayanan) AND
(PelayanJemaat.NamaPel<PelayanJemaat2.NamaPel)
GROUP BY PelayanJemaat.TahunPel, PelayanJemaat.BidangPelayanan,
PelayanJemaat.NamaPel;

Thanks for anybody's idea to solve this problem
--
H. Frank Situmorang

:

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

Open a new query
-- Add your table to the query two times (I'll call those two - Table
1 and
Table 2)
-- Drag from the Year field in table 1 to the year field in table 2
-- Drag from the position field in table 1 to the position field in
table 2
-- Drag from the Name field in table 1 to the name field in table 2
(That should set up three join lines) between the tables
-- Add the fields year, position, and name from table 1 to the grid
-- Add the position field from table 2 to the grid
-- SELECT View: Totals from the menu
-- Change GROUP BY to Count under table 2 position field
-- SELECT View: SQL View from the menu
In the text that is now visible,
-- Change INNER JOIN to LEFT JOIN
-- Change table1.Name field = Table2.Name Field to
table1.Name field < Table2.Name Field

Try to run the query. If it runs, then save it and use that as the
source
of a crosstab query. IF you have problems with the crosstab query,
post
back with any error messages, so someone can help you.

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

John,

I want to use your suggestion, but can you explain again on how can
we
make
the query you said ( SQL) and then how can we make the TRANSFOR
and
Pivot. I
appreciate your help on how can we build that query. Normally I can
use
grid
query and also someone told me in this thread how to make SQL but
we must
in
in the form first and then in the data record source, we can build
the
SQL.

Thanks in advance
--
H. Frank Situmorang

:

Duane,
I thought by adding the Ranking that I would get multiple lines
for each
position in the Crosstab query. OR did I miss something with
that.

Year Position Name RankOrder
2007 Deacon Bob 1
2007 Deacon Tom 2
2007 Deacon Zeara 3
2008 Deacon Bob 1
2008 Deacon Zeara 2

Then crosstab would return
Position Rank 2007 2008
Deacon 1 Bob Bob
Deacon 2 Tom Zeara
Decaon 3 Zeara (Null)

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

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

:

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
..

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

Click to expand...

Click to expand...

Frank Situmorang · Feb 28, 2008

Sorry Duane, Tahunpel is year of service or we can say Year, I just do not
now which one is columnheading because it didn't show column heading like in
the grids, but is it Pivot what you mean column heading?, I have tried
bidangpelayanan or in english means fieldposition as the columnheading, but
it will not show the year ub the column result.

Do I miss something?, sorry for the language problem, if I put full in
English, the people in Indonesia will not understand when I explain it to
them.

We appreciate if you could explain again.

--
H. Frank Situmorang

Duane Hookom said:
It's a bit difficult to understand your SQL since most of us don't understand
your field names. I think your issue might be that you didn't follow my
instruction a while back to "Use the CountOfBidangPelayanan as the the Column
Heading." I don't know what your column heading field "TahunPel" stores.
--
Duane Hookom
Microsoft Access MVP

Frank Situmorang said:

Thanks John for your advice, The sequence already shows in sequeece but I do
not understand why each position only shows one record/one line

This is my SQL for Query:
SELECT PelayanJemaat.TahunPel, PelayanJemaat.NamaPel, PelayanJemaat.Nurut,
PelayanJemaat.BidangPelayanan, Count(PelayanJemaat_1.BidangPelayanan) AS
CountOfBidangPelayanan
FROM PelayanJemaat LEFT JOIN PelayanJemaat AS PelayanJemaat_1 ON
(PelayanJemaat.BidangPelayanan=PelayanJemaat_1.BidangPelayanan) AND
(PelayanJemaat.NamaPel<PelayanJemaat_1.NamaPel) AND
(PelayanJemaat.Nurut=PelayanJemaat_1.Nurut) AND
(PelayanJemaat.TahunPel=PelayanJemaat_1.TahunPel)
GROUP BY PelayanJemaat.TahunPel, PelayanJemaat.NamaPel, PelayanJemaat.Nurut,
PelayanJemaat.BidangPelayanan
ORDER BY PelayanJemaat.Nurut;

AND THIS IS MY SQL FOR CROSSTABQUERY:
TRANSFORM First(PelayanJemaatQrybyYear.NamaPel) AS FirstOfNamaPel
SELECT PelayanJemaatQrybyYear.Nurut, PelayanJemaatQrybyYear.BidangPelayanan,
First(PelayanJemaatQrybyYear.NamaPel) AS [Total Of NamaPel]
FROM PelayanJemaatQrybyYear
GROUP BY PelayanJemaatQrybyYear.Nurut, PelayanJemaatQrybyYear.BidangPelayanan
PIVOT PelayanJemaatQrybyYear.TahunPel;

Thanks for your help

--
H. Frank Situmorang

John Spencer said:

If you mean you want the positions to appear in specific order then you will
need to sort by the column that contains Seq No. Add an ORDER BY clause to
your query.

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

Ok John and Duane,

The query works now to have the year sideway, but the sequance of the
position is not like what I expected.

We need to that in my church officers table, I have the Position sequece
in
order to present as follows:

Seq.No Posision Name
1. First Elder Mr. A
2. Elders Mr. B
Mr. C
Mr. D
3. Dean of Deacon Mr.E
4. Deacons Mr. F
Mr. G
Mr. H.

How can we make it the position is in sequence like that

Thanks in advance

--
H. Frank Situmorang

:

You missed some of John's suggestion. The PelayanJemaat table should be
added
to the query twice.

Try something like:
SELECT PelayanJemaat.TahunPel, PelayanJemaat.BidangPelayanan,
PelayanJemaat.NamaPel, Count(B.BidangPelayanan) AS
CountOfBidangPelayanan
FROM PelayanJemaat LEFT JOIN PelayanJemaat B ON
(PelayanJemaat.TahunPel=B.TahunPel) AND
(PelayanJemaat.BidangPelayanan=B.BidangPelayanan) AND
(PelayanJemaat.NamaPel<B.NamaPel)
GROUP BY PelayanJemaat.TahunPel, PelayanJemaat.BidangPelayanan,
PelayanJemaat.NamaPel;

--
Duane Hookom
Microsoft Access MVP

:

John, when I followed step by step procedures like you suggested, I
still do
not know how to make it in rank order and how to make a transform....

This is my SQL when I followed your suggestion.
SELECT PelayanJemaat.TahunPel, PelayanJemaat.BidangPelayanan,
PelayanJemaat.NamaPel, Count(PelayanJemaat2.BidangPelayanan) AS
CountOfBidangPelayanan
FROM PelayanJemaat LEFT JOIN PelayanJemaat2 ON
(PelayanJemaat.TahunPel=PelayanJemaat2.TahunPel) AND
(PelayanJemaat.BidangPelayanan=PelayanJemaat2.BidangPelayanan) AND
(PelayanJemaat.NamaPel<PelayanJemaat2.NamaPel)
GROUP BY PelayanJemaat.TahunPel, PelayanJemaat.BidangPelayanan,
PelayanJemaat.NamaPel;

Thanks for anybody's idea to solve this problem
--
H. Frank Situmorang

:

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

Open a new query
-- Add your table to the query two times (I'll call those two - Table
1 and
Table 2)
-- Drag from the Year field in table 1 to the year field in table 2
-- Drag from the position field in table 1 to the position field in
table 2
-- Drag from the Name field in table 1 to the name field in table 2
(That should set up three join lines) between the tables
-- Add the fields year, position, and name from table 1 to the grid
-- Add the position field from table 2 to the grid
-- SELECT View: Totals from the menu
-- Change GROUP BY to Count under table 2 position field
-- SELECT View: SQL View from the menu
In the text that is now visible,
-- Change INNER JOIN to LEFT JOIN
-- Change table1.Name field = Table2.Name Field to
table1.Name field < Table2.Name Field

Try to run the query. If it runs, then save it and use that as the
source
of a crosstab query. IF you have problems with the crosstab query,
post
back with any error messages, so someone can help you.

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

John,

I want to use your suggestion, but can you explain again on how can
we
make
the query you said ( SQL) and then how can we make the TRANSFOR
and
Pivot. I
appreciate your help on how can we build that query. Normally I can
use
grid
query and also someone told me in this thread how to make SQL but
we must
in
in the form first and then in the data record source, we can build
the
SQL.

Thanks in advance
--
H. Frank Situmorang

:

Duane,
I thought by adding the Ranking that I would get multiple lines
for each
position in the Crosstab query. OR did I miss something with
that.

Year Position Name RankOrder
2007 Deacon Bob 1
2007 Deacon Tom 2
2007 Deacon Zeara 3
2008 Deacon Bob 1
2008 Deacon Zeara 2

Then crosstab would return
Position Rank 2007 2008
Deacon 1 Bob Bob
Deacon 2 Tom Zeara
Decaon 3 Zeara (Null)

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

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

:

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
..

Hello,

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

Normal report based on the normal query:

Click to expand...

Click to expand...

Duane Hookom · Feb 28, 2008

I think you want the Year and the Position as Row Headings, the CountOf... as
the Column Heading, and first of the name field as the value.
--
Duane Hookom
Microsoft Access MVP

Frank Situmorang said:
Sorry Duane, Tahunpel is year of service or we can say Year, I just do not
now which one is columnheading because it didn't show column heading like in
the grids, but is it Pivot what you mean column heading?, I have tried
bidangpelayanan or in english means fieldposition as the columnheading, but
it will not show the year ub the column result.

Do I miss something?, sorry for the language problem, if I put full in
English, the people in Indonesia will not understand when I explain it to
them.

We appreciate if you could explain again.

--
H. Frank Situmorang

Duane Hookom said:

It's a bit difficult to understand your SQL since most of us don't understand
your field names. I think your issue might be that you didn't follow my
instruction a while back to "Use the CountOfBidangPelayanan as the the Column
Heading." I don't know what your column heading field "TahunPel" stores.
--
Duane Hookom
Microsoft Access MVP

Frank Situmorang said:

Thanks John for your advice, The sequence already shows in sequeece but I do
not understand why each position only shows one record/one line

This is my SQL for Query:
SELECT PelayanJemaat.TahunPel, PelayanJemaat.NamaPel, PelayanJemaat.Nurut,
PelayanJemaat.BidangPelayanan, Count(PelayanJemaat_1.BidangPelayanan) AS
CountOfBidangPelayanan
FROM PelayanJemaat LEFT JOIN PelayanJemaat AS PelayanJemaat_1 ON
(PelayanJemaat.BidangPelayanan=PelayanJemaat_1.BidangPelayanan) AND
(PelayanJemaat.NamaPel<PelayanJemaat_1.NamaPel) AND
(PelayanJemaat.Nurut=PelayanJemaat_1.Nurut) AND
(PelayanJemaat.TahunPel=PelayanJemaat_1.TahunPel)
GROUP BY PelayanJemaat.TahunPel, PelayanJemaat.NamaPel, PelayanJemaat.Nurut,
PelayanJemaat.BidangPelayanan
ORDER BY PelayanJemaat.Nurut;

AND THIS IS MY SQL FOR CROSSTABQUERY:
TRANSFORM First(PelayanJemaatQrybyYear.NamaPel) AS FirstOfNamaPel
SELECT PelayanJemaatQrybyYear.Nurut, PelayanJemaatQrybyYear.BidangPelayanan,
First(PelayanJemaatQrybyYear.NamaPel) AS [Total Of NamaPel]
FROM PelayanJemaatQrybyYear
GROUP BY PelayanJemaatQrybyYear.Nurut, PelayanJemaatQrybyYear.BidangPelayanan
PIVOT PelayanJemaatQrybyYear.TahunPel;

Thanks for your help

--
H. Frank Situmorang

:

If you mean you want the positions to appear in specific order then you will
need to sort by the column that contains Seq No. Add an ORDER BY clause to
your query.

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

Ok John and Duane,

The query works now to have the year sideway, but the sequance of the
position is not like what I expected.

We need to that in my church officers table, I have the Position sequece
in
order to present as follows:

Seq.No Posision Name
1. First Elder Mr. A
2. Elders Mr. B
Mr. C
Mr. D
3. Dean of Deacon Mr.E
4. Deacons Mr. F
Mr. G
Mr. H.

How can we make it the position is in sequence like that

Thanks in advance

--
H. Frank Situmorang

:

You missed some of John's suggestion. The PelayanJemaat table should be
added
to the query twice.

Try something like:
SELECT PelayanJemaat.TahunPel, PelayanJemaat.BidangPelayanan,
PelayanJemaat.NamaPel, Count(B.BidangPelayanan) AS
CountOfBidangPelayanan
FROM PelayanJemaat LEFT JOIN PelayanJemaat B ON
(PelayanJemaat.TahunPel=B.TahunPel) AND
(PelayanJemaat.BidangPelayanan=B.BidangPelayanan) AND
(PelayanJemaat.NamaPel<B.NamaPel)
GROUP BY PelayanJemaat.TahunPel, PelayanJemaat.BidangPelayanan,
PelayanJemaat.NamaPel;

--
Duane Hookom
Microsoft Access MVP

:

John, when I followed step by step procedures like you suggested, I
still do
not know how to make it in rank order and how to make a transform....

This is my SQL when I followed your suggestion.
SELECT PelayanJemaat.TahunPel, PelayanJemaat.BidangPelayanan,
PelayanJemaat.NamaPel, Count(PelayanJemaat2.BidangPelayanan) AS
CountOfBidangPelayanan
FROM PelayanJemaat LEFT JOIN PelayanJemaat2 ON
(PelayanJemaat.TahunPel=PelayanJemaat2.TahunPel) AND
(PelayanJemaat.BidangPelayanan=PelayanJemaat2.BidangPelayanan) AND
(PelayanJemaat.NamaPel<PelayanJemaat2.NamaPel)
GROUP BY PelayanJemaat.TahunPel, PelayanJemaat.BidangPelayanan,
PelayanJemaat.NamaPel;

Thanks for anybody's idea to solve this problem
--
H. Frank Situmorang

:

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

Open a new query
-- Add your table to the query two times (I'll call those two - Table
1 and
Table 2)
-- Drag from the Year field in table 1 to the year field in table 2
-- Drag from the position field in table 1 to the position field in
table 2
-- Drag from the Name field in table 1 to the name field in table 2
(That should set up three join lines) between the tables
-- Add the fields year, position, and name from table 1 to the grid
-- Add the position field from table 2 to the grid
-- SELECT View: Totals from the menu
-- Change GROUP BY to Count under table 2 position field
-- SELECT View: SQL View from the menu
In the text that is now visible,
-- Change INNER JOIN to LEFT JOIN
-- Change table1.Name field = Table2.Name Field to
table1.Name field < Table2.Name Field

Try to run the query. If it runs, then save it and use that as the
source
of a crosstab query. IF you have problems with the crosstab query,
post
back with any error messages, so someone can help you.

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

John,

I want to use your suggestion, but can you explain again on how can
we
make
the query you said ( SQL) and then how can we make the TRANSFOR
and
Pivot. I
appreciate your help on how can we build that query. Normally I can
use
grid
query and also someone told me in this thread how to make SQL but
we must
in
in the form first and then in the data record source, we can build
the
SQL.

Thanks in advance
--
H. Frank Situmorang

:

Duane,
I thought by adding the Ranking that I would get multiple lines
for each
position in the Crosstab query. OR did I miss something with
that.

Year Position Name RankOrder
2007 Deacon Bob 1
2007 Deacon Tom 2
2007 Deacon Zeara 3
2008 Deacon Bob 1
2008 Deacon Zeara 2

Then crosstab would return
Position Rank 2007 2008
Deacon 1 Bob Bob
Deacon 2 Tom Zeara
Decaon 3 Zeara (Null)

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

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

:

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.
--

Click to expand...

Click to expand...

Frank Situmorang · Feb 28, 2008

Duane, I have tried that, but the result is, the year is in the row, and in
the colum heading is number 1 number 2....... What I want is like in the
outlay that I said in my first thread.

It seems that I want to give up on this approach, maybe I could try the
approach you said on other threads, in the website:
http://www.tek-tips.com/faqs.cfm?fid=5466.

Is it maybe because in my table I have also field the member's ID?, which I
do not take it into account as it is for other purpose in the future?

Thanks for your helps

--
H. Frank Situmorang

Duane Hookom said:
I think you want the Year and the Position as Row Headings, the CountOf... as
the Column Heading, and first of the name field as the value.
--
Duane Hookom
Microsoft Access MVP

Frank Situmorang said:

Sorry Duane, Tahunpel is year of service or we can say Year, I just do not
now which one is columnheading because it didn't show column heading like in
the grids, but is it Pivot what you mean column heading?, I have tried
bidangpelayanan or in english means fieldposition as the columnheading, but
it will not show the year ub the column result.

Do I miss something?, sorry for the language problem, if I put full in
English, the people in Indonesia will not understand when I explain it to
them.

We appreciate if you could explain again.

--
H. Frank Situmorang

Duane Hookom said:

It's a bit difficult to understand your SQL since most of us don't understand
your field names. I think your issue might be that you didn't follow my
instruction a while back to "Use the CountOfBidangPelayanan as the the Column
Heading." I don't know what your column heading field "TahunPel" stores.
--
Duane Hookom
Microsoft Access MVP

:

Thanks John for your advice, The sequence already shows in sequeece but I do
not understand why each position only shows one record/one line

This is my SQL for Query:
SELECT PelayanJemaat.TahunPel, PelayanJemaat.NamaPel, PelayanJemaat.Nurut,
PelayanJemaat.BidangPelayanan, Count(PelayanJemaat_1.BidangPelayanan) AS
CountOfBidangPelayanan
FROM PelayanJemaat LEFT JOIN PelayanJemaat AS PelayanJemaat_1 ON
(PelayanJemaat.BidangPelayanan=PelayanJemaat_1.BidangPelayanan) AND
(PelayanJemaat.NamaPel<PelayanJemaat_1.NamaPel) AND
(PelayanJemaat.Nurut=PelayanJemaat_1.Nurut) AND
(PelayanJemaat.TahunPel=PelayanJemaat_1.TahunPel)
GROUP BY PelayanJemaat.TahunPel, PelayanJemaat.NamaPel, PelayanJemaat.Nurut,
PelayanJemaat.BidangPelayanan
ORDER BY PelayanJemaat.Nurut;

AND THIS IS MY SQL FOR CROSSTABQUERY:
TRANSFORM First(PelayanJemaatQrybyYear.NamaPel) AS FirstOfNamaPel
SELECT PelayanJemaatQrybyYear.Nurut, PelayanJemaatQrybyYear.BidangPelayanan,
First(PelayanJemaatQrybyYear.NamaPel) AS [Total Of NamaPel]
FROM PelayanJemaatQrybyYear
GROUP BY PelayanJemaatQrybyYear.Nurut, PelayanJemaatQrybyYear.BidangPelayanan
PIVOT PelayanJemaatQrybyYear.TahunPel;

Thanks for your help

--
H. Frank Situmorang

:

If you mean you want the positions to appear in specific order then you will
need to sort by the column that contains Seq No. Add an ORDER BY clause to
your query.

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

Ok John and Duane,

The query works now to have the year sideway, but the sequance of the
position is not like what I expected.

We need to that in my church officers table, I have the Position sequece
in
order to present as follows:

Seq.No Posision Name
1. First Elder Mr. A
2. Elders Mr. B
Mr. C
Mr. D
3. Dean of Deacon Mr.E
4. Deacons Mr. F
Mr. G
Mr. H.

How can we make it the position is in sequence like that

Thanks in advance

--
H. Frank Situmorang

:

You missed some of John's suggestion. The PelayanJemaat table should be
added
to the query twice.

Try something like:
SELECT PelayanJemaat.TahunPel, PelayanJemaat.BidangPelayanan,
PelayanJemaat.NamaPel, Count(B.BidangPelayanan) AS
CountOfBidangPelayanan
FROM PelayanJemaat LEFT JOIN PelayanJemaat B ON
(PelayanJemaat.TahunPel=B.TahunPel) AND
(PelayanJemaat.BidangPelayanan=B.BidangPelayanan) AND
(PelayanJemaat.NamaPel<B.NamaPel)
GROUP BY PelayanJemaat.TahunPel, PelayanJemaat.BidangPelayanan,
PelayanJemaat.NamaPel;

--
Duane Hookom
Microsoft Access MVP

:

John, when I followed step by step procedures like you suggested, I
still do
not know how to make it in rank order and how to make a transform....

This is my SQL when I followed your suggestion.
SELECT PelayanJemaat.TahunPel, PelayanJemaat.BidangPelayanan,
PelayanJemaat.NamaPel, Count(PelayanJemaat2.BidangPelayanan) AS
CountOfBidangPelayanan
FROM PelayanJemaat LEFT JOIN PelayanJemaat2 ON
(PelayanJemaat.TahunPel=PelayanJemaat2.TahunPel) AND
(PelayanJemaat.BidangPelayanan=PelayanJemaat2.BidangPelayanan) AND
(PelayanJemaat.NamaPel<PelayanJemaat2.NamaPel)
GROUP BY PelayanJemaat.TahunPel, PelayanJemaat.BidangPelayanan,
PelayanJemaat.NamaPel;

Thanks for anybody's idea to solve this problem
--
H. Frank Situmorang

:

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

Open a new query
-- Add your table to the query two times (I'll call those two - Table
1 and
Table 2)
-- Drag from the Year field in table 1 to the year field in table 2
-- Drag from the position field in table 1 to the position field in
table 2
-- Drag from the Name field in table 1 to the name field in table 2
(That should set up three join lines) between the tables
-- Add the fields year, position, and name from table 1 to the grid
-- Add the position field from table 2 to the grid
-- SELECT View: Totals from the menu
-- Change GROUP BY to Count under table 2 position field
-- SELECT View: SQL View from the menu
In the text that is now visible,
-- Change INNER JOIN to LEFT JOIN
-- Change table1.Name field = Table2.Name Field to
table1.Name field < Table2.Name Field

Try to run the query. If it runs, then save it and use that as the
source
of a crosstab query. IF you have problems with the crosstab query,
post
back with any error messages, so someone can help you.

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

John,

I want to use your suggestion, but can you explain again on how can
we
make
the query you said ( SQL) and then how can we make the TRANSFOR
and
Pivot. I
appreciate your help on how can we build that query. Normally I can
use
grid
query and also someone told me in this thread how to make SQL but
we must
in
in the form first and then in the data record source, we can build
the
SQL.

Thanks in advance
--
H. Frank Situmorang

:

Duane,
I thought by adding the Ranking that I would get multiple lines
for each
position in the Crosstab query. OR did I miss something with
that.

Year Position Name RankOrder
2007 Deacon Bob 1
2007 Deacon Tom 2
2007 Deacon Zeara 3
2008 Deacon Bob 1
2008 Deacon Zeara 2

Then crosstab would return
Position Rank 2007 2008
Deacon 1 Bob Bob
Deacon 2 Tom Zeara
Decaon 3 Zeara (Null)

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

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

:

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.

Click to expand...

Click to expand...