Report using cross tab

  • Thread starter Thread starter Frank Situmorang
  • Start date Start date
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 lik
=Forms!frm..
You were suppose to substitute your actual form and control name which woul
be [Forms]![frmTahundipilih]![cboEndYear

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

SELECT PelayanJemaat.Nurut, PelayanJemaat.BidangPelayana
FROM PelayanJemaa
GROUP BY PelayanJemaat.Nurut, PelayanJemaat.BidangPelayana

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

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

:

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Power of Reflection: Creating an Object Factory
http://www.eggheadcafe.com/tutorial...9c-164856e47425/power-of-reflection-crea.aspx
 
Again, quoting from an earlier post on 11/20/2009:
=======================================
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
=======================================
Did you try this? What do you see in these text boxes? I have told you
several times to use these with Bidang Pelayanan in the Link Master property
of the subreports. Each copy of the subreport links to a different "year"
text box.

--
Duane Hookom
Microsoft Access MVP


Frank Situmorang said:
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


:

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Power of Reflection: Creating an Object Factory
http://www.eggheadcafe.com/tutorial...9c-164856e47425/power-of-reflection-crea.aspx
.
Click to expand...
 
PK Duane...thanks very much, I will try to figure it out based on your sample. The big difference with mine, that there are 5 copies of subreport while you prebiously said we only need a subreport means one subreport. Probably my problem also is language understanding English.

I will let you know when it works

Thanks in advance

Frank



Duane Hookom wrote:

I have created a demo mdb of this concept. You can download it fromhttp://www.
02-Dec-09

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


:

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
A Developer's Guide To SQL Server Profiler
http://www.eggheadcafe.com/tutorial...d8-ce1600d6db80/a-developers-guide-to-sq.aspx
 
You only need to design one subreport.
I don't think I could have made the number of copies more clear in my replies:

From 11/13
===================================
I would try use 5 copies of the same subreport side-by-side in the detail
section of a main report

From 11/18
===================================
Add 5 copies of the subreport across the main report detail section under
the 5 text boxes.

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

From 11/24
===================================
I would try use 5 copies of the same subreport side-by-side in the detail
section of a main report

From 12/1
===================================
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
 
Duane, thank you very much, this morning I have tried it and it works now, although the line distance is still to be arranged, but I think I could manage it. The important thing is the data is already shown up

Sorry Duane...I bothered you a lot. Yes...this is not because of your missinstruction, but all is because my lack of knowledege and English understanding.

Again thank you very much, hope to see you some day.
If you plan to come to Bali someday ( Bali is part of Indonesia), please let me know, maybe I can help you.

GBU

Frank



Duane Hookom wrote:

You only need to design one subreport.
03-Dec-09

You only need to design one subreport
I do not think I could have made the number of copies more clear in my replies

From 11/1
==================================
I would try use 5 copies of the same subreport side-by-side in the detai
section of a main repor

From 11/1
==================================
Add 5 copies of the subreport across the main report detail section unde
the 5 text boxes

From 11/2
==================================
You would have the link master/child properties of each subreport cop
reference the year and position fields

From 11/2
==================================
I would try use 5 copies of the same subreport side-by-side in the detai
section of a main repor

From 12/
==================================
You should have the 5 text boxes that would display the 5 years across th
top of the detail section in the main report. Below each of these should be
copy of the subrepor

-
Duane Hooko
Microsoft Access MV

:

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
COM and .NET Interoperability [aPress]
http://www.eggheadcafe.com/tutorial...45-c40dcf416b1e/com-and-net-interoperabi.aspx
 
Back
Top