Solved Creating Running Sum & Balance in a Continuous Form (1 Viewer)

[Weekleyba]

I'm trying to create both a Running Sum and a Balance on a continuous form.
The database is to score projects.
Projects can be either a BEMAR project, a Major project or a Small Clinic project. (3 categories)
The top scored projects are ranked in order.
There is a cost estimate for each project.
I have a continuous forms that for 'All Projects', 'BEMAR Projects', 'Major Projects', and 'Small Clinic Projects'.
I would like to have a fields on the forms that show the running sum and the balance for only those projects that are in that category.

Can anyone lend a hand here?
I've been at it for a few hours now and can't seem to find a solution.

Attached is my sample database.
Open it, select FY 2021 and then select any of the buttons.

My query, 'ScoreSheet_Q', seems work for the running sum only if all the projects are the same FY.

Any help is much appreciated!

 

[plog]

You've given a lot of facts about your database (types, rank order, cost estimates, etc). But you never really tell us how all those things figure into the running sum or balance, or if they do at all. You mention a form where the results should appear, but then talk about a query that sort of works. I really don't know how to help you because you haven't told us what it is the final result should be based on.

What should we focus on to help you? Would building a query achieve what you want? My guess this is ultimately a data issue so building a query should be the aim.

If that's true, the best way to communicate data issues is with data. If what you ultimately need is a query, please provide 2 datasets:

A. Starting data from your table(s). What you posted so far can suffice, as long as it has enough data to cover all cases. If it doesn't, please post better data.

B. Expected results of A. This you have not posted. I would need to see what data you expect to end up with when you feed in the A data.

Again, if you want a query, the way for us to help you is for you to provide 2 sets of actual data--starting and expected results based on the starting data.

 

[Weekleyba]

Sorry about the confusion.
Perhaps this will help.
For each of the 3 forms, BEMAR Projects, Major Projects, and Small Clinic Projects, I would like to add the fields Running Total and Balance as related to the Estimate.
Below is an example of what I am looking to accomplish.

I can do this Excel as shown below.
But I need help accomplishing this in Access in the continuous forms.

Hopefully that more clearly communicates what I need help with.
Weather its done within a query or some other method, it doesn't much matter to me.

Thanks for the help.

 

[CJ_London]

to do a running sum of this nature with databases you need to specify an order to determine next/previous. You do in excel as well, but can manually set the order to effectively be random as your data is.

To specify an order you might use a PK, a date or a timestamp for example. In your example data we can 'fudge it' by sorting on the project title field which happens to be in alphabetical order - which may be OK for you - or it might not, you need to tell us

 

[plog]

That demonstrates it, but it doesn't give me the data details I need.

What table is the form based on? What field of that table are the records ordered by?

 

[Weekleyba]

I’ll get back to you guys later tonight but i did attach the database that shows all the info you’re asking for.
None the less, I’ll lay it out later tonight.
thanks for your willingness to help me out.

 

[plog]

You've got it already in your query, except you shouldn't do it in your query, but you should assign it to the text box of the form you want it to appear in as its Control Source:

=DSum("CostEstimate","Project_T","[ProjectID]<=" & [ProjectID])

Then for Balance you just do math. You subtract the above from whatever the control name is that has the 6000000 value in your image above.

Lastly, but foremost, I don't think you've structured your tables correctly. An outsider looking at your tables (e.g. me) shouldn't have to know about your business to be able to understand the names you've chosen for your fields. [LifeSafetyCode], [OtherCodes&Standards], [BacklogOfM&I] should probably be values in a field, not field names themselves--All of those terms seem to describe some sort of type. You most likely need a new table to house all of that data.

 

[Weekleyba]

I'm still stuck. I placed the DSum function in the textbox's control source and it gives me the following:

I do want it ordered by the Score, aka GrandTotal in the query, but it doesn't work as shown above.
If I order it by the ProjectID, then it does work, as shown below.

How do I get it to work with ordering it by Score/GrandTotal?
I have been ordering it at the Form level as shown below.

 

[plog]

Change the DSum criteria to use the score field instead of the ProjectID:

=DSum("CostEstimate","Project_T","[Score]>=" & [Score])


Now, if projects share the same score, then they will have the same Running Total value. You must have way to unique sort your data for a running sum to work.

 

[arnelgp]

see your running sum db.

 

[Weekleyba]

Thank you Arnelgp! You've given me a lot to look at.
However, I still have the problem of sorting the Score in descending order.
I've tried multiple things.
Any ideas?

 

[Weekleyba]

I should add, it needs to sort the Score while recalculating the Balance so that it is accurate on the form.
I can force the sort of the Score but then the Balance is messed up. Like below.

 

[arnelgp]

all the Form's are based on the query, if you can add the Sort on the Query, it will calculate correctly.

 

[Weekleyba]

The problem I have is the Score (which is the GrandTotal field in the query) will not sort in the query.
I think it may be because it’s an expression, but I’m not sure.
When I choose Descending I’m the drop down of the GrandTotal and then run the query, it ask for the two fields that add together for the GrandTotal.
How do I get around that?

 

[Weekleyba]

I'm pretty it's because I have query fields that are expressions, consisting of other fields in the query, that are also expressions.
Then when a try to place a Sort on one of them, it doesn't know what to do.
I tried to combine all expressions into one field but, it gives an error shown below.
Any other ideas?

 

[arnelgp]

i added 3 new queries that will sort correctly.
do not delete the 3 previous queries i made because
they are also being used in the 3 new queries.

 

[Weekleyba]

Arnelgp...Very nice!
I did not know how to create a custom unique identifier made up of both my GrandTotal and ProjectID. That's brilliant!
Thank you for all you help on this.
I learned a lot.