Dynamic column in report using crosstab query

[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

 

[Duane Hookom]

I would probably try create "relative years" using the solution for "relative
months" found at http://www.tek-tips.com/faqs.cfm?fid=5466.

 

[Frank Situmorang]

Thanks Duane for your help. I have tried to do it but it is difficult because
when Itried to see the crosstab query whien desining in the report it
already shows the year 2007 and 2008 ( the sample data that I made). How can
we make it becomes the column heading if we fill for year 2009 how can it
creat dynamically.

This is the crosstab query:
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;


--
H. Frank Situmorang

I would probably try create "relative years" using the solution for "relative
months" found at http://www.tek-tips.com/faqs.cfm?fid=5466.

--
Duane Hookom
Microsoft Access MVP

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

 

[Frank Situmorang]

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

 

[Duane Hookom]

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

 

[Frank Situmorang]

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

 

[Duane Hookom]

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

 

[Frank Situmorang]

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

 

[Duane Hookom]

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

 

[Frank Situmorang]

OK Duane, I will try it now
--
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

 

[Frank Situmorang]

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

 

[Duane Hookom]

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

 

[Frank Situmorang]

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

 

[Duane Hookom]

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

 

[Frank Situmorang]

I just confused Duane, I have tried to import your form and report and tried
to make my crosstabqueary similar to yours, but it just can not work.

I do not know what to do, maybe I just make a menu just to open the query.
Bedause actually my crosstab query when I open it it already shows up like
what I want, but to put it into report dynamically, that is the problem.

Since I want to present this this project to the church board this coming
Saturday, maybe I just make the report which is not dynamic, any time there
is an addtional year I can drag the field and redesigned it.

Thanks to all of you that already helped me, I will donate this dabatase to
our church.
--
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

 

[Frank Situmorang]

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

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

 

[Frank Situmorang]

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

 

[Duane Hookom]

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

 

[Frank Situmorang]

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

 

[Duane Hookom]

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