Sorry, it still didn't work. Attached is the snapshot for your review. Oh this is so frustrating.
-
-
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.Comment
-
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.Comment
-
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 dComment
-
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.Comment
-
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?Comment
-
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.Comment
-
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.Comment
-
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.Comment
-
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" .
Comment
-
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 rptBalanceDetai lUPA.
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.Comment
-
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.
Comment
-
OK, I checked out the problem with FTABudget in srptBalanceDeta ilUPA (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,,,you r main report would give you the same info. Do no have the time to look at your query today....maybe tomorrow.Comment
-
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.Comment
-
Attached, please find a revised extract of your mdb and a word document with my comments. Hope this helps.Comment
Comment