Dynamic column in report using crosstab query

[Frank Situmorang]

Thanks very much Duane, the result now is OK.

When I copy the SQL from your posting and I paste it to replace the
PelayanJemaatbyYear_Crosstab, I can not see it anymore in Grids.

Will it be a problem?, can we still import it to my churchbook database?,
because actually I will combine all the objects into my whole database.

Thanks verymuch for all your helps
--
H. Frank Situmorang

1. just re-arrange the columns in the report design. This is just a drag and
drop operation.
2. If you want more years, just change the Column Headings property of the
crosstab to add Yr2, Yr3, and Yr4.
3. Change the SQL of the crosstab to:
PARAMETERS [Forms]![frmTahundipilih]![cboEndYear] Short;
TRANSFORM First(Concatenate("SELECT NamaPel FROM PelayanJemaat WHERE
BidangPelayanan='" & [BidangPelayanan] & "' AND TahunPel =" & [TahunPel] & "
ORDER BY ID_pel",Chr(13) & Chr(10))) AS Expr2
SELECT PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan
FROM PelayanJemaat
GROUP BY PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan
PIVOT "Yr" & Forms!frmTahundipilih!cboEndYear-[TahunPel] In
("Yr0","Yr1","Yr2","Yr3","Yr4");

You can use many different expressions in the Concatenate() function such as

Concatenate("SELECT Fistr & ' ' & Middle & ' ' & [Last] FROM PelayanJemaat
...
--
Duane Hookom
Microsoft Access MVP

Thanks very much Duane, this a great help for us.

Can we modify it to have a result in the report as follows:

1. The current year/the latest will be the 1st column, such as
..2008..2007..2006
2. I will make it on the land scape paper format, so could we have it for 5
yrs?, because when we have the election again, we can see who is more
experience in the position
3. As we can see in the table there is an IDpel, in English something like a
record number, when we input it in the form. Can we have it ordered by
IDpel?, although we do not need to show it up on the report. Because when we
make election that is the order when we elected them. It have the effect on
the feeling of seniority or more higher in the level. example, like
Elder(Ketua) among the Ketua in 2008 election, Max Posumah should be above,
then go to Henry Panjaitan then to dusten Simalango, the record number
(IDpel) will indicate it.

The other question, what about the module of concatenate , will it
contradict with my other formula because in the other table which I did not
send you, I have the calculated field in the query which combine Fistr,
Middle, and Last name to have a full name of abreviation name.

Thanks for your help
--
H. Frank Situmorang

I imported the concatenate function from the sample mdb I had posted and make
a couple changes.

The result is available at
http://www.access.hookom.net/samples/churchofficer.zip.
--
Duane Hookom
Microsoft Access MVP


:

Duane,

The file now is in : http://www.savefile.com/files/1416593

I appreciate if your help.

--
H. Frank Situmorang


:

I have created and uploaded a sample of how I would create the report.
Download it from http://www.access.hookom.net/samples/crosstabnames.zip.
--
Duane Hookom
Microsoft Access MVP


:

I have deleted it Duane, let me try it again. I just read again the website
you gave me an tried to paralel it with mine.

You have skype or phone number Duane?, I will try to call you from my home
now using skype. Although normal telp, we can use the skype internet phone.
May be you could understand my broken English

Thanks very much
--
H. Frank Situmorang


:

This is a fairly good error message. Have you specified at least one Row
Heading and only on Column Heading?

What is the SQL of your crosstab that generates the error.

--
Duane Hookom
Microsoft Access MVP


:

Duane,

When I add in the next column of the grid this expression: ColHead: "Yr" &
[Forms]![frmRptSlct]![txtYear]-[PelayanJemaatbyYear].[TahunPel]

and I when I tried to save it I can not, because it says that I have to
specify one more rowheading and column heading,

Is it because I add in the next column of the grids?

Thanks in advance


--
H. Frank Situmorang

:

The only part of the solution I was proposing was the calculation of the
column headings of the crosstab.

What textbox are you thinking about putting in the "page header"? The text
box for the user to enter an ending year would be on a form.

If TahunPel is the year then put:
"Yr" & Forms!frmRptSlct!txtYear-PelayanJemaatbyYear.TahunPel
in the field (upper row) in your crosstab query. Set the Crosstab to Column
Heading for this expression.

--
Duane Hookom
Microsoft Access MVP


:

Duane.

Crosstab query that John suggested is a bit extraordinary. is the sample on
the website can be used for this kind of crosstab query?
In desining report for this,

Unbound textbox I will put in the page header,
Where shall I put the columheading expression:"Yr" &
Forms!frmRptSlct!txtYear-PelayanJemaatbyYear.TahunPel

Is in the event proerty?

Thanks a lot

H. Frank Situmorang


:

The text box on the form is unbound and is used only as a year that can be
used to find the difference between years in the report. This is just like
the months in the solution at http://www.tek-tips.com/faqs.cfm?fid=5466.

That's what this part of the crosstab sql is for

PIVOT "Yr" & Forms!frmRptSlct!txtYear-PelayanJemaatbyYear.TahunPel
IN ("Yr0","Yr1","Yr2");

This basically suggests taking the TahumPel (Year?) from the ending year
entered into the text box on the form.
--
Duane Hookom
Microsoft Access MVP


:

Thanks Duane for your kind explanation. I am still not clear on this:
1. Parameters Forms!frmRptSlct!txtYear Integer;
I have the data input form for this church officers, should I take the
name of the Year text box to replace txtYear?

2. IN ("Yr0","Yr1","Yr2");
How will it procuduce this number, while the data we fill in is 2007,
2008,.....

3. Do I need to change my SQL like you suggest as the basis/recors source
data of the report?

Thanks in advance for your help.


--
H. Frank Situmorang


:

I assume you want to dynamically choose a range of years for your report.
You need to use a reference year to calculate your column headings. This
could be a text box on a form for instance:
Forms!frmRptSlct!txtYear
You could default this text box to the current year and allow users to enter
any other year.

Parameters Forms!frmRptSlct!txtYear Integer;
TRANSFORM First(PelayanJemaatbyYear.NamaPel) AS FirstOfNamaPel
SELECT PelayanJemaatbyYear.Nurut, PelayanJemaatbyYear.BidangPelayanan,
PelayanJemaatbyYear.CountOfBidangPelayanan,
First(PelayanJemaatbyYear.NamaPel) AS [Total Of NamaPel]
FROM PelayanJemaatbyYear
GROUP BY PelayanJemaatbyYear.Nurut, PelayanJemaatbyYear.BidangPelayanan,
PelayanJemaatbyYear.CountOfBidangPelayanan
PIVOT "Yr" & Forms!frmRptSlct!txtYear-PelayanJemaatbyYear.TahunPel
IN ("Yr0","Yr1","Yr2");

This would result in column headings based on the year entered into the text
box and the 2 previous years.

--
Duane Hookom
Microsoft Access MVP


:

Duane,

I do not quite understand this saying on the website:
Set the with menuing: Query|Parameter
Forms!frmA!txtEndDate Date/Time

I have the form to enter the church officers data, I think the date/time can
be replaced by Year, but my question is what is the meaning of "with menuing"

Thanks in advance for your idea
--
H. Frank Situmorang


:

Hello,

With the help of John and Duane, I can create crosstab query which year
column can be dynamic.

For long term usage we want it in the report the year column can be dynamic.
I appreciate if anyone can help me how can I make the Year column using this
crosstabquery:

TRANSFORM First(PelayanJemaatbyYear.NamaPel) AS FirstOfNamaPel
SELECT PelayanJemaatbyYear.Nurut, PelayanJemaatbyYear.BidangPelayanan,
PelayanJemaatbyYear.CountOfBidangPelayanan,
First(PelayanJemaatbyYear.NamaPel) AS [Total Of NamaPel]
FROM PelayanJemaatbyYear
GROUP BY PelayanJemaatbyYear.Nurut, PelayanJemaatbyYear.BidangPelayanan,
PelayanJemaatbyYear.CountOfBidangPelayanan
PIVOT PelayanJemaatbyYear.TahunPel;


Thanks in advance