Report using cross tab

Frank Situmorang · Nov 12, 2009

Hello,

I appreciate your help, the following is my cross tab query used in the
report, however some of the records do not show up, could you help me how can
I solve it?. I tried to make the detail can grow, but the result is still the
same. ( some records do not show up)

Thanks in advance

Duane Hookom · Nov 12, 2009

Size doesn't matter. Each record in your report's record source will create
another instance/rendering of the detail section.

-Check the filter property of your report
-Count the records in your record source datasheet
-Add a text box in the report header with a control source of:
=Count(*)

Frank Situmorang · Nov 13, 2009

I do not understand duane why part of the records row is truncated, that is I tried to see in the format property, I have tried all, but the result is stil the same.

This is my Query:
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");

Could it be because of Pivot?. Only the large number of records per group, say deacon group, there are 20 persons, but only 12 shown per year and the very bottom of this group, the part of the name is shown, but part looks like truncated.

Thanks for your help.

Duane Hookom wrote:

Size does not matter.
12-Nov-09

Size does not matter. Each record in your report's record source will creat
another instance/rendering of the detail section

-Check the filter property of your repor
-Count the records in your record source datashee
-Add a text box in the report header with a control source of
=Count(*

-
Duane Hooko
Microsoft Access MV

:

Previous Posts In This Thread:

EggHeadCafe - Software Developer Portal of Choice
No-SOAP, No-Webservice WSE Xml Signature
http://www.eggheadcafe.com/tutorial...901a-bc2341bc3a08/nosoap-nowebservice-ws.aspx

Duane Hookom · Nov 13, 2009

I now see that you are using my Concatenate() function. This is very
significant to your issue. I expect your pivoted value created by
Concatenate() is displaying 255 characters or less. Is this the issue?

Any value displayed as the Value in a crosstab query will be 255 characters
or less since the query is a totals query.

I would try use 5 copies of the same subreport side-by-side in the detail
section of a main report that has a record source grouped by
PelayanJemaat.Nurut and PelayanJemaat.BidangPelayanan. Use five text boxes in
the main report (1 per year) as part of the link master property.

Frank Situmorang · Nov 18, 2009

Yes Duane...the problem could be in the pivot for limited 255 long, so how can we solve the problem, could you help me?

Thanks

Frank

Frank Situmorang wrote:

Some of records is truncated
13-Nov-09

I do not understand duane why part of the records row is truncated, that is I tried to see in the format property, I have tried all, but the result is stil the same.

This is my Query:
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");

Could it be because of Pivot?. Only the large number of records per group, say deacon group, there are 20 persons, but only 12 shown per year and the very bottom of this group, the part of the name is shown, but part looks like truncated.

Thanks for your help.

Previous Posts In This Thread:

EggHeadCafe - Software Developer Portal of Choice
Beer - Words To Live By
http://www.eggheadcafe.com/tutorial...9c25-7085092dc09c/beer--words-to-live-by.aspx

Duane Hookom · Nov 18, 2009

I would create a main report based on a query grouped by Nurut and
BedangPelayanan:

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

Create a subreport based on a query like:
SELECT PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan, NamaPel,
TahunPel, Forms!frmTahundipilih!cboEndYear-[TahunPel] as Yr
FROM PelayanJemaat
WHERE Forms!frmTahundipilih!cboEndYear-[TahunPel] In (0,1,2,3,4);

In the subreport display only the NamaPel field.

Add 5 text boxes across the Report Header section of the main report and set
the control sources and names like:

Name: txt0
Control Source: =0

Name: txt1
Control Source: =1

Name: txt2
Control Source: =2

Name: txt3
Control Source: =3

Name: txt4
Control Source: =4

Display the Nurut and BidangPelayanan fields in the detail section of the
main report.
Add 5 copies of the subreport across the main report detail section under
the 5 text boxes.
Set the Link Master/Child properties to match this pattern:

1st subreport
Link Master: Nurut, BidangPelayanan, txt0
Link Child: Nurut, BidangPelayanan, Yr

2nd subreport
Link Master: Nurut, BidangPelayanan, txt1
Link Child: Nurut, BidangPelayanan, Yr

3rd subreport
Link Master: Nurut, BidangPelayanan, txt2
Link Child: Nurut, BidangPelayanan, Yr

Make sure the main report detail section and the subreport can grow.
--
Duane Hookom
Microsoft Access MVP

Frank Situmorang said:
Yes Duane...the problem could be in the pivot for limited 255 long, so how can we solve the problem, could you help me?

Thanks

Frank

Frank Situmorang wrote:

Some of records is truncated
13-Nov-09

I do not understand duane why part of the records row is truncated, that is I tried to see in the format property, I have tried all, but the result is stil the same.

This is my Query:
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");

Could it be because of Pivot?. Only the large number of records per group, say deacon group, there are 20 persons, but only 12 shown per year and the very bottom of this group, the part of the name is shown, but part looks like truncated.

Thanks for your help.

Previous Posts In This Thread:

EggHeadCafe - Software Developer Portal of Choice
Beer - Words To Live By
http://www.eggheadcafe.com/tutorial...9c25-7085092dc09c/beer--words-to-live-by.aspx
.

Frank Situmorang · Nov 19, 2009

Thanks Duane for your response. I appreciate your help again. Can the year choosed in the combo will still give the last 4 years? for example if we chosed in combo year 2009, the 2008,2007,2006, 2005 will be shown in the column header?

Also my proglems, because I have distributed the database to my other churches, only when I found it later for more persons per group ( like deacons) some/the very bottom rows do not show up or like truncated, So there is no more other ways to follow the pivot one?

OK I will try your suggestion too, but since my expertice Duane is acccounting, only because I like to give something to my churches, I make this as hobbies, so I appreciate like before you give a link so that I can download your sample using your proposal, because I am not expert in this Access.

Thanks very much,

Frank

Duane Hookom wrote:

I would create a main report based on a query grouped by Nurut
18-Nov-09

I would create a main report based on a query grouped by Nurut an
BedangPelayanan

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

Create a subreport based on a query like
SELECT PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan, NamaPel
TahunPel, Forms!frmTahundipilih!cboEndYear-[TahunPel] as Y
FROM PelayanJemaa
WHERE Forms!frmTahundipilih!cboEndYear-[TahunPel] In (0,1,2,3,4)

In the subreport display only the NamaPel field

Add 5 text boxes across the Report Header section of the main report and se
the control sources and names like

Name: txt
Control Source: =

Name: txt
Control Source: =

Name: txt
Control Source: =

Name: txt
Control Source: =

Name: txt
Control Source: =

Display the Nurut and BidangPelayanan fields in the detail section of th
main report
Add 5 copies of the subreport across the main report detail section unde
the 5 text boxes
Set the Link Master/Child properties to match this pattern

1st subrepor
Link Master: Nurut, BidangPelayanan, txt
Link Child: Nurut, BidangPelayanan, Y

2nd subrepor
Link Master: Nurut, BidangPelayanan, txt
Link Child: Nurut, BidangPelayanan, Y

3rd subrepor
Link Master: Nurut, BidangPelayanan, txt
Link Child: Nurut, BidangPelayanan, Y

Make sure the main report detail section and the subreport can grow
-
Duane Hooko
Microsoft Access MV

:

Previous Posts In This Thread:

EggHeadCafe - Software Developer Portal of Choice
How to Get Your Silverlight Pages Indexed By Search Engines
http://www.eggheadcafe.com/tutorial...e-1ca9ad6bcac8/how-to-get-your-silverlig.aspx

Duane Hookom · Nov 19, 2009

You could probably just use the TahunPel in the detail report and set its
criteria to the last 5 years.

Then your txt0 - txt4 would have values like:
=Year(Date())
=Year(Date())-1
=Year(Date())-2

The link child properties would need to be the year field.

--
Duane Hookom
Microsoft Access MVP

Frank Situmorang said:
Thanks Duane for your response. I appreciate your help again. Can the year choosed in the combo will still give the last 4 years? for example if we chosed in combo year 2009, the 2008,2007,2006, 2005 will be shown in the column header?

Also my proglems, because I have distributed the database to my other churches, only when I found it later for more persons per group ( like deacons) some/the very bottom rows do not show up or like truncated, So there is no more other ways to follow the pivot one?

OK I will try your suggestion too, but since my expertice Duane is acccounting, only because I like to give something to my churches, I make this as hobbies, so I appreciate like before you give a link so that I can download your sample using your proposal, because I am not expert in this Access.

Thanks very much,

Frank

Duane Hookom wrote:

I would create a main report based on a query grouped by Nurut
18-Nov-09

I would create a main report based on a query grouped by Nurut and
BedangPelayanan:

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

Create a subreport based on a query like:
SELECT PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan, NamaPel,
TahunPel, Forms!frmTahundipilih!cboEndYear-[TahunPel] as Yr
FROM PelayanJemaat
WHERE Forms!frmTahundipilih!cboEndYear-[TahunPel] In (0,1,2,3,4);

In the subreport display only the NamaPel field.

Add 5 text boxes across the Report Header section of the main report and set
the control sources and names like:

Name: txt0
Control Source: =0

Name: txt1
Control Source: =1

Name: txt2
Control Source: =2

Name: txt3
Control Source: =3

Name: txt4
Control Source: =4

Display the Nurut and BidangPelayanan fields in the detail section of the
main report.
Add 5 copies of the subreport across the main report detail section under
the 5 text boxes.
Set the Link Master/Child properties to match this pattern:

1st subreport
Link Master: Nurut, BidangPelayanan, txt0
Link Child: Nurut, BidangPelayanan, Yr

2nd subreport
Link Master: Nurut, BidangPelayanan, txt1
Link Child: Nurut, BidangPelayanan, Yr

3rd subreport
Link Master: Nurut, BidangPelayanan, txt2
Link Child: Nurut, BidangPelayanan, Yr

Make sure the main report detail section and the subreport can grow.
--
Duane Hookom
Microsoft Access MVP

:

Previous Posts In This Thread:

EggHeadCafe - Software Developer Portal of Choice
How to Get Your Silverlight Pages Indexed By Search Engines
http://www.eggheadcafe.com/tutorial...e-1ca9ad6bcac8/how-to-get-your-silverlig.aspx
.

Frank Situmorang · Nov 20, 2009

Thanks Duane...but are you saying that Tahun dipil should be the header of the main report?, becaue if we compare with your previous sugggestion the year would be the header and the Name of the officers (NamaPel) will be in the detail.

I am a bit confused.

Thanks in advance

Frank

Duane Hookom wrote:

You could probably just use the TahunPel in the detail report and set
19-Nov-09

You could probably just use the TahunPel in the detail report and set it
criteria to the last 5 years

Then your txt0 - txt4 would have values like
=Year(Date()
=Year(Date())-
=Year(Date())-

The link child properties would need to be the year field

-
Duane Hooko
Microsoft Access MV

:

Previous Posts In This Thread:

EggHeadCafe - Software Developer Portal of Choice
Excel Conditional Hiding Without VBA
http://www.eggheadcafe.com/tutorial...15-b0106033e45c/excel-conditional-hiding.aspx

Duane Hookom · Nov 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

Frank Situmorang · Nov 23, 2009

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 valu
to link to the year in the subreport. These 5 text boxes coul

Name: txt
Control Source: =Forms!frmTahundipilih!cboEndYea

Name: txt
Control Source: =Forms!frmTahundipilih!cboEndYear -

Name: txt
Control Source: =Forms!frmTahundipilih!cboEndYear -

Name: txt
Control Source: =Forms!frmTahundipilih!cboEndYear -

Name: txt
Control Source: =Forms!frmTahundipilih!cboEndYear -

-
Duane Hooko
Microsoft Access MV

:

Previous Posts In This Thread:

EggHeadCafe - Software Developer Portal of Choice
Free Icons / Graphics For Business Applications
http://www.eggheadcafe.com/tutorial...1b-a1be20ad935f/free-icons--graphics-for.aspx

Duane Hookom · Nov 23, 2009

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.

Frank Situmorang · Nov 24, 2009

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
TahunPe
FROM PelayanJemaa

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

-
Duane Hooko
Microsoft Access MV

:

Previous Posts In This Thread:

EggHeadCafe - Software Developer Portal of Choice
Encryption On Mobile Devices in C# / .NET
http://www.eggheadcafe.com/tutorial...0-da5581fdf86b/encryption-on-mobile-devi.aspx

Duane Hookom · Nov 24, 2009

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?

Frank Situmorang · Nov 25, 2009

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 th
Year[X] columns in your main report record source. What's wrong with the mai
report record source as I suggested in my reply on 11/18 and the subrepor
record source from 11/23

-
Duane Hooko
Microsoft Access MV

:

Previous Posts In This Thread:

EggHeadCafe - Software Developer Portal of Choice
Look Ma, No DataBase! Bamboo Prevalence
http://www.eggheadcafe.com/tutorial...d77-d3a00233c10a/look-ma-no-database-bam.aspx

Duane Hookom · Nov 25, 2009

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.

Frank Situmorang · Dec 1, 2009

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

:

Previous Posts In This Thread:

Submitted via EggHeadCafe - Software Developer Portal of Choice
XAML "Windows Send Error Report"
http://www.eggheadcafe.com/tutorial...9c6-0e6c5954f2af/xaml-windows-send-error.aspx

Duane Hookom · Dec 1, 2009

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

:

Previous Posts In This Thread:

Submitted via EggHeadCafe - Software Developer Portal of Choice
XAML "Windows Send Error Report"
http://www.eggheadcafe.com/tutorial...9c6-0e6c5954f2af/xaml-windows-send-error.aspx
.

Frank Situmorang · Dec 2, 2009

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

:

Previous Posts In This Thread:

Submitted via EggHeadCafe - Software Developer Portal of Choice
SQL Server Table Variables And Cursorless Cursor
http://www.eggheadcafe.com/tutorial...b-5049a5b56bab/sql-server-table-variable.aspx

Duane Hookom · Dec 2, 2009

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

:

Previous Posts In This Thread:

Submitted via EggHeadCafe - Software Developer Portal of Choice
SQL Server Table Variables And Cursorless Cursor
http://www.eggheadcafe.com/tutorial...b-5049a5b56bab/sql-server-table-variable.aspx
.