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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|