Results 1 to 15 of 15
  1. #1
    neuk is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    36

    Create Running Total in a Query

    I need to calculate the parameter bmg as a running total in a query.

    - I tried Dsum but resulted in a circular reference since I was trying to pull the next calculation using the running total (which uses a reference that hasn’t been calculated yet…)

    - I then created a field in the table that stores the running total of the calculated parameters, which works but I don’t like putting calculated fields in my table

    - I then found a solution using SQL in the query. The SQL works like this

    Code:
    SELECT OrderID, OriginalValue,
    (SELECT Sum(tblOriginalValues.OriginalValue) AS Total
    FROM tblOriginalValues
    WHERE tblOriginalValues.OrderID <= T1.OrderID) AS Total
    FROM tblOriginalValues AS T1
    Because the OrderID value (the AutoNumber field) is in ascending order, the WHERE clause's condition is always True, which forces a new sum for every record (see https://www.techrepublic.com/article...-access-query/) .
    While I was able to get this to work in a sample query, I can’t get this to work in the query I need it to work in. The original SQL in the query of interest
    (before I made any changes)
    is as follows:

    Code:
    SELECTtblRecord.IdRe, tblRecord.ReDateRecord ASdr, tblRecord.FidDe, tblRecord.FidRi, tblRecord.ReDensity AS dn, tblRecord.ReWeight ASwt, tblRecord.ReSize AS sz,tblRecord.ReFeedRate AS fr,tblRecord.RePercentX 
    ASpx, tblRecord.FidVfX, tblVarFeed.VfFeedType ASfx, tblRecord.RePercentY AS py,tblRecord.FidVfY, tblVarFeed_1.VfFeedType ASfy, tblRecord.ReTmp AS tmp, tblRecord.RePh AS ph, tblRecord.ReDo ASdo, 
    tblRecord.ReOrp ASorp, tblRecord.ReNitrite AS nti,tblRecord.ReNitrate AS nta,tblRecord.ReTurbidity AS ntu,tblRecord.ReAlgaeVol AS av,tblRecord.ReNotes AS nt,tblRecord.ReDispEnblA, tblRecord.ReDispEnblB, 
    tblRecord.ReBiomassGainSum ASbga, ([wt]*[dn]/1000)+[bga] AS bm, ([TempVars]![tvBfr]*[bm]) AS cfr, [cfr]*[TempVars]![tvFer] AS bmg,[bm]+[bmg] AS nbm,Format([RePercentX],"0%") & " " &[tblVarFeed.VfFeedType] AS pfx, 
    Format([RePercentY],"0%") & " " &[tblVarFeed_1.VfFeedType] AS pfy, [px]+[py] AS pz, [px]*([dn]/1000)*[fr] AS gx, [py]*([dn]/1000)*[fr] AS gy, [gx]+[gy] AS gz, [gx]/[tblVarFeed.VfUnitGrams]AS ux, [gy]/[tblVarFeed_1.VfUnitGrams]
    ASuy, [ux]+[uy] AS uz, [ux]*[tblVarFeed.VfCost] AScx, [uy]*[tblVarFeed_1.VfCost] AScy, [cx]+[cy] AS cz, [fx] AS dxa, [fy] AS dya, [fx] AS dxb, [fy] AS dyb, [TempVars]![tvA]*[Gx] AS gxa, [TempVars]![tvA]*[Gy] AS gya, 
    [TempVars]![tvB]*[Gx] AS gxb, [TempVars]![tvB]*[Gy] AS gyb, [Gxa]+[Gya] AS ga, [Gxb]+[Gyb] AS gb, [Ga]+[Gb] AS gc, ([Ga]*[TempVars]![tvMg])/[TempVars]![tvP] AS pa, [Gb]*[TempVars]![tvMg]/[TempVars]![tvP] AS pb, [Pa]+[Pb] AS 
    pc, [Pa]/([TempVars]![tvH]*60) AS ra, [Pb]/([TempVars]![tvH]*60) AS rb, [Ra]+[Rb] AS rc, [dn]-[TempVars]![tvSdn] AS [at], [dn]/[TempVars]![tvSdn] AS tr, 1-([dn]/[TempVars]![tvSdn])AS tl
    FROMtblVarFeed INNER JOIN(tblVarFeed AS tblVarFeed_1 INNER JOINtblRecord ON tblVarFeed_1.IdVf = tblRecord.FidVfY) ONtblVarFeed.IdVf = tblRecord.FidVfX;
    Simply put, I need the para
    meter bmg to act as a running total. That translates into the following:

    Code:
    SELECT IdRe, bmg,
    (SELECT Sum(bmg) AS Total
    FROM tblRecord
    WHERE tblRecord.IdRe <+ T1.IdRe) As Total
    FROM tblRecord AS T1
    Next when I put this in the SQL I came up with something like this:

    Code:
    SELECT IdRe, tblRecord.ReDateRecord AS dr, tblRecord.FidDe, tblRecord.FidRi, tblRecord.ReDensity AS dn, tblRecord.ReWeight AS wt, tblRecord.ReSize AS sz, tblRecord.ReFeedRate AS fr, tblRecord.RePercentX 
    AS px, tblRecord.FidVfX, tblVarFeed.VfFeedType AS fx, tblRecord.RePercentY AS py, tblRecord.FidVfY, tblVarFeed_1.VfFeedType AS fy, tblRecord.ReTmp AS tmp, tblRecord.RePh AS ph, tblRecord.ReDo AS do, 
    tblRecord.ReOrp AS orp, tblRecord.ReNitrite AS nti, tblRecord.ReNitrate AS nta, tblRecord.ReTurbidity AS ntu, tblRecord.ReAlgaeVol AS av, tblRecord.ReNotes AS nt, tblRecord.ReDispEnblA, tblRecord.ReDispEnblB, 
    tblRecord.ReBiomassGainSum AS bga, ([wt]*[dn]/1000)+[bga] AS bm, ([TempVars]![tvBfr]*[bm]) AS cfr, [cfr]*[TempVars]![tvFer] AS bmg, [bm]+[bmg] AS nbm, Format([RePercentX],"0%") & " " & [tblVarFeed.VfFeedType] AS pfx, 
    Format([RePercentY],"0%") & " " & [tblVarFeed_1.VfFeedType] AS pfy, [px]+[py] AS pz, [px]*([dn]/1000)*[fr] AS gx, [py]*([dn]/1000)*[fr] AS gy, [gx]+[gy] AS gz, [gx]/[tblVarFeed.VfUnitGrams] AS ux, [gy]/[tblVarFeed_1.VfUnitGrams] 
    AS uy, [ux]+[uy] AS uz, [ux]*[tblVarFeed.VfCost] AS cx, [uy]*[tblVarFeed_1.VfCost] AS cy, [cx]+[cy] AS cz, [fx] AS dxa, [fy] AS dya, [fx] AS dxb, [fy] AS dyb, [TempVars]![tvA]*[Gx] AS gxa, [TempVars]![tvA]*[Gy] AS gya, 
    [TempVars]![tvB]*[Gx] AS gxb, [TempVars]![tvB]*[Gy] AS gyb, [Gxa]+[Gya] AS ga, [Gxb]+[Gyb] AS gb, [Ga]+[Gb] AS gc, ([Ga]*[TempVars]![tvMg])/[TempVars]![tvP] AS pa, [Gb]*[TempVars]![tvMg]/[TempVars]![tvP] AS pb, [Pa]+[Pb] AS 
    pc, [Pa]/([TempVars]![tvH]*60) AS ra, [Pb]/([TempVars]![tvH]*60) AS rb, [Ra]+[Rb] AS rc, [dn]-[TempVars]![tvSdn] AS [at], [dn]/[TempVars]![tvSdn] AS tr, 1-([dn]/[TempVars]![tvSdn]) AS tl 
    
    (SELECT Sum(bmg) AS Total 
    FROM tblRecord 
    WHERE tblRecord.IdRe <+ T1.IdRe) As Total
     
    FROM tblVarFeed INNER JOIN (tblVarFeed AS tblVarFeed_1 INNER JOIN tblRecord AS T1 ON tblVarFeed_1.IdVf = tblRecord.FidVfY) ON tblVarFeed.IdVf = tblRecord.FidVfX;


    This results in an error. Although I tried this in a number of configurations which each results in a different error, the posted SQL statement results in error "The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect"


  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    cant be done easily.(or at all)

    you can easily in a report,(set field property Running sum=true)
    or
    post query to a table, then cycle thru to sum a RunTotal field.

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You are missing a comma just before the subquery,

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    subqueries are faster than domain queries, but if you find your query is still slow, another way to do this which is faster is in your from clause - then join on idRE to your tblRecord

    Code:
    (SELECT T1.IdRe,Sum(T2.bmg) AS Total
    FROM tblRecord T1 INNER JOIN tblRecord T2 ON T2.IdRe<=T1.IdRe
    GROUP BY T1.IdfRe) qTtl
    Note the non standard join highlighted in red

    I may have got the brackets wrong but your from clause would be something like

    Code:
    pc, [Pa]/([TempVars]![tvH]*60) AS ra, [Pb]/([TempVars]![tvH]*60) AS rb, [Ra]+[Rb] AS rc, [dn]-[TempVars]![tvSdn] AS [at], [dn]/[TempVars]![tvSdn] AS tr, 1-([dn]/[TempVars]![tvSdn]) AS tl, 
    
    Total
    
    FROM (tblVarFeed INNER JOIN 
        (tblVarFeed AS tblVarFeed_1 INNER JOIN 
          tblRecord ON tblVarFeed_1.IdVf = tblRecord.FidVfY) ON tblVarFeed.IdVf = tblRecord.FidVfX) INNER JOIN 
          (SELECT T1.IdRe,Sum(T2.bmg) AS Total 
             FROM tblRecord T1 INNER JOIN tblRecord T2 ON T2.IdRe<=T1.IdRe
               GROUP BY T1.IdfRe) qTtl ON tblRecord.IdfRe=qTtl.IdfRe

  5. #5
    neuk is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    36
    I created a separate query that only has the values that I need. after simplifing it I got it to work. the SQL is as follows:

    Code:
    SELECT T1.IdRe, ([ReWeight]*[ReDensity])/1000+Total AS bm, [TempVars]![tvBfr]*bm AS cfr, cfr*[TempVars]![tvFer] as bmg, T1.ReDensity, T1.ReWeight, 
    (SELECT  SUM ([TempVars]![tvBfr] *(([ReWeight]*[ReDensity])/1000)*[TempVars]![tvFer]) AS Total 
    FROM tblRecord
    WHERE tblRecord.IdRe <+ T1.IdRe) AS Total
    FROM tblRecord AS T1;
    However, I ran into another problem..,Is there a way to sum() the caluclated value alias bmg in the subquery? The problem is the sum function only produces a very rough number (1 decimal place) and I need this to be precise. I tried Val(Format()),)) approach but no luck. I think the reason why it's only producing a rough number is because of the chain of values I have to put in the sum function since it appears I can't sum the bmg calculated value (the values put in the sum function is the string of values nested in the bmg parameter I highlighted in green.

    Click image for larger version. 

Name:	Capture.JPG 
Views:	38 
Size:	34.9 KB 
ID:	41786

    Bottom line is I need to create a running total of bmg in the total column, which it does, but I need the total column to be precise. Any ideas?

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    have you tried my suggestion in post #4?

    you can also try modifying your calculation from

    (([ReWeight]*[ReDensity])/1000)*[TempVars]![tvFer])

    to


    [ReWeight]*[ReDensity])*[TempVars]![tvFer]/1000

  7. #7
    neuk is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    36
    Yes, I did play around with it for a couple hours, but I struggled to make it work. if I understand your concept, I could adjust my SQL to look something like this:

    Code:
    SELECT T1.IdRe, ([ReWeight]*[ReDensity])/1000+Total AS bm, [TempVars]![tvBfr]*bm AS cfr, cfr*[TempVars]![tvFer] as bmg, T1.ReDensity, T1.ReWeight, 
    (SELECT  T1.IdRe, SUM (T2.bmg) AS Total 
    FROM tblRecord T1 INNER JOIN tblRecord T2 ON T2.IdRe<=T1.IdRe
    GROUP BY T1.IdfRe) qTtl
    I don’t have my computer present to test this out, but one of the problems seems to be that I can’t sum a calculated value (bmg) so I had to write it out long handed. Am I missing something? I’ll be sure to test this out as soon as I have my computer.

  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    No! but I now see you have a recursive value in calculating your total

    [ReWeight]*[ReDensity])/1000+Total AS bm

  9. #9
    neuk is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    36
    Yes. bmg is "biomass gain" of fish after each feed cycle which is then added to the running "total". The "total" column represents the total gain and is then, as you observed, used to calculate the next sequence, or what the new feed rate "cfr" should be. Since I need to use bmg to calculate the next sequence, this resulted in a circular reference when I was using DSum. So, what I did was I created a table to hold the value of bmg, which works, but is a pain to work with. So, I wanted to see if I could somehow do this in SQL so I could keep all calculated values in the query (instead of a cross between a table).

    It would be nice if I could somehow store the total as a value, instead of a formula, in the query so I can prevent circular reference. Any ideas? I appreciate all the help you've given me already Ajax. I'm fairly comfortable in vba, but I'm pretty new to SQL. One thought I was thinking of is creating a public function to store bmg and then retrieve it somehow, but obviously, the less moving parts the better. It would be nice if I could just hash this out in SQL alone.

    Description Formula syntax global variables
    weight* density/1000+ (old) biomass gain aggregate = biomass wt*dn/1000+bga = bm bm: ([wt]*[dn]/1000)+[bga]
    biomass feed rate * biomass = calculated feed rate bfr*bm = cfr cfr: [TempVars]![tvBfr]*[bm] TempVars bfr = 1%
    calculated feed rate * feed efficiency ratio = (new) biomass gain cfr*fer = bmg bmg: [cfr]*[TempVars]![tvFer] TempVars fer = 71%
    Sum of all biomass gain = new biomass gain aggregate Sum (bmg) = bga or "total"
    Note: the new biomass aggregate is then used as the old biomass for each recursive sequence.
    starting weight (wt) = 60 and density (dn) = 700


  10. #10
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    a function might do it - look at using a static variable rather than dim for your accruing total

    need to study the formula but it might be possible to calculate the accrual each time - also need to know what happens for the first instance - there is no total brought forward.

    is the data in your post #5 realistic? presumably idRe 160 is the first instance?

  11. #11
    neuk is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    36
    Correct. Since nothing is brought forward for the first instance bga is equal to 0. To demonstrate it would look something like this:

    Day 1
    wt*dn/1000+bga = bm 60*700/1000+0 = 42 bm = 42 Note: bga is 0 on day 1
    bfr*bm = cfr .01*42 = .42 cfr = 0.42
    cfr*fer = bmg .42*.71 = .2982 bmg = 0.3
    Sum of bmg Sum(bmg) = .3 bga = 0.3
    Day 2
    wt*dn/1000+bga = bm 60*700/1000 + 0.3= 42.3 bm = 42.3
    bfr*bm = cfr .01*42.423 = .423 cfr = 0.423
    cfr*fer = bmg .423*.71 = .3021 bmg = 0.30214
    Sum of bmg Sum(bmg) = .3 +.3021 bga = 0.60214
    Day 3
    wt*dn/1000+bga = bm 60*700/1000 + 0.6021 = 42.6021 bm = 42.6021
    bfr*bm = cfr .01*42.6021 = .425985 cfr = 0.42602
    cfr*fer = bmg .42602*.71 = 0.3043 bmg = 0.304301020408163
    Sum of bmg Sum(bmg) = .3 +.3021+.3043 bga = 0.90644
    The query would then look something like this:
    bm cfr bmg bga bm
    Day1 42 0.42 0.3 0.3 42.3
    Day2 42.3 0.423 0.302142857 0.602143 42.60214
    Day3 42.60214286 0.426021429 0.30430102 0.906444 42.90644
    Day4 42.90644388 0.429064439 0.306474599 1.212918 43.21292
    Day5 43.21291848 0.432129185 0.308663703 1.521582 43.52158
    Day6 43.52158218 0.435215822 0.310868444 1.832451 43.83245
    Day7 43.83245062 0.438324506 0.313088933 2.14554 44.14554

  12. #12
    neuk is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    36
    Ajax, Sorry I missed your last question. The reason IdRe starts at 160 is because there is a number of other calcs I've been testing out over the course of multiple months. So, every time I delete records and then re add them to test out my initialization sequences in forms and queries and the such, the auto number of course keeps on adding. While that is fine, as long as the values are unique and in ascending order, that does look weird. Given the number where the first record now starts out at, I probably would have asked the same thing, but I wouldn't read into it.

  13. #13
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I find your example a bit confusing - you round the first day but not the others which puts your calculations out.

    Also the whole calculation can be done in one hit rather than the excel way of taking steps.

    By my reckoning, you need two alias tables - one to sum to the previous day and one to to sum to the current day e.g.

    Code:
    SELECT T1.daynum, Sum(Nz([t2].[wt])*Nz([t2].[dn])/1000*0.71*0.01) AS bgp 
    FROM myTable AS T1 LEFT JOIN myTable AS T2 ON T1.daynum > T2.daynum
    GROUP BY T1.daynum
    Code:
    SELECT T1.daynum, Sum(Nz([t2].[wt])*Nz([t2].[dn])/1000*0.71*0.01) AS bga 
    FROM myTable AS T1 LEFT JOIN myTable AS T2 ON T1.daynum >= T2.daynum
    GROUP BY T1.daynum
    the only difference is highlighted in red

    these can then be combined into one query
    Code:
    SELECT myTable.daynum, myTable.wt, myTable.dn, ([wt]*[dn]/1000)+[bgp] AS bm, [bm]*0.01 AS cfr, [cfr]*0.71 AS bmg, BMCF.bga, [bm]+[bga]-[bgp] AS bmcf
    FROM myTable INNER JOIN 
                              ((SELECT T1.daynum, Sum(Nz([t2].[wt])*Nz([t2].[dn])/1000*0.71*0.01) AS bgp
                                 FROM myTable AS T1 LEFT JOIN myTable AS T2 ON T1.daynum > T2.daynum
                                 GROUP BY T1.daynum)  AS BMT 
                          INNER JOIN 
                               (SELECT T1.daynum, Sum(Nz([t2].[wt])*Nz([t2].[dn])/1000*0.71*0.01) AS bga
                                  FROM myTable AS T1 LEFT JOIN myTable AS T2 ON T1.daynum >= T2.daynum
                                  GROUP BY T1.daynum)  AS BMCF 
                          ON BMT.daynum = BMCF.daynum) ON myTable.daynum = BMT.daynum
    which produces this result

    daynum wt dn bm cfr bmg bga bmcf
    1 60 700 42 0.42 0.2982 0.2982 42.2982
    2 60 700 42.2982 0.422982 0.30031722 0.5964 42.5964
    3 60 700 42.5964 0.425964 0.30243444 0.8946 42.8946
    4 60 700 42.8946 0.428946 0.30455166 1.1928 43.1928
    5 60 700 43.1928 0.431928 0.30666888 1.491 43.491
    6 60 700 43.491 0.43491 0.3087861 1.7892 43.7892
    7 60 700 43.7892 0.437892 0.31090332 2.0874 44.0874

  14. #14
    neuk is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    36
    Ajax,

    You're right, I was rounding the first day and didn't even realize it until I manually calculated out each of the 7 days. Your numbers are more accurate. Sorry for the confusion.

    I created a quick database and posted your SQL and it works as you described. It looks like you did the following:

    (1) Created two alias tables - BMT and BMCF.
    (2) Used daynum as the primary foreign key relationship to hold all three tables in balance (myTable, BMT, & BMCF).
    (3) BMCF uses bga as the current cumulaitve gain (T1.daynum > T2.daynum)
    (4) BMT uses bgp as the cumulative gain from the previous day (t1.daynum>= t2.daynum).
    (5) Once BMCF and BMT have been established you were then able to refer to them in the myTable select clause; i.e; wt*dn
    /1000+bgp, bm+bga-bgp, BMCF.bga, BMT.bgp etc.

    Is that right? Your solution is much more elegant than the public module I was attempting to write so I could carry "bgp" forward. Also, it's very nice to have both the previous and current day's gain at my disposal in each record.


    Thank you for the lesson on SQL.

  15. #15
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    points 3 and 4 are the other way round but otherwise you have a correct analysis. happy to help, good luck with your project

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Create a running total
    By PRyan in forum Queries
    Replies: 4
    Last Post: 10-20-2017, 02:07 AM
  2. Running Total Query
    By Plender in forum Queries
    Replies: 3
    Last Post: 06-28-2017, 08:11 PM
  3. How do I create a running total
    By dniezby in forum Access
    Replies: 5
    Last Post: 04-13-2013, 11:42 AM
  4. Running Total Query??
    By kwooten in forum Queries
    Replies: 8
    Last Post: 06-15-2012, 06:10 AM
  5. Using running total in query to create graphs
    By maggioant in forum Queries
    Replies: 4
    Last Post: 10-02-2009, 05:58 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums