AnsweredAssumed Answered

Portal and sub-summary totals not adding up

Question asked by EWood on Mar 18, 2011
Latest reply on Mar 18, 2011 by philmodjunk


Portal and sub-summary totals not adding up


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.