A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Report using cross tab



 
 
Thread Tools Display Modes
  #1  
Old November 12th, 2009, 08:45 AM posted to microsoft.public.access.reports
Frank Situmorang[_3_]
external usenet poster
 
Posts: 61
Default Report using cross tab

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
--
Frank Situmorang
  #2  
Old November 12th, 2009, 03:49 PM posted to microsoft.public.access.reports
Duane Hookom
external usenet poster
 
Posts: 7,177
Default Report using cross tab

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(*)

--
Duane Hookom
Microsoft Access MVP


"Frank Situmorang" wrote:

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

  #3  
Old November 13th, 2009, 09:27 AM posted to microsoft.public.access.reports
Frank Situmorang[_4_]
external usenet poster
 
Posts: 24
Default Some of records is truncated

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 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(*)

--
Duane Hookom
Microsoft Access MVP


"Frank Situmorang" wrote:

Previous Posts In This Thread:

EggHeadCafe - Software Developer Portal of Choice
No-SOAP, No-Webservice WSE Xml Signature
http://www.eggheadcafe.com/tutorials...ervice-ws.aspx
  #4  
Old November 13th, 2009, 04:07 PM posted to microsoft.public.access.reports
Duane Hookom
external usenet poster
 
Posts: 7,177
Default Some of records is truncated

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.

--
Duane Hookom
Microsoft Access MVP


"Frank Situmorang" wrote:

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 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(*)

--
Duane Hookom
Microsoft Access MVP


"Frank Situmorang" wrote:

Previous Posts In This Thread:

EggHeadCafe - Software Developer Portal of Choice
No-SOAP, No-Webservice WSE Xml Signature
http://www.eggheadcafe.com/tutorials...ervice-ws.aspx
.

  #5  
Old November 18th, 2009, 04:32 AM posted to microsoft.public.access.reports
Frank Situmorang[_4_]
external usenet poster
 
Posts: 24
Default Crosstab Query does not show up all ot the reports in the bottom row

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/tutorials...o-live-by.aspx
  #6  
Old November 18th, 2009, 05:02 PM posted to microsoft.public.access.reports
Duane Hookom
external usenet poster
 
Posts: 7,177
Default Crosstab Query does not show up all ot the reports in the bottom r

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" wrote:

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/tutorials...o-live-by.aspx
.

  #7  
Old November 19th, 2009, 03:16 AM posted to microsoft.public.access.reports
Frank Situmorang[_4_]
external usenet poster
 
Posts: 24
Default Can it still be dynamic in choosing the year to be reported


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


"Frank Situmorang" wrote:

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/tutorials...silverlig.aspx
  #8  
Old November 19th, 2009, 07:50 AM posted to microsoft.public.access.reports
Duane Hookom
external usenet poster
 
Posts: 7,177
Default Can it still be dynamic in choosing the year to be reported

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" wrote:


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


"Frank Situmorang" wrote:

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/tutorials...silverlig.aspx
.

  #9  
Old November 20th, 2009, 11:29 AM posted to microsoft.public.access.reports
Frank Situmorang[_4_]
external usenet poster
 
Posts: 24
Default are you saying that Tahun dipil should be the header of the main report

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 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" wrote:

Previous Posts In This Thread:

EggHeadCafe - Software Developer Portal of Choice
Excel Conditional Hiding Without VBA
http://www.eggheadcafe.com/tutorials...al-hiding.aspx
  #10  
Old November 20th, 2009, 05:26 PM posted to microsoft.public.access.reports
Duane Hookom
external usenet poster
 
Posts: 7,177
Default are you saying that Tahun dipil should be the header of the main r

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:

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 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" wrote:

Previous Posts In This Thread:

EggHeadCafe - Software Developer Portal of Choice
Excel Conditional Hiding Without VBA
http://www.eggheadcafe.com/tutorials...al-hiding.aspx
.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:39 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.