MS Access - How to calculate a total using a couple different calculated fields from a previous record?

I have a data set where I need to calculate a running total based on calculations in the previous record.  This works easily in excel, but I am trying to move this into a database with no success.

I have a pond that has two sources of water (A & B) that feed into it.  The mixed water is removed from the pond and tracked as total water removed.  Each day, I need to calculate the amount of A and B water that is in the pond by adding the newly added A and B water (measured separately) and the removed A & B water (measured as a combined total) based on the mix from the previous day.  My data's unique id is a date - there is a row of data for every day.

Day 1 =
     [measured_A] + [measured_A_input] - ([pond_output] * ([starting_A] / ([starting_A] + [starting_B]))) = [A_qty]
     [measured_B] + [measured_B_input] - ([pond_output] * ([starting_B] / ([starting_A] + [starting_B]))) = [B_qty]

Day 2 =
     [A_qty] + [measured_A_input] - ([pond_output] * ([A_qty] / ([A_qty] + [B_qty]))) = [A_qty]
     [B_qty] + [measured_B_input] - ([pond_output] * ([B_qty] / ([A_qty] + [B_qty]))) = [B_qty]

Day 3 - same as Day 2 using day 2 data.  etc...

I am trying not to use any Dsum type formulas as this causes the queries to run super slow.  Any assistance is greatly appreciated.

data sample
LVL 1
sj_rodriguezAsked:
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:
It will be difficult to do what you are asking because the dataset you have in Excel does not appear to be "normalized"
For example, it is not clear what the specific field names are for each column.
It is also not clear how the names you have in your text match with the screenshot you posted.
In other words, ...this is not something you can easily insert into Access.

So it may be clearer if you post a graphical example showing what you have *And* the exact results you need.

Ultimately, to do this in Access the simplest way may be with a report.
Where getting a running sum is simply a matter of setting the running sum property of a control.

JeffCoachman
0
Dale FyeCommented:
I have to agree with Jeff's assessment of the data you provided.  For the first point, the sum of Out-of-Pond_A and Out-of-Pond_B does not total to the Out-of-Pond value for the three days you have it.  So that part of your Excel spreadsheet is broken from the get go.

The easiest way to get data from a previous day and pull it into a recordset is to join the table to itself on the previous day.  It looks like the only thing you need to get from the previous day are the Contents_A and Contents_B values, so that you can compute

SELECT Today.*, Yesterday.Contents_A, Yesterday_Contents_B
FROM yourTable as Today
LEFT JOIN yourTable as Yesterday
ON Today.DateField = Yesterday.DateField + 1

Once you have that query, you play with your equations to get the right combinations.  However, this requires that you have readings in your table for every day.  If you skip a day, there will be no "previous day".  This query may not be updateable.
0
sj_rodriguezAuthor Commented:
I had an error in my out-of-pond-A and out-of-pond-b excel calculation (fixed in the attached).  They should add up to the total out-of-pond number.

I will test the sample that Dale suggested.

There will always be a reading for each day (regulatory requirement in this case) otherwise, I would add a counter field to make sure they stay sequential.
Book1.xlsx
0
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.

sj_rodriguezAuthor Commented:
Using the select statement recommended by Dale, I am closer...  I am now getting a calculated out-of-pond-A (and B), but it is based on the Yesterday.pond-contents-A (and B) without removing the Yesterday.out-of-pond values.  Is there a way to have a continuous sum the out-of-pond-A and B values so that it can be subtracted from the Yesterday.pond-contents field each day?
0
Dale FyeCommented:
it would be helpful if we could see what you have so far, even better if you could provide some sample data.

Please confirm that this is not a homework assignment.
0
sj_rodriguezAuthor Commented:
This is not a homework assignment.  We have a client that we have been doing this calculation in excel for several years and want to move it to a database.  

The blue values in the attached file are the data entry points.  We have two types of water entering a pond, process and fresh water.  We have metered data for each water type into the pond and the total water out of the pond on a daily basis.  What I need to calculate is what portion of the water out is process and what portion is fresh using the pond content totals from the previous day.  The pond content totals need to be running totals for each water type taking into account the daily additions and subtractions from the pond.

Based on suggestions yesterday, I created this query. The problem is that the previous day pond contents from the source query isn't taking into account any of the daily water removals, so the ratio is off.  

SELECT Today.pond_pw_add, Today.pond_fw_add, Today.pond_pw, Today.pond_fw, Today.out_of_pond, Today.pond_pw - (Today.out_of_pond * (Yesterday.pond_pw/(Yesterday.pond_pw_add + Yesterday.pond_fw))) AS pw_total, Today.pond_fw - (Today.out_of_pond * (Yesterday.pond_fw/(Yesterday.pond_pw_add + Yesterday.pond_fw))) AS fw_total
FROM qry_calc_flows AS Today
LEFT JOIN qry_calc_flows AS Yesterday
ON Today.meter_date = Yesterday.meter_date + 1;

I think I need to have my query reference itself but am not sure how or if I can do that.
Data-Set-with-needed-calcs.xlsx
0
Jeffrey CoachmanMIS LiasonCommented:
Yes, situations like his are tricky
Note that in your spreadsheet:
In the first row, pond_pw and pond_fw are "calculated",...but in the subsequent rows they are static/stored values.
This cannot be done in Access

I found out the hard way that, (in some situations), getting "Previous" record data is not as simple as you might expect.
Because, (among other things), you need to keep the dataset sorted in a certain way.

I have posted a scaled down sample of how I had to do this in one case.
(Get the previous key value, ...then use the previous key value to get the previous weight)

Now, ...I kid Dale about his solutions always being "more elegant" than mine.
;-)
So if he has a simpler approach in this case, I would benefit as well.
(as because of the complexity, this query comparatively slow)

In my sample I was calculating BMI (Body Mass Index) for employees.
And in the query/form, I wanted to show them their current weight AND their previous weight
1. To let them compare their weights
2. to allow me to calculate the difference between the two.

Take from it what you like, ...but it worked for me.


Jeff
Database80.mdb
0
Dale FyeCommented:
Ok, retrieving the values from the previous record is easy.  The problem is that you cannot do this recursively within a query, so you cannot simply start with a StartDate, Pond_PW, and Pond_FW and then record the PW_Add, FW_Add, and Out_of_Pond and get Access to compute the Pond_PW, Pond_FW, Pond_Total, PW_Out, and FW_Out values recursively.

You can get it to update those values for today, after you enter the PW_Add, FW_Add and Out_of_Pond values for the day, but I would normally do that by calling a procedure in the AfterUpdate event of those three controls on a form.  That procedure would check to ensure that all three values have been entered, and then compute the other values by retrieving the vales of Pond_PW and Pond_FW from the previous day and then computing these new values.

If you wanted to display this in a datasheet or a continuous form, and be able to enter and change values anywhere within the PW_Add, FW_Add, and Out_of_Pond fields, then the procedure would need to loop through the forms recordset to update each record, one row at a time, just like Excel does when it does a recalc operation.

Take a look at the attached database.  I've locked all of the numeric fields except PW_Add, FW_Add, and Out_of_Pond.  Notice how if you change a value towards the top of the form, it will recalc the remainder of the form.
Pond.accdb
1

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
Dale FyeCommented:
BTW, that was a fun little exercise.
0
sj_rodriguezAuthor Commented:
You have both confirmed what I was starting to think...  

My database is used by a c# web application.  I try to build the queries in the database first then update the application.  In this case, it looks like I will need to do this portion in my program with a loop (like your example Dale).  

I appreciate your help and incite.  I will try to find another "fun" exercise for you Dale. :)
0
Jeffrey CoachmanMIS LiasonCommented:
OK

Enjoy the weekend
0
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.