Report using cross tab

Duane, we can not link the Master to subreport since there is no field of Tahunpel (year of Serice) in the Main report Query.

Should I add it in the Main report query?

Thanks for your advice

Frank



Duane Hookom wrote:

The subreport should not have any text boxes with Control Sources like=Forms!
01-Dec-09

The subreport should not have any text boxes with Control Sources like
=Forms!frm...
You were suppose to substitute your actual form and control name which would
be [Forms]![frmTahundipilih]![cboEndYear]

Both of your report querys have a criteria that limits them to a single
year. Try a main report query of my suggestion earlier. There should be no
year or name fields in this query.

SELECT PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan
FROM PelayanJemaat
GROUP BY PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan

The subreport query should be:
SELECT PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan, NamaPel,
TahunPel
FROM PelayanJemaat
WHERE [TahunPel] Between Forms!frmTahundipilih!cboEndYear- 4 AND
Forms!frmTahundipilih!cboEndYear;

You should have the 5 text boxes that would display the 5 years across the
top of the detail section in the main report. Below each of these should be a
copy of the subreport. You use the link master/child properties to filter
each subreport to the appropriate fields. These fields will include the text
boxes displaying the years as part of the Link Master. The link child field
that links with the text boxes is the TahunPel which I assume is a field
storing the year like 2009, 2008, 2007,...
--
Duane Hookom
Microsoft Access MVP


"Frank Situmorang" wrote:

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
SQL Server Table Variables And Cursorless Cursor
http://www.eggheadcafe.com/tutorials...-variable.aspx

Duane, we can not link the Master to subreport since there is no field of Tahunpel (year of Serice) in the Main report Query.

Should I add it in the Main report query?

Thanks for your advice

Frank



Duane Hookom wrote:

The subreport should not have any text boxes with Control Sources like=Forms!
01-Dec-09

The subreport should not have any text boxes with Control Sources like
=Forms!frm...
You were suppose to substitute your actual form and control name which would
be [Forms]![frmTahundipilih]![cboEndYear]

Both of your report querys have a criteria that limits them to a single
year. Try a main report query of my suggestion earlier. There should be no
year or name fields in this query.

SELECT PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan
FROM PelayanJemaat
GROUP BY PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan

The subreport query should be:
SELECT PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan, NamaPel,
TahunPel
FROM PelayanJemaat
WHERE [TahunPel] Between Forms!frmTahundipilih!cboEndYear- 4 AND
Forms!frmTahundipilih!cboEndYear;

You should have the 5 text boxes that would display the 5 years across the
top of the detail section in the main report. Below each of these should be a
copy of the subreport. You use the link master/child properties to filter
each subreport to the appropriate fields. These fields will include the text
boxes displaying the years as part of the Link Master. The link child field
that links with the text boxes is the TahunPel which I assume is a field
storing the year like 2009, 2008, 2007,...
--
Duane Hookom
Microsoft Access MVP


"Frank Situmorang" wrote:

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
SQL Server Table Variables And Cursorless Cursor
http://www.eggheadcafe.com/tutorials...-variable.aspx

Duane, we can not link the Master to subreport since there is no field of Tahunpel (year of Serice) in the Main report Query.

Should I add it in the Main report query?

Thanks for your advice

Frank



Duane Hookom wrote:

The subreport should not have any text boxes with Control Sources like=Forms!
01-Dec-09

The subreport should not have any text boxes with Control Sources like
=Forms!frm...
You were suppose to substitute your actual form and control name which would
be [Forms]![frmTahundipilih]![cboEndYear]

Both of your report querys have a criteria that limits them to a single
year. Try a main report query of my suggestion earlier. There should be no
year or name fields in this query.

SELECT PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan
FROM PelayanJemaat
GROUP BY PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan

The subreport query should be:
SELECT PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan, NamaPel,
TahunPel
FROM PelayanJemaat
WHERE [TahunPel] Between Forms!frmTahundipilih!cboEndYear- 4 AND
Forms!frmTahundipilih!cboEndYear;

You should have the 5 text boxes that would display the 5 years across the
top of the detail section in the main report. Below each of these should be a
copy of the subreport. You use the link master/child properties to filter
each subreport to the appropriate fields. These fields will include the text
boxes displaying the years as part of the Link Master. The link child field
that links with the text boxes is the TahunPel which I assume is a field
storing the year like 2009, 2008, 2007,...
--
Duane Hookom
Microsoft Access MVP


"Frank Situmorang" wrote:

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
SQL Server Table Variables And Cursorless Cursor
http://www.eggheadcafe.com/tutorials...-variable.aspx

I never suggested you needed the Tahunpel field in the main report query.
Every main report record source I have suggested doesn't have this field. I
don't want the "field" in the report. You don't need or want the field in
your main report's record source. Use the record source is as I have stated.
I have done this before. I know what I am suggesting works. PLEASE ignore the
Tahunpel field in the main report. Just use it in the subreport.

Do you understand that you only need the year values in the main report?
That is why I have described many times that you need to have 5 text boxes
with the control sources to calculate the year values. I don't think I could
be any more clear that the text boxes are used as part of the Link Master
(main report) property. You need to type the Link Master/Child properties.
--
Duane Hookom
Microsoft Access MVP


"Frank Situmorang" wrote:

Duane, we can not link the Master to subreport since there is no field of Tahunpel (year of Serice) in the Main report Query.

Should I add it in the Main report query?

Thanks for your advice

Frank



Duane Hookom wrote:

The subreport should not have any text boxes with Control Sources like=Forms!
01-Dec-09

The subreport should not have any text boxes with Control Sources like
=Forms!frm...
You were suppose to substitute your actual form and control name which would
be [Forms]![frmTahundipilih]![cboEndYear]

Both of your report querys have a criteria that limits them to a single
year. Try a main report query of my suggestion earlier. There should be no
year or name fields in this query.

SELECT PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan
FROM PelayanJemaat
GROUP BY PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan

The subreport query should be:
SELECT PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan, NamaPel,
TahunPel
FROM PelayanJemaat
WHERE [TahunPel] Between Forms!frmTahundipilih!cboEndYear- 4 AND
Forms!frmTahundipilih!cboEndYear;

You should have the 5 text boxes that would display the 5 years across the
top of the detail section in the main report. Below each of these should be a
copy of the subreport. You use the link master/child properties to filter
each subreport to the appropriate fields. These fields will include the text
boxes displaying the years as part of the Link Master. The link child field
that links with the text boxes is the TahunPel which I assume is a field
storing the year like 2009, 2008, 2007,...
--
Duane Hookom
Microsoft Access MVP


"Frank Situmorang" wrote:

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
SQL Server Table Variables And Cursorless Cursor
http://www.eggheadcafe.com/tutorials...-variable.aspx
.

I never suggested you needed the Tahunpel field in the main report query.
Every main report record source I have suggested doesn't have this field. I
don't want the "field" in the report. You don't need or want the field in
your main report's record source. Use the record source is as I have stated.
I have done this before. I know what I am suggesting works. PLEASE ignore the
Tahunpel field in the main report. Just use it in the subreport.

Do you understand that you only need the year values in the main report?
That is why I have described many times that you need to have 5 text boxes
with the control sources to calculate the year values. I don't think I could
be any more clear that the text boxes are used as part of the Link Master
(main report) property. You need to type the Link Master/Child properties.
--
Duane Hookom
Microsoft Access MVP


"Frank Situmorang" wrote:

Duane, we can not link the Master to subreport since there is no field of Tahunpel (year of Serice) in the Main report Query.

Should I add it in the Main report query?

Thanks for your advice

Frank



Duane Hookom wrote:

The subreport should not have any text boxes with Control Sources like=Forms!
01-Dec-09

The subreport should not have any text boxes with Control Sources like
=Forms!frm...
You were suppose to substitute your actual form and control name which would
be [Forms]![frmTahundipilih]![cboEndYear]

Both of your report querys have a criteria that limits them to a single
year. Try a main report query of my suggestion earlier. There should be no
year or name fields in this query.

SELECT PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan
FROM PelayanJemaat
GROUP BY PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan

The subreport query should be:
SELECT PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan, NamaPel,
TahunPel
FROM PelayanJemaat
WHERE [TahunPel] Between Forms!frmTahundipilih!cboEndYear- 4 AND
Forms!frmTahundipilih!cboEndYear;

You should have the 5 text boxes that would display the 5 years across the
top of the detail section in the main report. Below each of these should be a
copy of the subreport. You use the link master/child properties to filter
each subreport to the appropriate fields. These fields will include the text
boxes displaying the years as part of the Link Master. The link child field
that links with the text boxes is the TahunPel which I assume is a field
storing the year like 2009, 2008, 2007,...
--
Duane Hookom
Microsoft Access MVP


"Frank Situmorang" wrote:

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
SQL Server Table Variables And Cursorless Cursor
http://www.eggheadcafe.com/tutorials...-variable.aspx
.

I never suggested you needed the Tahunpel field in the main report query.
Every main report record source I have suggested doesn't have this field. I
don't want the "field" in the report. You don't need or want the field in
your main report's record source. Use the record source is as I have stated.
I have done this before. I know what I am suggesting works. PLEASE ignore the
Tahunpel field in the main report. Just use it in the subreport.

Do you understand that you only need the year values in the main report?
That is why I have described many times that you need to have 5 text boxes
with the control sources to calculate the year values. I don't think I could
be any more clear that the text boxes are used as part of the Link Master
(main report) property. You need to type the Link Master/Child properties.
--
Duane Hookom
Microsoft Access MVP


"Frank Situmorang" wrote:

Duane, we can not link the Master to subreport since there is no field of Tahunpel (year of Serice) in the Main report Query.

Should I add it in the Main report query?

Thanks for your advice

Frank



Duane Hookom wrote:

The subreport should not have any text boxes with Control Sources like=Forms!
01-Dec-09

The subreport should not have any text boxes with Control Sources like
=Forms!frm...
You were suppose to substitute your actual form and control name which would
be [Forms]![frmTahundipilih]![cboEndYear]

Both of your report querys have a criteria that limits them to a single
year. Try a main report query of my suggestion earlier. There should be no
year or name fields in this query.

SELECT PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan
FROM PelayanJemaat
GROUP BY PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan

The subreport query should be:
SELECT PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan, NamaPel,
TahunPel
FROM PelayanJemaat
WHERE [TahunPel] Between Forms!frmTahundipilih!cboEndYear- 4 AND
Forms!frmTahundipilih!cboEndYear;

You should have the 5 text boxes that would display the 5 years across the
top of the detail section in the main report. Below each of these should be a
copy of the subreport. You use the link master/child properties to filter
each subreport to the appropriate fields. These fields will include the text
boxes displaying the years as part of the Link Master. The link child field
that links with the text boxes is the TahunPel which I assume is a field
storing the year like 2009, 2008, 2007,...
--
Duane Hookom
Microsoft Access MVP


"Frank Situmorang" wrote:

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
SQL Server Table Variables And Cursorless Cursor
http://www.eggheadcafe.com/tutorials...-variable.aspx
.

In the Mainreport query are only 2 fields, i.e : Nurut(Sequence number of the Position) and Bidang Pelayanan ( Posisiton Description), so we can not link main report with the child report.

If what you meant is to type 2009, 2008, 2007, accros the detail of the main report, this is not dynamic anymore?, because I have upt 11 years now in my database.

Thanks for your advice.

Frank



Duane Hookom wrote:

The subreport should not have any text boxes with Control Sources like=Forms!
01-Dec-09

The subreport should not have any text boxes with Control Sources like
=Forms!frm...
You were suppose to substitute your actual form and control name which would
be [Forms]![frmTahundipilih]![cboEndYear]

Both of your report querys have a criteria that limits them to a single
year. Try a main report query of my suggestion earlier. There should be no
year or name fields in this query.

SELECT PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan
FROM PelayanJemaat
GROUP BY PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan

The subreport query should be:
SELECT PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan, NamaPel,
TahunPel
FROM PelayanJemaat
WHERE [TahunPel] Between Forms!frmTahundipilih!cboEndYear- 4 AND
Forms!frmTahundipilih!cboEndYear;

You should have the 5 text boxes that would display the 5 years across the
top of the detail section in the main report. Below each of these should be a
copy of the subreport. You use the link master/child properties to filter
each subreport to the appropriate fields. These fields will include the text
boxes displaying the years as part of the Link Master. The link child field
that links with the text boxes is the TahunPel which I assume is a field
storing the year like 2009, 2008, 2007,...
--
Duane Hookom
Microsoft Access MVP


"Frank Situmorang" wrote:

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Power of Reflection: Creating an Object Factory
http://www.eggheadcafe.com/tutorials...tion-crea.aspx

In the Mainreport query are only 2 fields, i.e : Nurut(Sequence number of the Position) and Bidang Pelayanan ( Posisiton Description), so we can not link main report with the child report.

If what you meant is to type 2009, 2008, 2007, accros the detail of the main report, this is not dynamic anymore?, because I have upt 11 years now in my database.

Thanks for your advice.

Frank



Duane Hookom wrote:

The subreport should not have any text boxes with Control Sources like=Forms!
01-Dec-09

The subreport should not have any text boxes with Control Sources like
=Forms!frm...
You were suppose to substitute your actual form and control name which would
be [Forms]![frmTahundipilih]![cboEndYear]

Both of your report querys have a criteria that limits them to a single
year. Try a main report query of my suggestion earlier. There should be no
year or name fields in this query.

SELECT PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan
FROM PelayanJemaat
GROUP BY PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan

The subreport query should be:
SELECT PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan, NamaPel,
TahunPel
FROM PelayanJemaat
WHERE [TahunPel] Between Forms!frmTahundipilih!cboEndYear- 4 AND
Forms!frmTahundipilih!cboEndYear;

You should have the 5 text boxes that would display the 5 years across the
top of the detail section in the main report. Below each of these should be a
copy of the subreport. You use the link master/child properties to filter
each subreport to the appropriate fields. These fields will include the text
boxes displaying the years as part of the Link Master. The link child field
that links with the text boxes is the TahunPel which I assume is a field
storing the year like 2009, 2008, 2007,...
--
Duane Hookom
Microsoft Access MVP


"Frank Situmorang" wrote:

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Power of Reflection: Creating an Object Factory
http://www.eggheadcafe.com/tutorials...tion-crea.aspx

In the Mainreport query are only 2 fields, i.e : Nurut(Sequence number of the Position) and Bidang Pelayanan ( Posisiton Description), so we can not link main report with the child report.

If what you meant is to type 2009, 2008, 2007, accros the detail of the main report, this is not dynamic anymore?, because I have upt 11 years now in my database.

Thanks for your advice.

Frank



Duane Hookom wrote:

The subreport should not have any text boxes with Control Sources like=Forms!
01-Dec-09

The subreport should not have any text boxes with Control Sources like
=Forms!frm...
You were suppose to substitute your actual form and control name which would
be [Forms]![frmTahundipilih]![cboEndYear]

Both of your report querys have a criteria that limits them to a single
year. Try a main report query of my suggestion earlier. There should be no
year or name fields in this query.

SELECT PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan
FROM PelayanJemaat
GROUP BY PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan

The subreport query should be:
SELECT PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan, NamaPel,
TahunPel
FROM PelayanJemaat
WHERE [TahunPel] Between Forms!frmTahundipilih!cboEndYear- 4 AND
Forms!frmTahundipilih!cboEndYear;

You should have the 5 text boxes that would display the 5 years across the
top of the detail section in the main report. Below each of these should be a
copy of the subreport. You use the link master/child properties to filter
each subreport to the appropriate fields. These fields will include the text
boxes displaying the years as part of the Link Master. The link child field
that links with the text boxes is the TahunPel which I assume is a field
storing the year like 2009, 2008, 2007,...
--
Duane Hookom
Microsoft Access MVP


"Frank Situmorang" wrote:

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Power of Reflection: Creating an Object Factory
http://www.eggheadcafe.com/tutorials...tion-crea.aspx

I have created a demo mdb of this concept. You can download it from
http://www.access.hookom.net/samples/memberships.zip.

It has some different form, control, table, and field names but you should
be able to convert.

--
Duane Hookom
Microsoft Access MVP


"Frank Situmorang" wrote:

In the Mainreport query are only 2 fields, i.e : Nurut(Sequence number of the Position) and Bidang Pelayanan ( Posisiton Description), so we can not link main report with the child report.

If what you meant is to type 2009, 2008, 2007, accros the detail of the main report, this is not dynamic anymore?, because I have upt 11 years now in my database.

Thanks for your advice.

Frank



Duane Hookom wrote:

The subreport should not have any text boxes with Control Sources like=Forms!
01-Dec-09

The subreport should not have any text boxes with Control Sources like
=Forms!frm...
You were suppose to substitute your actual form and control name which would
be [Forms]![frmTahundipilih]![cboEndYear]

Both of your report querys have a criteria that limits them to a single
year. Try a main report query of my suggestion earlier. There should be no
year or name fields in this query.

SELECT PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan
FROM PelayanJemaat
GROUP BY PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan

The subreport query should be:
SELECT PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan, NamaPel,
TahunPel
FROM PelayanJemaat
WHERE [TahunPel] Between Forms!frmTahundipilih!cboEndYear- 4 AND
Forms!frmTahundipilih!cboEndYear;

You should have the 5 text boxes that would display the 5 years across the
top of the detail section in the main report. Below each of these should be a
copy of the subreport. You use the link master/child properties to filter
each subreport to the appropriate fields. These fields will include the text
boxes displaying the years as part of the Link Master. The link child field
that links with the text boxes is the TahunPel which I assume is a field
storing the year like 2009, 2008, 2007,...
--
Duane Hookom
Microsoft Access MVP


"Frank Situmorang" wrote:

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Power of Reflection: Creating an Object Factory
http://www.eggheadcafe.com/tutorials...tion-crea.aspx
.