RE: Dynamic column in report using crosstab query

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

Hi Duane and all others:

I do not want to lose hope, I still want to make a dynamic report because it
could be that I could move from the church to another place, but they still
can use the database.

So could you please let me know how can you may download it. Last time there
is an website hosting the download for free told by someone in this forum,
but I could not get the website anymore, it sounds like file save.com

I have the this particular database I want to put it there so you can
download.

Thanks in addvance
--
H. Frank Situmorang


"Duane Hookom" wrote:

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


"Frank Situmorang" wrote:

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


"Duane Hookom" wrote:

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


"Frank Situmorang" wrote:

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

"Duane Hookom" wrote:

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


"Frank Situmorang" wrote:

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


"Duane Hookom" wrote:

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


"Frank Situmorang" wrote:

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


"Duane Hookom" wrote:

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


"Frank Situmorang" wrote:

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


"Frank Situmorang" wrote:

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
--
H. Frank Situmorang
.

Relevant Pages

  • RE: Dynamic column in report using crosstab query
    ... H. Frank Situmorang ... "Duane Hookom" wrote: ... What is the SQL of your crosstab that generates the error. ... You need to use a reference year to calculate your column headings. ...
    (microsoft.public.access.reports)
  • RE: Dynamic column in report using crosstab query
    ... Change the SQL of the crosstab to: ... Can we modify it to have a result in the report as follows: ... H. Frank Situmorang ... "Duane Hookom" wrote: ...
    (microsoft.public.access.reports)
  • RE: Dynamic column in report using crosstab query
    ... H. Frank Situmorang ... "Duane Hookom" wrote: ... What is the SQL of your crosstab that generates the error. ... You need to use a reference year to calculate your column headings. ...
    (microsoft.public.access.reports)
  • RE: Dynamic column in report using crosstab query
    ... although we do not need to show it up on the report. ... H. Frank Situmorang ... "Duane Hookom" wrote: ... What is the SQL of your crosstab that generates the error. ...
    (microsoft.public.access.reports)
  • RE: Dynamic column in report using crosstab query
    ... H. Frank Situmorang ... "Duane Hookom" wrote: ... Crosstab query that John suggested is a bit extraordinary. ... You need to use a reference year to calculate your column headings. ...
    (microsoft.public.access.reports)