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
|
|||
|
|||
Repost-Still Suffering with Subreports
Is anyone able to help with me with this posted on Aug
19. Thank you, Hello again, still having multiple problems getting four subreports on a main report to print items from each by date. Have made some head way with your help provided but now have another problem. I have subtotal boxes in the date footer on the main form for the amounts from each form i.e. Subreport1 - 208.00. Each one of the total boxes has: =IIf([Subreport1].Report.HasData,[Subreport1].Report! [VGAmounttoContractorTotal],0) so that if the report has no data that the field is populated with 0.00. (This part seems to work okay) I have found that if any one of the subreports doesn't have any data to report i.e. Subreport 1 - 208.00, Subreport 2 - 0.00, the total box for Subreport 1 will skew the data and give me a false number such as 143.00 instead of the 208.00. As long as the reports have information in them, Subreport 1 will produce the correct amount in the total box. Anyone have any ideas about what might be going on. Is there anything specific that subreports have to have make them run correctly. I have found a way to put link/child fields on all of them so that they have an attachment to a date which they didn't have before, they had prevously just been unlinked on the main report. Thanks, Irene |
#2
|
|||
|
|||
Where in the subreport, do you get VGAmounttoContractorTotal from? Is it in
the report footer section or a date footer? How about other totals on subreports? -- Duane Hookom MS Access MVP "IreneJ" wrote in message ... Is anyone able to help with me with this posted on Aug 19. Thank you, Hello again, still having multiple problems getting four subreports on a main report to print items from each by date. Have made some head way with your help provided but now have another problem. I have subtotal boxes in the date footer on the main form for the amounts from each form i.e. Subreport1 - 208.00. Each one of the total boxes has: =IIf([Subreport1].Report.HasData,[Subreport1].Report! [VGAmounttoContractorTotal],0) so that if the report has no data that the field is populated with 0.00. (This part seems to work okay) I have found that if any one of the subreports doesn't have any data to report i.e. Subreport 1 - 208.00, Subreport 2 - 0.00, the total box for Subreport 1 will skew the data and give me a false number such as 143.00 instead of the 208.00. As long as the reports have information in them, Subreport 1 will produce the correct amount in the total box. Anyone have any ideas about what might be going on. Is there anything specific that subreports have to have make them run correctly. I have found a way to put link/child fields on all of them so that they have an attachment to a date which they didn't have before, they had prevously just been unlinked on the main report. Thanks, Irene |
#3
|
|||
|
|||
Good morning,
In subreport 1 (Volunteer), VGAmounttoContractorTotal is in the Date Footer. VGAmounttoContractorTotal is a total field comprised of =[VDAmounttoContractorTotal]+ [DTotalJobCats]. DTotalJobCats is a field comprised of = [TotalGroupRS], a running sum of some job categories from the Date Footer. I checked all the other reports and all the other totals come from the Report Footer. I was having so much trouble summing the job categories and the billing amount to the contractor in the Volunteer report that I moved the total to the Date Footer but I did have it in the Report Footer also at one point but trying to add two fields together was causing me grief so I moved it. Thanks for trying to help me with this. I really appreciate any help, it's making me crazy. I thought that subreporting would be fairly easy but it hasn't been at all! Thanks again, Irene -----Original Message----- Where in the subreport, do you get VGAmounttoContractorTotal from? Is it in the report footer section or a date footer? How about other totals on subreports? -- Duane Hookom MS Access MVP "IreneJ" wrote in message ... Is anyone able to help with me with this posted on Aug 19. Thank you, Hello again, still having multiple problems getting four subreports on a main report to print items from each by date. Have made some head way with your help provided but now have another problem. I have subtotal boxes in the date footer on the main form for the amounts from each form i.e. Subreport1 - 208.00. Each one of the total boxes has: =IIf([Subreport1].Report.HasData,[Subreport1].Report! [VGAmounttoContractorTotal],0) so that if the report has no data that the field is populated with 0.00. (This part seems to work okay) I have found that if any one of the subreports doesn't have any data to report i.e. Subreport 1 - 208.00, Subreport 2 - 0.00, the total box for Subreport 1 will skew the data and give me a false number such as 143.00 instead of the 208.00. As long as the reports have information in them, Subreport 1 will produce the correct amount in the total box. Anyone have any ideas about what might be going on. Is there anything specific that subreports have to have make them run correctly. I have found a way to put link/child fields on all of them so that they have an attachment to a date which they didn't have before, they had prevously just been unlinked on the main report. Thanks, Irene . |
#4
|
|||
|
|||
Since you have link master child set to the date field, I would place the
totals in the subreport in the report footer. Is there a reason why you are using a running sum for TotalGroupRS rather than a regular Sum()? -- Duane Hookom MS Access MVP "IreneJ" wrote in message ... Good morning, In subreport 1 (Volunteer), VGAmounttoContractorTotal is in the Date Footer. VGAmounttoContractorTotal is a total field comprised of =[VDAmounttoContractorTotal]+ [DTotalJobCats]. DTotalJobCats is a field comprised of = [TotalGroupRS], a running sum of some job categories from the Date Footer. I checked all the other reports and all the other totals come from the Report Footer. I was having so much trouble summing the job categories and the billing amount to the contractor in the Volunteer report that I moved the total to the Date Footer but I did have it in the Report Footer also at one point but trying to add two fields together was causing me grief so I moved it. Thanks for trying to help me with this. I really appreciate any help, it's making me crazy. I thought that subreporting would be fairly easy but it hasn't been at all! Thanks again, Irene -----Original Message----- Where in the subreport, do you get VGAmounttoContractorTotal from? Is it in the report footer section or a date footer? How about other totals on subreports? -- Duane Hookom MS Access MVP "IreneJ" wrote in message ... Is anyone able to help with me with this posted on Aug 19. Thank you, Hello again, still having multiple problems getting four subreports on a main report to print items from each by date. Have made some head way with your help provided but now have another problem. I have subtotal boxes in the date footer on the main form for the amounts from each form i.e. Subreport1 - 208.00. Each one of the total boxes has: =IIf([Subreport1].Report.HasData,[Subreport1].Report! [VGAmounttoContractorTotal],0) so that if the report has no data that the field is populated with 0.00. (This part seems to work okay) I have found that if any one of the subreports doesn't have any data to report i.e. Subreport 1 - 208.00, Subreport 2 - 0.00, the total box for Subreport 1 will skew the data and give me a false number such as 143.00 instead of the 208.00. As long as the reports have information in them, Subreport 1 will produce the correct amount in the total box. Anyone have any ideas about what might be going on. Is there anything specific that subreports have to have make them run correctly. I have found a way to put link/child fields on all of them so that they have an attachment to a date which they didn't have before, they had prevously just been unlinked on the main report. Thanks, Irene . |
#5
|
|||
|
|||
Good morning, thank you for responding. On each install
that we do, an installer can be paid extra job categories, so I built a small subreport on the Volunteer subreport that would take each group of values for a day and put them together, the only way I knew to total, say a whole day at a time, with 10 installs that all have a lot of job cats was to use a running sum. Example Basic Install Job Cat 1 Job Cat 2 Job Cat 3 $39.00 20.00 15.00 30.00 Total 65.00 $39.00 15.00 30.00 Total 45.00 Total Basic Total Job Cats 78.00 65.00+45.00 = 110.00 It does seem that my problem seems to be attached to these job categories though I think. Yesterday I tried various methods of moving a total box to the report footer (or even the date footer) of the Volunteer subreport. Each time I do it and preview it I can see the proper subtotal, say $110.00 properly. But the second I take that field name and go to the main report and put it in the date footer I get a skewed number, i.e. just like the example above, I would only get a 45.00 showing up instead of the $110.00, but if I move it to the Report Footer on the Main Report it reports at $110.00. Thanks again, I really appreciate this. I struggle with what appears to be 'being just about there, but not quite' and it's really frustrating. Regards, IEJ -----Original Message----- Since you have link master child set to the date field, I would place the totals in the subreport in the report footer. Is there a reason why you are using a running sum for TotalGroupRS rather than a regular Sum()? -- Duane Hookom MS Access MVP "IreneJ" wrote in message ... Good morning, In subreport 1 (Volunteer), VGAmounttoContractorTotal is in the Date Footer. VGAmounttoContractorTotal is a total field comprised of =[VDAmounttoContractorTotal]+ [DTotalJobCats]. DTotalJobCats is a field comprised of = [TotalGroupRS], a running sum of some job categories from the Date Footer. I checked all the other reports and all the other totals come from the Report Footer. I was having so much trouble summing the job categories and the billing amount to the contractor in the Volunteer report that I moved the total to the Date Footer but I did have it in the Report Footer also at one point but trying to add two fields together was causing me grief so I moved it. Thanks for trying to help me with this. I really appreciate any help, it's making me crazy. I thought that subreporting would be fairly easy but it hasn't been at all! Thanks again, Irene -----Original Message----- Where in the subreport, do you get VGAmounttoContractorTotal from? Is it in the report footer section or a date footer? How about other totals on subreports? -- Duane Hookom MS Access MVP "IreneJ" wrote in message ... Is anyone able to help with me with this posted on Aug 19. Thank you, Hello again, still having multiple problems getting four subreports on a main report to print items from each by date. Have made some head way with your help provided but now have another problem. I have subtotal boxes in the date footer on the main form for the amounts from each form i.e. Subreport1 - 208.00. Each one of the total boxes has: =IIf([Subreport1].Report.HasData, [Subreport1].Report! [VGAmounttoContractorTotal],0) so that if the report has no data that the field is populated with 0.00. (This part seems to work okay) I have found that if any one of the subreports doesn't have any data to report i.e. Subreport 1 - 208.00, Subreport 2 - 0.00, the total box for Subreport 1 will skew the data and give me a false number such as 143.00 instead of the 208.00. As long as the reports have information in them, Subreport 1 will produce the correct amount in the total box. Anyone have any ideas about what might be going on. Is there anything specific that subreports have to have make them run correctly. I have found a way to put link/child fields on all of them so that they have an attachment to a date which they didn't have before, they had prevously just been unlinked on the main report. Thanks, Irene . . |
#6
|
|||
|
|||
I would not have fields for different Job Cats (but that is an entirely
different issue). I think you can kill your running sum and then sum your job cats in the footer with the expression: =Sum( Nz(JobCat1,0) + Nz(JobCat2,0) + Nz(JobCat3,0) ) -- Duane Hookom MS Access MVP -- "IreneJ" wrote in message ... Good morning, thank you for responding. On each install that we do, an installer can be paid extra job categories, so I built a small subreport on the Volunteer subreport that would take each group of values for a day and put them together, the only way I knew to total, say a whole day at a time, with 10 installs that all have a lot of job cats was to use a running sum. Example Basic Install Job Cat 1 Job Cat 2 Job Cat 3 $39.00 20.00 15.00 30.00 Total 65.00 $39.00 15.00 30.00 Total 45.00 Total Basic Total Job Cats 78.00 65.00+45.00 = 110.00 It does seem that my problem seems to be attached to these job categories though I think. Yesterday I tried various methods of moving a total box to the report footer (or even the date footer) of the Volunteer subreport. Each time I do it and preview it I can see the proper subtotal, say $110.00 properly. But the second I take that field name and go to the main report and put it in the date footer I get a skewed number, i.e. just like the example above, I would only get a 45.00 showing up instead of the $110.00, but if I move it to the Report Footer on the Main Report it reports at $110.00. Thanks again, I really appreciate this. I struggle with what appears to be 'being just about there, but not quite' and it's really frustrating. Regards, IEJ -----Original Message----- Since you have link master child set to the date field, I would place the totals in the subreport in the report footer. Is there a reason why you are using a running sum for TotalGroupRS rather than a regular Sum()? -- Duane Hookom MS Access MVP "IreneJ" wrote in message ... Good morning, In subreport 1 (Volunteer), VGAmounttoContractorTotal is in the Date Footer. VGAmounttoContractorTotal is a total field comprised of =[VDAmounttoContractorTotal]+ [DTotalJobCats]. DTotalJobCats is a field comprised of = [TotalGroupRS], a running sum of some job categories from the Date Footer. I checked all the other reports and all the other totals come from the Report Footer. I was having so much trouble summing the job categories and the billing amount to the contractor in the Volunteer report that I moved the total to the Date Footer but I did have it in the Report Footer also at one point but trying to add two fields together was causing me grief so I moved it. Thanks for trying to help me with this. I really appreciate any help, it's making me crazy. I thought that subreporting would be fairly easy but it hasn't been at all! Thanks again, Irene -----Original Message----- Where in the subreport, do you get VGAmounttoContractorTotal from? Is it in the report footer section or a date footer? How about other totals on subreports? -- Duane Hookom MS Access MVP "IreneJ" wrote in message ... Is anyone able to help with me with this posted on Aug 19. Thank you, Hello again, still having multiple problems getting four subreports on a main report to print items from each by date. Have made some head way with your help provided but now have another problem. I have subtotal boxes in the date footer on the main form for the amounts from each form i.e. Subreport1 - 208.00. Each one of the total boxes has: =IIf([Subreport1].Report.HasData, [Subreport1].Report! [VGAmounttoContractorTotal],0) so that if the report has no data that the field is populated with 0.00. (This part seems to work okay) I have found that if any one of the subreports doesn't have any data to report i.e. Subreport 1 - 208.00, Subreport 2 - 0.00, the total box for Subreport 1 will skew the data and give me a false number such as 143.00 instead of the 208.00. As long as the reports have information in them, Subreport 1 will produce the correct amount in the total box. Anyone have any ideas about what might be going on. Is there anything specific that subreports have to have make them run correctly. I have found a way to put link/child fields on all of them so that they have an attachment to a date which they didn't have before, they had prevously just been unlinked on the main report. Thanks, Irene . . |
#7
|
|||
|
|||
Good morning, Thank you, but I can't figure out how to
take the running sum off. The JobCat report is a subreport on Volunteer and is a based on a crosstab query that shows a 1 for each job cat and the ContractorRate field for each of the values i.e. $14.00 for a 1 etc., then there is a Sum([ContractorRate]) in the CallID footer for each install, I called it Total. I then put a a textbox on the Volunteer report with =JobCat.Report! Total to get the info from the subreport and called it CategoryTotal. Beside that I put another text box to total the values from the CategoryTotal field, I called that field JobCatsRunningSum and it gets its info from CategoryTotal and has a running sum Over Group. I then put a box in the Date Footer called DTotalJobCats and getting its info from =JobCatsRunningSum. Then I put another box in the Report Footer called GTotalJobCats and it gets its info from DTotalJobCats. Is there a way to bypass these extra boxes and get info from a subreport on a subreport to report directly to the date footer i.e. could you put Volunteer.Report.JobCat.Report.CategoryTotal. Thanks so much, -----Original Message----- I would not have fields for different Job Cats (but that is an entirely different issue). I think you can kill your running sum and then sum your job cats in the footer with the expression: =Sum( Nz(JobCat1,0) + Nz(JobCat2,0) + Nz(JobCat3,0) ) -- Duane Hookom MS Access MVP -- "IreneJ" wrote in message ... Good morning, thank you for responding. On each install that we do, an installer can be paid extra job categories, so I built a small subreport on the Volunteer subreport that would take each group of values for a day and put them together, the only way I knew to total, say a whole day at a time, with 10 installs that all have a lot of job cats was to use a running sum. Example Basic Install Job Cat 1 Job Cat 2 Job Cat 3 $39.00 20.00 15.00 30.00 Total 65.00 $39.00 15.00 30.00 Total 45.00 Total Basic Total Job Cats 78.00 65.00+45.00 = 110.00 It does seem that my problem seems to be attached to these job categories though I think. Yesterday I tried various methods of moving a total box to the report footer (or even the date footer) of the Volunteer subreport. Each time I do it and preview it I can see the proper subtotal, say $110.00 properly. But the second I take that field name and go to the main report and put it in the date footer I get a skewed number, i.e. just like the example above, I would only get a 45.00 showing up instead of the $110.00, but if I move it to the Report Footer on the Main Report it reports at $110.00. Thanks again, I really appreciate this. I struggle with what appears to be 'being just about there, but not quite' and it's really frustrating. Regards, IEJ -----Original Message----- Since you have link master child set to the date field, I would place the totals in the subreport in the report footer. Is there a reason why you are using a running sum for TotalGroupRS rather than a regular Sum()? -- Duane Hookom MS Access MVP "IreneJ" wrote in message ... Good morning, In subreport 1 (Volunteer), VGAmounttoContractorTotal is in the Date Footer. VGAmounttoContractorTotal is a total field comprised of =[VDAmounttoContractorTotal]+ [DTotalJobCats]. DTotalJobCats is a field comprised of = [TotalGroupRS], a running sum of some job categories from the Date Footer. I checked all the other reports and all the other totals come from the Report Footer. I was having so much trouble summing the job categories and the billing amount to the contractor in the Volunteer report that I moved the total to the Date Footer but I did have it in the Report Footer also at one point but trying to add two fields together was causing me grief so I moved it. Thanks for trying to help me with this. I really appreciate any help, it's making me crazy. I thought that subreporting would be fairly easy but it hasn't been at all! Thanks again, Irene -----Original Message----- Where in the subreport, do you get VGAmounttoContractorTotal from? Is it in the report footer section or a date footer? How about other totals on subreports? -- Duane Hookom MS Access MVP "IreneJ" wrote in message ... Is anyone able to help with me with this posted on Aug 19. Thank you, Hello again, still having multiple problems getting four subreports on a main report to print items from each by date. Have made some head way with your help provided but now have another problem. I have subtotal boxes in the date footer on the main form for the amounts from each form i.e. Subreport1 - 208.00. Each one of the total boxes has: =IIf([Subreport1].Report.HasData, [Subreport1].Report! [VGAmounttoContractorTotal],0) so that if the report has no data that the field is populated with 0.00. (This part seems to work okay) I have found that if any one of the subreports doesn't have any data to report i.e. Subreport 1 - 208.00, Subreport 2 - 0.00, the total box for Subreport 1 will skew the data and give me a false number such as 143.00 instead of the 208.00. As long as the reports have information in them, Subreport 1 will produce the correct amount in the total box. Anyone have any ideas about what might be going on. Is there anything specific that subreports have to have make them run correctly. I have found a way to put link/child fields on all of them so that they have an attachment to a date which they didn't have before, they had prevously just been unlinked on the main report. Thanks, Irene . . . |
#8
|
|||
|
|||
Since your subreport is returning from one date only, again, I would remove
the date footer from the subreport and use only the subreport report footer. Do I dare ask how you are creating a report based on a crosstab? Are you hard-coding in Column Headings in your crosstab? -- Duane Hookom MS Access MVP "IreneJ" wrote in message ... Good morning, Thank you, but I can't figure out how to take the running sum off. The JobCat report is a subreport on Volunteer and is a based on a crosstab query that shows a 1 for each job cat and the ContractorRate field for each of the values i.e. $14.00 for a 1 etc., then there is a Sum([ContractorRate]) in the CallID footer for each install, I called it Total. I then put a a textbox on the Volunteer report with =JobCat.Report! Total to get the info from the subreport and called it CategoryTotal. Beside that I put another text box to total the values from the CategoryTotal field, I called that field JobCatsRunningSum and it gets its info from CategoryTotal and has a running sum Over Group. I then put a box in the Date Footer called DTotalJobCats and getting its info from =JobCatsRunningSum. Then I put another box in the Report Footer called GTotalJobCats and it gets its info from DTotalJobCats. Is there a way to bypass these extra boxes and get info from a subreport on a subreport to report directly to the date footer i.e. could you put Volunteer.Report.JobCat.Report.CategoryTotal. Thanks so much, -----Original Message----- I would not have fields for different Job Cats (but that is an entirely different issue). I think you can kill your running sum and then sum your job cats in the footer with the expression: =Sum( Nz(JobCat1,0) + Nz(JobCat2,0) + Nz(JobCat3,0) ) -- Duane Hookom MS Access MVP -- "IreneJ" wrote in message ... Good morning, thank you for responding. On each install that we do, an installer can be paid extra job categories, so I built a small subreport on the Volunteer subreport that would take each group of values for a day and put them together, the only way I knew to total, say a whole day at a time, with 10 installs that all have a lot of job cats was to use a running sum. Example Basic Install Job Cat 1 Job Cat 2 Job Cat 3 $39.00 20.00 15.00 30.00 Total 65.00 $39.00 15.00 30.00 Total 45.00 Total Basic Total Job Cats 78.00 65.00+45.00 = 110.00 It does seem that my problem seems to be attached to these job categories though I think. Yesterday I tried various methods of moving a total box to the report footer (or even the date footer) of the Volunteer subreport. Each time I do it and preview it I can see the proper subtotal, say $110.00 properly. But the second I take that field name and go to the main report and put it in the date footer I get a skewed number, i.e. just like the example above, I would only get a 45.00 showing up instead of the $110.00, but if I move it to the Report Footer on the Main Report it reports at $110.00. Thanks again, I really appreciate this. I struggle with what appears to be 'being just about there, but not quite' and it's really frustrating. Regards, IEJ -----Original Message----- Since you have link master child set to the date field, I would place the totals in the subreport in the report footer. Is there a reason why you are using a running sum for TotalGroupRS rather than a regular Sum()? -- Duane Hookom MS Access MVP "IreneJ" wrote in message ... Good morning, In subreport 1 (Volunteer), VGAmounttoContractorTotal is in the Date Footer. VGAmounttoContractorTotal is a total field comprised of =[VDAmounttoContractorTotal]+ [DTotalJobCats]. DTotalJobCats is a field comprised of = [TotalGroupRS], a running sum of some job categories from the Date Footer. I checked all the other reports and all the other totals come from the Report Footer. I was having so much trouble summing the job categories and the billing amount to the contractor in the Volunteer report that I moved the total to the Date Footer but I did have it in the Report Footer also at one point but trying to add two fields together was causing me grief so I moved it. Thanks for trying to help me with this. I really appreciate any help, it's making me crazy. I thought that subreporting would be fairly easy but it hasn't been at all! Thanks again, Irene -----Original Message----- Where in the subreport, do you get VGAmounttoContractorTotal from? Is it in the report footer section or a date footer? How about other totals on subreports? -- Duane Hookom MS Access MVP "IreneJ" wrote in message ... Is anyone able to help with me with this posted on Aug 19. Thank you, Hello again, still having multiple problems getting four subreports on a main report to print items from each by date. Have made some head way with your help provided but now have another problem. I have subtotal boxes in the date footer on the main form for the amounts from each form i.e. Subreport1 - 208.00. Each one of the total boxes has: =IIf([Subreport1].Report.HasData, [Subreport1].Report! [VGAmounttoContractorTotal],0) so that if the report has no data that the field is populated with 0.00. (This part seems to work okay) I have found that if any one of the subreports doesn't have any data to report i.e. Subreport 1 - 208.00, Subreport 2 - 0.00, the total box for Subreport 1 will skew the data and give me a false number such as 143.00 instead of the 208.00. As long as the reports have information in them, Subreport 1 will produce the correct amount in the total box. Anyone have any ideas about what might be going on. Is there anything specific that subreports have to have make them run correctly. I have found a way to put link/child fields on all of them so that they have an attachment to a date which they didn't have before, they had prevously just been unlinked on the main report. Thanks, Irene . . . |
#9
|
|||
|
|||
Thank you Duane I will remove the date footer in the
subreport...yup built a report based on a crosstab and yup hard coded the column headings. Is this unusual? It was the only way I could get the data to preview in the way I wanted them to show. I'll go take out the date footer and let you know how I make out. When calling a subreport on a subreport is there a proper naming convention. I have only been using the subreport name i.e. Volunteer.Report!nameoffieldoffofsubreport i.e. JobCatsRunningSum. Should this be something else, i.e. Volunteer.Report.JobCat.Report!JobCatsRunningSum. Thanks so much, IEJ -----Original Message----- Since your subreport is returning from one date only, again, I would remove the date footer from the subreport and use only the subreport report footer. Do I dare ask how you are creating a report based on a crosstab? Are you hard-coding in Column Headings in your crosstab? -- Duane Hookom MS Access MVP "IreneJ" wrote in message ... Good morning, Thank you, but I can't figure out how to take the running sum off. The JobCat report is a subreport on Volunteer and is a based on a crosstab query that shows a 1 for each job cat and the ContractorRate field for each of the values i.e. $14.00 for a 1 etc., then there is a Sum([ContractorRate]) in the CallID footer for each install, I called it Total. I then put a a textbox on the Volunteer report with =JobCat.Report! Total to get the info from the subreport and called it CategoryTotal. Beside that I put another text box to total the values from the CategoryTotal field, I called that field JobCatsRunningSum and it gets its info from CategoryTotal and has a running sum Over Group. I then put a box in the Date Footer called DTotalJobCats and getting its info from =JobCatsRunningSum. Then I put another box in the Report Footer called GTotalJobCats and it gets its info from DTotalJobCats. Is there a way to bypass these extra boxes and get info from a subreport on a subreport to report directly to the date footer i.e. could you put Volunteer.Report.JobCat.Report.CategoryTotal. Thanks so much, -----Original Message----- I would not have fields for different Job Cats (but that is an entirely different issue). I think you can kill your running sum and then sum your job cats in the footer with the expression: =Sum( Nz(JobCat1,0) + Nz(JobCat2,0) + Nz(JobCat3,0) ) -- Duane Hookom MS Access MVP -- "IreneJ" wrote in message ... Good morning, thank you for responding. On each install that we do, an installer can be paid extra job categories, so I built a small subreport on the Volunteer subreport that would take each group of values for a day and put them together, the only way I knew to total, say a whole day at a time, with 10 installs that all have a lot of job cats was to use a running sum. Example Basic Install Job Cat 1 Job Cat 2 Job Cat 3 $39.00 20.00 15.00 30.00 Total 65.00 $39.00 15.00 30.00 Total 45.00 Total Basic Total Job Cats 78.00 65.00+45.00 = 110.00 It does seem that my problem seems to be attached to these job categories though I think. Yesterday I tried various methods of moving a total box to the report footer (or even the date footer) of the Volunteer subreport. Each time I do it and preview it I can see the proper subtotal, say $110.00 properly. But the second I take that field name and go to the main report and put it in the date footer I get a skewed number, i.e. just like the example above, I would only get a 45.00 showing up instead of the $110.00, but if I move it to the Report Footer on the Main Report it reports at $110.00. Thanks again, I really appreciate this. I struggle with what appears to be 'being just about there, but not quite' and it's really frustrating. Regards, IEJ -----Original Message----- Since you have link master child set to the date field, I would place the totals in the subreport in the report footer. Is there a reason why you are using a running sum for TotalGroupRS rather than a regular Sum()? -- Duane Hookom MS Access MVP "IreneJ" wrote in message ... Good morning, In subreport 1 (Volunteer), VGAmounttoContractorTotal is in the Date Footer. VGAmounttoContractorTotal is a total field comprised of =[VDAmounttoContractorTotal]+ [DTotalJobCats]. DTotalJobCats is a field comprised of = [TotalGroupRS], a running sum of some job categories from the Date Footer. I checked all the other reports and all the other totals come from the Report Footer. I was having so much trouble summing the job categories and the billing amount to the contractor in the Volunteer report that I moved the total to the Date Footer but I did have it in the Report Footer also at one point but trying to add two fields together was causing me grief so I moved it. Thanks for trying to help me with this. I really appreciate any help, it's making me crazy. I thought that subreporting would be fairly easy but it hasn't been at all! Thanks again, Irene -----Original Message----- Where in the subreport, do you get VGAmounttoContractorTotal from? Is it in the report footer section or a date footer? How about other totals on subreports? -- Duane Hookom MS Access MVP "IreneJ" wrote in message ... Is anyone able to help with me with this posted on Aug 19. Thank you, Hello again, still having multiple problems getting four subreports on a main report to print items from each by date. Have made some head way with your help provided but now have another problem. I have subtotal boxes in the date footer on the main form for the amounts from each form i.e. Subreport1 - 208.00. Each one of the total boxes has: =IIf([Subreport1].Report.HasData, [Subreport1].Report! [VGAmounttoContractorTotal],0) so that if the report has no data that the field is populated with 0.00. (This part seems to work okay) I have found that if any one of the subreports doesn't have any data to report i.e. Subreport 1 - 208.00, Subreport 2 - 0.00, the total box for Subreport 1 will skew the data and give me a false number such as 143.00 instead of the 208.00. As long as the reports have information in them, Subreport 1 will produce the correct amount in the total box. Anyone have any ideas about what might be going on. Is there anything specific that subreports have to have make them run correctly. I have found a way to put link/child fields on all of them so that they have an attachment to a date which they didn't have before, they had prevously just been unlinked on the main report. Thanks, Irene . . . . |
#10
|
|||
|
|||
Hi,
Sorry it took a while to back to this thread. I was on a short vacation to Mackinac Island... no computers (and I survived). Hard-coding column headings in the crosstab query is the only reliable method that I am aware of. I haven't had to nest two subreports on a report. I always use names like srpt.... and rpt.... for subreport and report prefixes. -- Duane Hookom MS Access MVP "IreneJ" wrote in message ... Thank you Duane I will remove the date footer in the subreport...yup built a report based on a crosstab and yup hard coded the column headings. Is this unusual? It was the only way I could get the data to preview in the way I wanted them to show. I'll go take out the date footer and let you know how I make out. When calling a subreport on a subreport is there a proper naming convention. I have only been using the subreport name i.e. Volunteer.Report!nameoffieldoffofsubreport i.e. JobCatsRunningSum. Should this be something else, i.e. Volunteer.Report.JobCat.Report!JobCatsRunningSum. Thanks so much, IEJ -----Original Message----- Since your subreport is returning from one date only, again, I would remove the date footer from the subreport and use only the subreport report footer. Do I dare ask how you are creating a report based on a crosstab? Are you hard-coding in Column Headings in your crosstab? -- Duane Hookom MS Access MVP "IreneJ" wrote in message ... Good morning, Thank you, but I can't figure out how to take the running sum off. The JobCat report is a subreport on Volunteer and is a based on a crosstab query that shows a 1 for each job cat and the ContractorRate field for each of the values i.e. $14.00 for a 1 etc., then there is a Sum([ContractorRate]) in the CallID footer for each install, I called it Total. I then put a a textbox on the Volunteer report with =JobCat.Report! Total to get the info from the subreport and called it CategoryTotal. Beside that I put another text box to total the values from the CategoryTotal field, I called that field JobCatsRunningSum and it gets its info from CategoryTotal and has a running sum Over Group. I then put a box in the Date Footer called DTotalJobCats and getting its info from =JobCatsRunningSum. Then I put another box in the Report Footer called GTotalJobCats and it gets its info from DTotalJobCats. Is there a way to bypass these extra boxes and get info from a subreport on a subreport to report directly to the date footer i.e. could you put Volunteer.Report.JobCat.Report.CategoryTotal. Thanks so much, -----Original Message----- I would not have fields for different Job Cats (but that is an entirely different issue). I think you can kill your running sum and then sum your job cats in the footer with the expression: =Sum( Nz(JobCat1,0) + Nz(JobCat2,0) + Nz(JobCat3,0) ) -- Duane Hookom MS Access MVP -- "IreneJ" wrote in message ... Good morning, thank you for responding. On each install that we do, an installer can be paid extra job categories, so I built a small subreport on the Volunteer subreport that would take each group of values for a day and put them together, the only way I knew to total, say a whole day at a time, with 10 installs that all have a lot of job cats was to use a running sum. Example Basic Install Job Cat 1 Job Cat 2 Job Cat 3 $39.00 20.00 15.00 30.00 Total 65.00 $39.00 15.00 30.00 Total 45.00 Total Basic Total Job Cats 78.00 65.00+45.00 = 110.00 It does seem that my problem seems to be attached to these job categories though I think. Yesterday I tried various methods of moving a total box to the report footer (or even the date footer) of the Volunteer subreport. Each time I do it and preview it I can see the proper subtotal, say $110.00 properly. But the second I take that field name and go to the main report and put it in the date footer I get a skewed number, i.e. just like the example above, I would only get a 45.00 showing up instead of the $110.00, but if I move it to the Report Footer on the Main Report it reports at $110.00. Thanks again, I really appreciate this. I struggle with what appears to be 'being just about there, but not quite' and it's really frustrating. Regards, IEJ -----Original Message----- Since you have link master child set to the date field, I would place the totals in the subreport in the report footer. Is there a reason why you are using a running sum for TotalGroupRS rather than a regular Sum()? -- Duane Hookom MS Access MVP "IreneJ" wrote in message ... Good morning, In subreport 1 (Volunteer), VGAmounttoContractorTotal is in the Date Footer. VGAmounttoContractorTotal is a total field comprised of =[VDAmounttoContractorTotal]+ [DTotalJobCats]. DTotalJobCats is a field comprised of = [TotalGroupRS], a running sum of some job categories from the Date Footer. I checked all the other reports and all the other totals come from the Report Footer. I was having so much trouble summing the job categories and the billing amount to the contractor in the Volunteer report that I moved the total to the Date Footer but I did have it in the Report Footer also at one point but trying to add two fields together was causing me grief so I moved it. Thanks for trying to help me with this. I really appreciate any help, it's making me crazy. I thought that subreporting would be fairly easy but it hasn't been at all! Thanks again, Irene -----Original Message----- Where in the subreport, do you get VGAmounttoContractorTotal from? Is it in the report footer section or a date footer? How about other totals on subreports? -- Duane Hookom MS Access MVP "IreneJ" wrote in message ... Is anyone able to help with me with this posted on Aug 19. Thank you, Hello again, still having multiple problems getting four subreports on a main report to print items from each by date. Have made some head way with your help provided but now have another problem. I have subtotal boxes in the date footer on the main form for the amounts from each form i.e. Subreport1 - 208.00. Each one of the total boxes has: =IIf([Subreport1].Report.HasData, [Subreport1].Report! [VGAmounttoContractorTotal],0) so that if the report has no data that the field is populated with 0.00. (This part seems to work okay) I have found that if any one of the subreports doesn't have any data to report i.e. Subreport 1 - 208.00, Subreport 2 - 0.00, the total box for Subreport 1 will skew the data and give me a false number such as 143.00 instead of the 208.00. As long as the reports have information in them, Subreport 1 will produce the correct amount in the total box. Anyone have any ideas about what might be going on. Is there anything specific that subreports have to have make them run correctly. I have found a way to put link/child fields on all of them so that they have an attachment to a date which they didn't have before, they had prevously just been unlinked on the main report. Thanks, Irene . . . . |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
REPOST - Keep subreports on one page | Rick B | Setting Up & Running Reports | 1 | August 2nd, 2004 06:29 PM |
Repost - Using SUMIF and COUNTA | Max | Worksheet Functions | 4 | May 4th, 2004 07:07 AM |
Repost - Using SUMIF and COUNTA | Peter | Worksheet Functions | 0 | May 3rd, 2004 01:36 PM |