Beginning Balance

Posted on 2011-10-05
Last Modified: 2013-11-28

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
Question by:pdvsa
    LVL 74

    Accepted Solution

    As you know, I like to do things like this in a report, where this is easy...
    LVL 84

    Assisted Solution

    by:Scott McDaniel (Microsoft Access MVP - EE MVE )
    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.

    Author Closing Comment

    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.

    Author Comment

    I did not completley understand the significance of non normalized data?
    LVL 84
    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.


    Author Comment

    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
    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    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.



    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now