D
Duane Hookom
Looking back at your original posting, if you want the year as part of the
column name, use The Year and Count of fields combined as the Column Heading.
--
Duane Hookom
Microsoft Access MVP
column name, use The Year and Count of fields combined as the Column Heading.
--
Duane Hookom
Microsoft Access MVP
Frank Situmorang said: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
:
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