1 Reply Latest reply on Mar 18, 2011 1:55 PM by philmodjunk

    Portal and sub-summary totals not adding up

    EWood

      Title

      Portal and sub-summary totals not adding up

      Post

      I have a database that tracks projects. Each project could have various items funded by 4 different funding sources, which I have a portal set up to keep track of them in the 'funding sources' table. Also there is a payment history portal, which is part of the 'funding sources' table. The 'project' table and 'funding sources' tables are connected via the 'ID_Contact' relationship. Everything works great in the 'project' layout. I have totals for the various 4 funding sources, a 'grand total' that adds all the funding sources together, a 'total paid' that adds up all the payments made, a 'funding commitment' number for the project, and a 'funding released' number for the project.

      Now I have created a Project by County report with sub-summaries and trailing grand totals that categorizes each of the projects by the county they are in and gives the amounts for 'funding commitment', 'funding released', total paid', and 'grand total'. The project by county layout is showing records from my 'project' layout. The sub-summary leading and sub-summary trailing are both sorted by the 'county'. I have the 'county' name being displayed in the sub-summary leading section. Body contains the 'project name', 'funding commitment', 'funding released', 'total paid', and 'grand total' for each project. The problem comes in the sub-summary trailing section. 

      In the sub-summary trailing section, my 'GrandFundingCommitment', 'GrandFundingReleased', 'GrandTotalPaidTally', and 'GrandTotalTally' 3 give me the values from the last project listed in the body. The 'GrandTotalTally' field gives me some some number I can't figure what it is doing. Sometimes it is twice the amount of the 'grand total', sometimes 6 times, sometimes 3 times, etc... and it doesn't increment by one as you go down the list. It literally is all over the board. All of those fields are summary type fields as 'Total of' selected and summarized 'all together'. FYI- the 'grand total' field is a calculation of the sum of the various 4 funding sources.

      I have also just tried using the 'funding commitment', 'funding released', 'total paid', and 'grand total' amounts in the sub-summary trailing section. All of those give me the values from the last project listed in the body.

      And if I do all that, but have the project layout showing records from my 'funding sources' layout, the projects are listed multi-times for each of their various items funded by 4 different funding sources, but the 'funding commitment' and 'funding released' aren't necessarily correct. Nor will a project show if the 'funding commitment', 'funding released', 'total paid', and 'grand total' fields are blank. The 'total paid' and 'grand total' amounts list the same number for each project. The only amounts that are displaying the correct numbers are the 'GrandFundingCommitment', 'GrandFundingReleased', 'GrandTotalPaidTally', and 'GrandTotalTally'. Those are the totals I want to see.

      Any help would greatly be appreciated.

      Note-Project names have been altered for public view.

      Projects2.jpg

        • 1. Re: Portal and sub-summary totals not adding up
          philmodjunk

          Seems like you have this relationship:

          Projects::ID_Contact = FundingSources::ID_Contact

          Don't have any idea what you've set up to handle "payments" here.

          In order to get a sub total in the sub summary part, the field must be a summary field defined in Projects. I can't tell for sure, but suspect that the fields you are using are summary fields defined in FundingSources which, as you've discovered, won't work when the layout is based on projects.

          I think you need calculation fields defined in Projects that compute the Sum of related records in FundingSources. You can then define summary fields to compute the "total of" these calculation fields to get your sub summary located sub totals. I could be wrong though, as I'm doing quite a bit of guessing about how you set up the report shown in your screen shot.