How to restrict report totals?

[Adrianna]

Okay...I consider my self to be fairly proficient in access and ths should be simple, but I guess I'm having a "DAY".

Here is the deal. I have a budget report that I've set to group by department. Within the department footer, it displays the total budget, total debts, and department balace (for each unit). The problem occurs when I try to run the Overall totals in the Report Footer. I figured that it would be simple =sum([department budget]), but that doesn't work. The number is huge. I tried =([department budget])just to humor myself and see what it came up with and that provides me with the department budget of the last department in the table.
Finally I figured out that =sum([department budget]) does not work properly because it counts the total department budget for each transaction within the department (meaning that if training has a budget of 20,000. and there have been six dedt transactions, the report will show 120,000 as the department budget). I want to prevent the report from multiplying or counting the department budget for each transaction. Any ideas?

 

[Fornatian]

I'm sure there is an easier answer than this but does think work?

=sum([department budget])\count([department budget])

Answers on a postcard to....

Ian

 

[Fornatian]

I'm sure there is an easier answer than this but does think work?

=sum([department budget])\count([department budget])

Answers on a postcard to....

Ian

 

[Pat Hartman]

The problem is that the report is being created with a recordset that includes a 1-to-many relationship AND you are trying to sum a column from the "1" side of the relationship. If you simply view the query you will readily see the problem. The "1" side data must of necessity be repeated for each occurance of the "many" side data. If it were not for the sum(), you could simply say to hide duplicates for the columns of the "1" side or add a group header/footer. However, even though do this, ALL of the rows will be summed, not just the visible one.

The solution is to break the report into a main report and subreport. Base the main report on a query that only selects data from the "1" side, and base the subreport on a query that selects data from the "many" side.

 

[Adrianna]

I hope this is what you meant by reply via post card. I copied my post below. I was wonderig if I explained it a little more clearly, maybe it would be possible to generate a little more advice. I've tried dividing by the count, but once again, my problem has not been corrected.
Here is the situation: [Department Budget] is actually made up of [Origianl Budget]+[Adjusted Budget]. No big deal there. The Budgets are allocated in a form called Budgets which reports back to a table called budgets. The [Debt]s are entered and reorded through a form and table called Financial Activities. The report that I'm running is generated from a query that calls on both the Budget and Financial Activities tables. The report is grouped using a DEPARTMENT header and footer and a SECTION header. When I enter =([Origianl Budget]+[Adjusted Budget]) in the department footer, I get the single budgeted amout for the Department. When I enter =sum([Origianl Budget]+[Adjusted Budget]) in the report footer, I get a HUGE NUMBER (it is adding the budgets for each transaction). When I divide by count([transactions]), I get a super small number. So, the deal would be figuring out how to call upon the budget numbers without relating it to the debts. I can not remove the relationships and a sub report doesn't avoid the relationship problem. Is there anyway to get around this?
Any help or insight would be appreciated.

 

[Fornatian]

I would follow Pats instructions if I were you. She knows much more than I. Although I am still learning (and aren't we all).

Ian

 

[Adrianna]

Okay....I've got a small sample database in Access 2000 if anyone thinks they have an idea but needs to see it hands on. I've tried to work with the one to many idea, but even if I can produce the number I need in a one to one subform, I will need to manipulate the number to use it in the main form.

 

[Baldrick]

I love resurrecting these old topics from the search archives...

How would one accomplish this with a many to many relationship? I have a tblNewProjects and tblAttendees. They both have a one-to-many relationship to tblAttendeeProjects, creating a many-to-many relationship (this table contains only an ID from each of the previous tables).

I'm struggling to create a report with sub-report, because I don't know how to add the "linking table" to the equation.

Help...

 

[David R]

Sorry to piggyback on, but I'm interested in this too and want to see if you get a response...

 

[Baldrick]

I may have figured it out.

I created a query for each side of the relationship, being sure to include a field from the "linking table" in at least one of them. Then use the queries as datasource for your reports (main/sub). Be sure to select the keys as fields in your report(s). When you insert the subform, Access should intuitively recognize the key to link to.

You may have to play with which you want as the main and which the sub to get the data to report correctly.

Post again if I've confused you.