[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 396
  • Last Modified:

Beginning Balance

Experts,

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
0
pdvsa
Asked:
pdvsa
  • 3
  • 2
  • 2
2 Solutions
 
Jeffrey CoachmanCommented:
As you know, I like to do things like this in a report, where this is easy...
0
 
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.
0
 
pdvsaAuthor 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.
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
pdvsaAuthor Commented:
I did not completley understand the significance of non normalized data?
0
 
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.

0
 
pdvsaAuthor 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
0
 
Jeffrey CoachmanCommented:
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.

;-)

Jeff
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now