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
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/) .Code:SELECT OrderID, OriginalValue, (SELECT Sum(tblOriginalValues.OriginalValue) AS Total FROM tblOriginalValues WHERE tblOriginalValues.OrderID <= T1.OrderID) AS Total FROM tblOriginalValues AS T1
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:
Simply put, I need the parameter bmg to act as a running total. That translates into the following: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;
Next when I put this in the SQL I came up with something like this:Code:SELECT IdRe, bmg, (SELECT Sum(bmg) AS Total FROM tblRecord WHERE tblRecord.IdRe <+ T1.IdRe) As Total FROM tblRecord AS T1
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"