Report using cross tab

Duane, I still can not grab all the idea, so what would be the query for the subreport. I think it is no longer like in your email before.

I appreciate your help.

Frank



Duane Hookom wrote:

I expect you want to have 5 text boxes in your main report to provide a
20-Nov-09

I expect you want to have 5 text boxes in your main report to provide a value
to link to the year in the subreport. These 5 text boxes could

Name: txt0
Control Source: =Forms!frmTahundipilih!cboEndYear

Name: txt1
Control Source: =Forms!frmTahundipilih!cboEndYear -1

Name: txt2
Control Source: =Forms!frmTahundipilih!cboEndYear - 2

Name: txt3
Control Source: =Forms!frmTahundipilih!cboEndYear - 3

Name: txt4
Control Source: =Forms!frmTahundipilih!cboEndYear - 4

--
Duane Hookom
Microsoft Access MVP


"Frank Situmorang" wrote:

Previous Posts In This Thread:

EggHeadCafe - Software Developer Portal of Choice
Free Icons / Graphics For Business Applications
http://www.eggheadcafe.com/tutorials...phics-for.aspx

Create a subreport based on a query like:
SELECT PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan, NamaPel,
TahunPel
FROM PelayanJemaat

You would have the link master/child properties of each subreport copy
reference the year and position fields.

--
Duane Hookom
Microsoft Access MVP


"Frank Situmorang" wrote:

Duane, I still can not grab all the idea, so what would be the query for the subreport. I think it is no longer like in your email before.

I appreciate your help.

Frank



Duane Hookom wrote:

I expect you want to have 5 text boxes in your main report to provide a
20-Nov-09

I expect you want to have 5 text boxes in your main report to provide a value
to link to the year in the subreport. These 5 text boxes could

Name: txt0
Control Source: =Forms!frmTahundipilih!cboEndYear

Name: txt1
Control Source: =Forms!frmTahundipilih!cboEndYear -1

Name: txt2
Control Source: =Forms!frmTahundipilih!cboEndYear - 2

Name: txt3
Control Source: =Forms!frmTahundipilih!cboEndYear - 3

Name: txt4
Control Source: =Forms!frmTahundipilih!cboEndYear - 4

--
Duane Hookom
Microsoft Access MVP


"Frank Situmorang" wrote:

Previous Posts In This Thread:

EggHeadCafe - Software Developer Portal of Choice
Free Icons / Graphics For Business Applications
http://www.eggheadcafe.com/tutorials...phics-for.aspx
.

Duane, I have tried it but the result is not like waht we expected.

This is my Query in the MainRepot:

SELECT PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan, Forms!frmTahundipilih!cboEndYear-0 AS Year0, Forms!frmTahundipilih!cboEndYear-1 AS Year1, Forms!frmTahundipilih!cboEndYear-2 AS Year2, Forms!frmTahundipilih!cboEndYear-3 AS Year3, Forms!frmTahundipilih!cboEndYear-4 AS Year4
FROM PelayanJemaat
GROUP BY PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan;

And this is my Query in the Subreport1:

SELECT PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan, PelayanJemaat.NamaPel, PelayanJemaat.TahunPel, Forms!frmTahundipilih!cboEndYear-0 AS [Year]
FROM PelayanJemaat;


Subreport2:
SELECT PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan, PelayanJemaat.NamaPel, PelayanJemaat.TahunPel, Forms!frmTahundipilih!cboEndYear-1 AS [Year]
FROM PelayanJemaat;

and so forth to subreport5

Link childfields: BidangPelayanan;Year
Link Master Fields: BidangPelayanan;Year0

and so forth....

What have I done wrong...should we have the NamaPel( name of officers) also in the Main Query/Report? When we say link should we linked with the field of the query or the name of the field in the report.

Could you help my by looking to your linked file, like you helped me before?

Thanks in advence

Frank






Duane Hookom wrote:

Create a subreport based on a query like:SELECT PelayanJemaat.
23-Nov-09

Create a subreport based on a query like:
SELECT PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan, NamaPel,
TahunPel
FROM PelayanJemaat

You would have the link master/child properties of each subreport copy
reference the year and position fields.

--
Duane Hookom
Microsoft Access MVP


"Frank Situmorang" wrote:

Previous Posts In This Thread:

EggHeadCafe - Software Developer Portal of Choice
Encryption On Mobile Devices in C# / .NET
http://www.eggheadcafe.com/tutorials...bile-devi.aspx

As I stated, there is only one subreport. I'm not sure where you got the
Year[X] columns in your main report record source. What's wrong with the main
report record source as I suggested in my reply on 11/18 and the subreport
record source from 11/23?

--
Duane Hookom
Microsoft Access MVP


"Frank Situmorang" wrote:

Duane, I have tried it but the result is not like waht we expected.

This is my Query in the MainRepot:

SELECT PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan, Forms!frmTahundipilih!cboEndYear-0 AS Year0, Forms!frmTahundipilih!cboEndYear-1 AS Year1, Forms!frmTahundipilih!cboEndYear-2 AS Year2, Forms!frmTahundipilih!cboEndYear-3 AS Year3, Forms!frmTahundipilih!cboEndYear-4 AS Year4
FROM PelayanJemaat
GROUP BY PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan;

And this is my Query in the Subreport1:

SELECT PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan, PelayanJemaat.NamaPel, PelayanJemaat.TahunPel, Forms!frmTahundipilih!cboEndYear-0 AS [Year]
FROM PelayanJemaat;


Subreport2:
SELECT PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan, PelayanJemaat.NamaPel, PelayanJemaat.TahunPel, Forms!frmTahundipilih!cboEndYear-1 AS [Year]
FROM PelayanJemaat;

and so forth to subreport5

Link childfields: BidangPelayanan;Year
Link Master Fields: BidangPelayanan;Year0

and so forth....

What have I done wrong...should we have the NamaPel( name of officers) also in the Main Query/Report? When we say link should we linked with the field of the query or the name of the field in the report.

Could you help my by looking to your linked file, like you helped me before?

Thanks in advence

Frank






Duane Hookom wrote:

Create a subreport based on a query like:SELECT PelayanJemaat.
23-Nov-09

Create a subreport based on a query like:
SELECT PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan, NamaPel,
TahunPel
FROM PelayanJemaat

You would have the link master/child properties of each subreport copy
reference the year and position fields.

--
Duane Hookom
Microsoft Access MVP


"Frank Situmorang" wrote:

Previous Posts In This Thread:

EggHeadCafe - Software Developer Portal of Choice
Encryption On Mobile Devices in C# / .NET
http://www.eggheadcafe.com/tutorials...bile-devi.aspx
.

Duane in my understanding report will take value from query and query will take value from table. So if we do not have a calculated field in query how can we link it.

I also refer to your previous email Nov 13 saying 5 set of subreport.

OK I will try again to make it only one subreport. But if you still have a rather easy one I appreciate. The problem because we have to choose in combo box the year of service, then it will show, for the last 5 years in the report.

Thanks very much,

Frank



Duane Hookom wrote:

As I stated, there is only one subreport.
24-Nov-09

As I stated, there is only one subreport. I am not sure where you got the
Year[X] columns in your main report record source. What's wrong with the main
report record source as I suggested in my reply on 11/18 and the subreport
record source from 11/23?

--
Duane Hookom
Microsoft Access MVP


"Frank Situmorang" wrote:

Previous Posts In This Thread:

EggHeadCafe - Software Developer Portal of Choice
Look Ma, No DataBase! Bamboo Prevalence
http://www.eggheadcafe.com/tutorials...abase-bam.aspx

My Nov 13 message was prior to you providing some information. My 11/13
message stated "I would try use 5 copies of the same subreport side-by-side
in the detail section of a main report". That clearly states the same
subreport copied 5 times in main report.

The Year field must be in the subreport's record source. This year is used
as part of the Link Child. To get the year part for the Link Master property,
you put 5 text boxes in the main report. If you can't figure out how to do
this based on the control on your form, just set the control sources of the 5
text boxes to:
=2009
=2008
=2007
=2006
=2005
Then once it works, read back through my posts to find out how to change
these to dynamically calculate based on the value from the control on the
form.

--
Duane Hookom
Microsoft Access MVP


"Frank Situmorang" wrote:

Duane in my understanding report will take value from query and query will take value from table. So if we do not have a calculated field in query how can we link it.

I also refer to your previous email Nov 13 saying 5 set of subreport.

OK I will try again to make it only one subreport. But if you still have a rather easy one I appreciate. The problem because we have to choose in combo box the year of service, then it will show, for the last 5 years in the report.

Thanks very much,

Frank



Duane Hookom wrote:

As I stated, there is only one subreport.
24-Nov-09

As I stated, there is only one subreport. I am not sure where you got the
Year[X] columns in your main report record source. What's wrong with the main
report record source as I suggested in my reply on 11/18 and the subreport
record source from 11/23?

--
Duane Hookom
Microsoft Access MVP


"Frank Situmorang" wrote:

Previous Posts In This Thread:

EggHeadCafe - Software Developer Portal of Choice
Look Ma, No DataBase! Bamboo Prevalence
http://www.eggheadcafe.com/tutorials...abase-bam.aspx
.

Duane,

I have followed your suggestion using:
=Forms!frmRptSlct!cboYear
=Forms!frmRptSlct!cboYear-1
=Forms!frmRptSlct!cboYear-2
=Forms!frmRptSlct!cboYear-3
=Forms!frmRptSlct!cboYear-4


both in the subreport and main report, but when I created the link master & child field, should be the field of the query, the error message says " Can not create the link with the unbound form/report.

This is my mainreport query:
SELECT PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan, PelayanJemaat.ID_pel, PelayanJemaat.TahunPel
FROM PelayanJemaat
WHERE (((PelayanJemaat.TahunPel)=[Forms]![frmTahundipilih]![cboEndYear]));

This is my subreport query:
SELECT PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan, PelayanJemaat.ID_pel, PelayanJemaat.NamaPel, PelayanJemaat.TahunPel
FROM PelayanJemaat
WHERE (((PelayanJemaat.TahunPel)=[Forms]![frmTahundipilih]![cboEndYear]));


Is it because both queries come from one table?

In other reports of my churchdatabase, the link can work when the field that I link is from difference table. Could it the onetable source be the cause of the problem?

Thanks for your help.

Frank



Duane Hookom wrote:

My Nov 13 message was prior to you providing some information.
25-Nov-09

My Nov 13 message was prior to you providing some information. My 11/13
message stated "I would try use 5 copies of the same subreport side-by-side
in the detail section of a main report". That clearly states the same
subreport copied 5 times in main report.

The Year field must be in the subreport's record source. This year is used
as part of the Link Child. To get the year part for the Link Master property,
you put 5 text boxes in the main report. If you cannot figure out how to do
this based on the control on your form, just set the control sources of the 5
text boxes to:
=2009
=2008
=2007
=2006
=2005
Then once it works, read back through my posts to find out how to change
these to dynamically calculate based on the value from the control on the
form.

--
Duane Hookom
Microsoft Access MVP


"Frank Situmorang" wrote:

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
XAML "Windows Send Error Report"
http://www.eggheadcafe.com/tutorials...end-error.aspx

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:

Duane,

I have followed your suggestion using:
=Forms!frmRptSlct!cboYear
=Forms!frmRptSlct!cboYear-1
=Forms!frmRptSlct!cboYear-2
=Forms!frmRptSlct!cboYear-3
=Forms!frmRptSlct!cboYear-4


both in the subreport and main report, but when I created the link master & child field, should be the field of the query, the error message says " Can not create the link with the unbound form/report.

This is my mainreport query:
SELECT PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan, PelayanJemaat.ID_pel, PelayanJemaat.TahunPel
FROM PelayanJemaat
WHERE (((PelayanJemaat.TahunPel)=[Forms]![frmTahundipilih]![cboEndYear]));

This is my subreport query:
SELECT PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan, PelayanJemaat.ID_pel, PelayanJemaat.NamaPel, PelayanJemaat.TahunPel
FROM PelayanJemaat
WHERE (((PelayanJemaat.TahunPel)=[Forms]![frmTahundipilih]![cboEndYear]));


Is it because both queries come from one table?

In other reports of my churchdatabase, the link can work when the field that I link is from difference table. Could it the onetable source be the cause of the problem?

Thanks for your help.

Frank



Duane Hookom wrote:

My Nov 13 message was prior to you providing some information.
25-Nov-09

My Nov 13 message was prior to you providing some information. My 11/13
message stated "I would try use 5 copies of the same subreport side-by-side
in the detail section of a main report". That clearly states the same
subreport copied 5 times in main report.

The Year field must be in the subreport's record source. This year is used
as part of the Link Child. To get the year part for the Link Master property,
you put 5 text boxes in the main report. If you cannot figure out how to do
this based on the control on your form, just set the control sources of the 5
text boxes to:
=2009
=2008
=2007
=2006
=2005
Then once it works, read back through my posts to find out how to change
these to dynamically calculate based on the value from the control on the
form.

--
Duane Hookom
Microsoft Access MVP


"Frank Situmorang" wrote:

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
XAML "Windows Send Error Report"
http://www.eggheadcafe.com/tutorials...end-error.aspx
.

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:

Duane,

I have followed your suggestion using:
=Forms!frmRptSlct!cboYear
=Forms!frmRptSlct!cboYear-1
=Forms!frmRptSlct!cboYear-2
=Forms!frmRptSlct!cboYear-3
=Forms!frmRptSlct!cboYear-4


both in the subreport and main report, but when I created the link master & child field, should be the field of the query, the error message says " Can not create the link with the unbound form/report.

This is my mainreport query:
SELECT PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan, PelayanJemaat.ID_pel, PelayanJemaat.TahunPel
FROM PelayanJemaat
WHERE (((PelayanJemaat.TahunPel)=[Forms]![frmTahundipilih]![cboEndYear]));

This is my subreport query:
SELECT PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan, PelayanJemaat.ID_pel, PelayanJemaat.NamaPel, PelayanJemaat.TahunPel
FROM PelayanJemaat
WHERE (((PelayanJemaat.TahunPel)=[Forms]![frmTahundipilih]![cboEndYear]));


Is it because both queries come from one table?

In other reports of my churchdatabase, the link can work when the field that I link is from difference table. Could it the onetable source be the cause of the problem?

Thanks for your help.

Frank



Duane Hookom wrote:

My Nov 13 message was prior to you providing some information.
25-Nov-09

My Nov 13 message was prior to you providing some information. My 11/13
message stated "I would try use 5 copies of the same subreport side-by-side
in the detail section of a main report". That clearly states the same
subreport copied 5 times in main report.

The Year field must be in the subreport's record source. This year is used
as part of the Link Child. To get the year part for the Link Master property,
you put 5 text boxes in the main report. If you cannot figure out how to do
this based on the control on your form, just set the control sources of the 5
text boxes to:
=2009
=2008
=2007
=2006
=2005
Then once it works, read back through my posts to find out how to change
these to dynamically calculate based on the value from the control on the
form.

--
Duane Hookom
Microsoft Access MVP


"Frank Situmorang" wrote:

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
XAML "Windows Send Error Report"
http://www.eggheadcafe.com/tutorials...end-error.aspx
.

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:

Duane,

I have followed your suggestion using:
=Forms!frmRptSlct!cboYear
=Forms!frmRptSlct!cboYear-1
=Forms!frmRptSlct!cboYear-2
=Forms!frmRptSlct!cboYear-3
=Forms!frmRptSlct!cboYear-4


both in the subreport and main report, but when I created the link master & child field, should be the field of the query, the error message says " Can not create the link with the unbound form/report.

This is my mainreport query:
SELECT PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan, PelayanJemaat.ID_pel, PelayanJemaat.TahunPel
FROM PelayanJemaat
WHERE (((PelayanJemaat.TahunPel)=[Forms]![frmTahundipilih]![cboEndYear]));

This is my subreport query:
SELECT PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan, PelayanJemaat.ID_pel, PelayanJemaat.NamaPel, PelayanJemaat.TahunPel
FROM PelayanJemaat
WHERE (((PelayanJemaat.TahunPel)=[Forms]![frmTahundipilih]![cboEndYear]));


Is it because both queries come from one table?

In other reports of my churchdatabase, the link can work when the field that I link is from difference table. Could it the onetable source be the cause of the problem?

Thanks for your help.

Frank



Duane Hookom wrote:

My Nov 13 message was prior to you providing some information.
25-Nov-09

My Nov 13 message was prior to you providing some information. My 11/13
message stated "I would try use 5 copies of the same subreport side-by-side
in the detail section of a main report". That clearly states the same
subreport copied 5 times in main report.

The Year field must be in the subreport's record source. This year is used
as part of the Link Child. To get the year part for the Link Master property,
you put 5 text boxes in the main report. If you cannot figure out how to do
this based on the control on your form, just set the control sources of the 5
text boxes to:
=2009
=2008
=2007
=2006
=2005
Then once it works, read back through my posts to find out how to change
these to dynamically calculate based on the value from the control on the
form.

--
Duane Hookom
Microsoft Access MVP


"Frank Situmorang" wrote:

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
XAML "Windows Send Error Report"
http://www.eggheadcafe.com/tutorials...end-error.aspx
.