Report Footer skips record

[MNNovice]

I am trying to generate a report to show remaining balance on a grant. It does not work when there are more than one project in a grant. rptGrantBalance is based on qryExpDetail. Fields include ProjectNo / Budget / Expense / Balance

If there is only one grant I get correct result but if there is more than one project, the budget only pulls budget for the first project although the Expense adds expenes for all projects. GroupHeader includes, GrantNo and ProjectNo.

How can I fix this problem?

Let me know if further information is needed. Thanks.

[puppydogbuddy]

You are probably grouping by project, then grant. You need to group by grant, then by project since there can be more than one project covered by one grant. Place your report in design view, click the sorting/grouping icon on tool bar and fix accordingly.

[MNNovice]

No, I checked and I have it sorted correctly. When I looked at the query which is the source of this report - everything looks okay. Why it will do this then? This is such a mystery. I have got to fix this problem today and I don't know how. This is also frustrating.

Thanks for your suggestion though.

[puppydogbuddy]

If you used the sorting and grouping tool on the icon bar in design view, it over-rides any sorting or grouping done in the query, so look and post back what is showing in the sorting/grouping tool.

[MNNovice]

The sorting/grouping is shown as this:

GrantNo - Ascending (yes/yes for header & footer)
ProjectNo - Ascending (yes/yes for header & footer)

The report is displayed as follows:

Grant No. 90001
ProjectNo. 65601
Budget: 100,000 Total Expense 40,000 Balance 60,000

ProjectNo. 61002
Budget: 250,000 Total Expenses 100,000 Balance 150,000

GrantNo.90001
Budget 250,000 Total Expenses 140,000 Balance 110,000

It's always taking the budget of the last project listed and not sum of the budget which, in this sample, should be 350,000 and the correct balance for the project should be 210,000. Surprisingly it adds the expenses correctly. I am so frustrated.

Thanks.

[puppydogbuddy]

ok, look at the formula shown in the grouping control for the budget amount. In order for that formula to work, it has to be preceded by an equal sign or it won't work correctly. it should look something like this:
= Sum([BudgetAmount])

Replace BudgetAmount with the actual name of the field as used in your application.

[MNNovice]

Thanks for your help. It's appreciated.

Let me explain a little.

My query has these fields:

Budget
Change
TotalBudget = Budget + Change

My Report has this for Budget = TotalBudget

If I do Sum([TotalBudget]), I get a figure that's just way too large. Now I get only the TotalBudget for the last project within a grant.

And yes, I have the equal signs in all the correct places.

[puppydogbuddy]

let's go over a few basics:
1. all fields in your query should appear in your report, unless you unchecked the show box in the query grid, Is that what happened with [change]?
2. any amounts appearing in the detail section of your report are detail amounts and should not be preceded with an equal sign or the word sum
3. any amounts appearing in a group header or footer should be preceded with an equal sign and the word Sum
4. Given the above, if you are presenting budget detail (salaries, office supplies, etc. you should have a detail line as described above. If not, you should not have any amounts in your detail section.

[MNNovice]

I don't understand #4. Lines 1 -3 agree on what I have. Do you prefer I post my sample DB and may be you can look at this one report I am struggling with. Let me know.

thanks.

[puppydogbuddy]

if your Access version is not V 2000 or compatible, I won't be able open your file. Otherwise, ok to send.

what happened with the field named "Change"?
if you have no detail section in your report, then in your group header or footer, you should try totalBudget>> = nz(Sum([Budget],0) + nz(Sum([Change],0)

[MNNovice]

Mine is MSAccess 2003. will this work?

[puppydogbuddy]

It might if V 2000 format was selected as the database format. If it wasn't, then
try to send a snapshot of your report in design view.

[puppydogbuddy]

Based on the title of your problem, is it just the report footer section that is missing? If the report footer section is missing and you using a page footer, then all you need to do is go to view on the command menu, and select report header/footer and Access will add a report header/footer section to the report. Then all you have to do is drag the controls from the page footer (not needed), leaving the page footer empty.

[MNNovice]

Is this what you wanted? Let me know. Many thanks for your time.

[puppydogbuddy]

Yes, that is what I wanted. I think I know what the problem is. You are trying to come up with the Budget subtotals w/o any detail amounts in the detail section. Expenses are no problem because you have a detail amount in the detail section, and quite simply, the total is the sum of the details. If that is the case, the fix is this: Put a tiny, hidden (set visible property to no) textbox control for the detail budget amount , and set your subtotal to be the sum of the details just like you did for expenses. The only difference is that the detail amount for the budget is not visible, while expense detail is visible.

[MNNovice]

Sorry, it still didn't work. Attached is the snapshot for your review. Oh this is so frustrating.

[puppydogbuddy]

When you say it is not working, can you tell me what the amount should be, or better yet, make the textbox for the budget detail visible for testing purposes and place the textbox in blank space between the category and expense detail. That way, we can see budget detail making up the subtotal. And remember what we discussed before.
1. The textbox control in the detail section should not have an equal sign or the word "sum" it should just refererence the underlying field from your fieldlist.
2. The subtotal textbox should have an equal sign and the word sum preceding the underlying field name.

[MNNovice]

The correct total for
Grant No. MN030086 $2,600,000
Grant No. MN030106 $3,000,000 ($500,000 + 2,500,000)

What it's actually doing is showing $2,600,000 for all three transactions and adding them to get $7,800,000 as total budget which is not correct.

1. I don't have any equal sign - just the field names [Budget] & [Change]
2. I do have equal sign - Sum([Budget]) + Sum([Change])

Hope I answered your questions. Thanks.

[puppydogbuddy]

You did not move the textboxes over and make them visible so you can see the detail making up the total? You don't have separate textboxes for Budget and Change in the detail? you need to. Then the subtotal calc becomes:
= Sum([Budget]+[Change]). hopefully with textboxes visible can identify whch one is the nissng jnm d

[MNNovice]

Good morning.

As I understand what's it doing is adding the budget amount as many times it's occuring in the detail section. For example, for MN030086, $2,600,000 occurs 3 times. So it's calculating 2,600,000 x 3 = 7,800,000. Same with the next grant MN030106.

What I don't understand is that Grant MN030106 has two projects, Project 62651 the [TotalFTA Amount] = 500,000 and for Project 65651 it is 2,500,000
Now if I have [TotalFTA Amount] in Grant Footer, why don't I get 500,000 + 2,500,000? Why do I get only one project amount?

Thanks.

[puppydogbuddy]

You are getting a painfull lesson on what happens when your database design is flawed.

Your business model is such that one grant can be used for many projects and one project can be funded from many grants. Your business model thus contains a many to many relationship between grants and projects for which Access, like other relational databases, can not enforce accurate results, and must rely on the database designer/programmer to find a work-around.

Access is designed to deal with (and can enforce accurate results from) one to many relationships between entities in the business model. You, therefore, need to convert the many to many relationship in your business model into one to many relationships that Access is designed to deal with.

To do this you must place a so called junction table between the entities in the many relationship. This junction table will have its own autonumber primary key field and two foreign key fields.... the prmary key of each of the other two tables (the grant and project tables). In your case the junction table will also contain the budget amount).

Are you with me?

[MNNovice]

Yes. I have a tblGrant and tblFund and then I have a tblGrantFunds that contain budget data as well.

Anyway I solved the problem in a different way. I created a main report for the budget info and a sub report for the expense part. Then, I added fields at the grant footer to show budget - expense = balance.

Thanks.

[puppydogbuddy]

I am glad you found a solution that you can live with. I still feel there is a flaw in your DB design that will continue to require you to find a work-around.

Without seeing your relationship window, this is all conjecture. I think you should have had an account code table that had a field for transaction type. That could be use to join revenue and expense transactions that affect the same account code. If you are interested, you should look at the sample Ledger database that comes with Access. To get to the sample,
1.click the Access icon
2. Instead of selecting "open an existing database", select "Access database wizards, pages and projects".
3. Select "Ledger" sample database loaded with data.
4. Review db structure in relationship window, paying particular attention as to how account codes are handled.

[MNNovice]

I am not an expert in Access by any measure. But I am trying to learn as I go which is not a very good idea. And I do struggle from time to time. What you say here makes sense and I am somewhat nervous about it. But without direct help from an "Expert", I continue to hit the wall in darkness and try to find my way out.

If you are willing, I could place the zipped version of my DB and may be you can help me design it better. Giving me direction to a better relationship (this part is hard for me to fully understand) structure etc.

Let me know if you are willing. Meanwhile I will read up on the material you suggested.

Many thanks.

[puppydogbuddy]

If you can convert your file to version 2000 compatible format so that I can read it, go ahead and attach the file here. I don't know how soon I can get to it, but I will look at it ASAP.

In the meantime, take a look at these links. the first one has data models from various access applications. Take a look at a couple of them and see how they were done.......for example, look at the one for hotel and room booking. The second link is for a completely free 23 part tutorial on Access 2003. Take a look at the 4 lessons being with the lesson that says "Relationships".

http://www.databasedev.co.uk/data_models.html

http://www.functionx.com/access2003/index.htm

[MNNovice]

Thank you so much for your offer of help. Attached is a zipped file for my DB. Feel free to tweak it as you wish. The report I am having difficulty with is called rptBalanceDetailUPA.

UPA is project number 63740 which has several classes. The report fails to show the aggreate budget for FTA Budget field. It should be $85,900,000 but it displays $9,506,225. Also, qryExpDetailUPA shows total budget for $351,877 which is allocated to only one class of project. The correct total budget s/b $107,437,319. For whatever reasons, it's pulling the budget for Class PT10 for $351,877 and not the whole project 63740.

If you can help me that will be much appreciated.

I will definitely read up on those links. Thanks for your kind help.

[puppydogbuddy]

It is going to be a few days before I can look at your file (I generally don't work on the weekend).

In the meantime, here is a download link for 28 Access DB Templates that you can download. I suggest you take a look at the Accounting Ledger template and the Expense Reportng template.

http://databases.about.com/od/access..._Templates.htm

[puppydogbuddy]

OK, I checked out the problem with FTABudget in srptBalanceDetailUPA (the subreport). This amount is in the Project footer section and the expression bound to this control should be: = Sum([FTABudget]), not FTABudget. Remember, any time you have a header or footer, you are dealing with the total of the detail line amount, and therefore, the field name must be preceded by the equal sign and the word "Sum" as shown above. By the way, you did not need to do a subreport,,,your main report would give you the same info. Do no have the time to look at your query today....maybe tomorrow.

[MNNovice]

I tried the expression: Sum([FTABudget]) it does not work. What I get is $7,800,000 instead of $2,600,000 for the first Grant MN030086.

How could I get the same info without adding a sub report? Can you elaborate on this issue?

Take your time. I appreciate any help I can get. Thanks.

[puppydogbuddy]

Attached, please find a revised extract of your mdb and a word document with my comments. Hope this helps.

[puppydogbuddy]

PS: Forgot to attach this link to MS Access Demos on various topics, some of which relate to what you are doing.

http://office.microsoft.com/en-gb/ac...739911033.aspx