Beginning Balance


Is it possible to do what I want in the picture?  
I need to use the [BegBal] as the [FacilityAggregate]
I dont know if Access can do this within a qry.

Please see pic.
Any other questions please let me know.

The name of the qry is qryLCIssuedFacExpireByMonth

thank you BegBaltestBegBal.accdb
pdvsaProject financeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
As you know, I like to do things like this in a report, where this is easy...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Access has no knowledge of "the immediately preceding line in my query", so there's really no simple way to do this. As boag suggests, the best way to manage this is in a Report.

You could build a temporary table to hold this data, and fill that table row-by-row. Depending on how much data you have, this can be a simple and quick tool to use when you need non-normalized data to show to the user.
pdvsaProject financeAuthor Commented:
Oh so the trick is to use a report.  I asked basically the same question a few months back and i think LSM answered but i dont believe a report was mentioned.  I asked this question again in hopes of getting an answer just like this... Thank you very much.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

pdvsaProject financeAuthor Commented:
I did not completley understand the significance of non normalized data?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Your data is not presented in a non-normalized fashion in your screenshots, and Access has a difficult time working with data like that. Your data is far more suited to a spreadsheet (i.e. "flat file", or non-normalized) sort of storage system, where you CAN define a "preceding line". Database are used to store financial data all the time, but in many cases, that data is extracted to a spreadsheet-type of a UI in order for the users to make the best use of the data.

As long as you ONLY need to view this data, then of course you'd use a report, as suggested by boag. Access can easily "flatten" this data, and even present you with running totals/sums etc, using the builtin reporting engine. Only when you have the requirement for users to EDIT this data does the trouble come into play.

pdvsaProject financeAuthor Commented:
I see.  

LSM, I have a question...very short.  I am here at work now and ran the query qryLCIssuedFacExpireByMonth

I get this input box for FacilityBalance and not sure why.
The formula is here:
FacilityBalance: Format([FacilityAggregate]-[SetToExpireThisMonth],"Currency")

The only thing I can think of is because the FAcilityAggregate and SsetToExpireThisMonth are also manual type of formulas.  But the odd thing is that I just hit enter for the msgboxes and I get my results and I dont believe whatever I input in the box makes a difference.

I dont know if it is because the "Total" row (the Group By option) should be something else such as "Expression" but I tried and still get the msgbox.  

Do you know why I would get the msgbox?  If not a quick answer then dont mind about it please.  I know there are better things to do...:)

thank you
Jeffrey CoachmanMIS LiasonCommented:
Just to be fair, I have seen queries to do what you are asking, but IMHO, I would still rather just use a Report.

(I guess you could Google: "Query Declining Balance"
...or "Query Running Sum")

If only for the fact that these queries tend to get more complex as your recordset complexity increases.

...Especially if this is just for display purposes.

But again, for whatever reason, some people just want/need to use a query.
So I made my initial post just in case...

I see that LSM actually contributed more to this thread than I did, ..I have no issue with you changing this to give him all the points.


It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.