Running Sum Error in Query
in [Queries]
|
From: NEWER USER on 27 Jun 2008 22:23 I have read several posts and can not figure out where I have gone wrong - ANY HELP appreciated. I get an #Error in the RunSum Column of my query results Number Pct RunSum 11 42 7 512 300 6.36 #Error 15400 P0H 305A 5.51 #Error 08922 02011A 4.61 #Error Here's my SQL: SELECT tblProduct.Number, Round([Sales]/[SumOfSales],4)*100 AS Pct, DSum("Pct","qryTEST1","[tblProduct]![Number] <= """ & tblProduct!Number & """") AS RunSum FROM qryTEST INNER JOIN tblProduct ON qryTEST.GroupID = tblProduct.GroupID ORDER BY Round([Sales]/[SumOfSales],4)*100 DESC; Number is a Text field in my table. I realize Number is a reserved name, so I referenced the source. Any suggestions???
From: Duane Hookom on 28 Jun 2008 00:13 Try something like this that assumes you have a [Number] field in qryTEST1: SELECT tblProduct.Number, Round([Sales]/[SumOfSales],4)*100 AS Pct, DSum("Pct","qryTEST1", "[Number] <= """ & tblProduct!Number & """") AS RunSum FROM qryTEST INNER JOIN tblProduct ON qryTEST.GroupID = tblProduct.GroupID ORDER BY Round([Sales]/[SumOfSales],4)*100 DESC; -- Duane Hookom Microsoft Access MVP "NEWER USER" wrote: > I have read several posts and can not figure out where I have gone wrong - > ANY HELP appreciated. I get an #Error in the RunSum Column of my query > results > > Number Pct RunSum > 11 42 7 512 300 6.36 #Error > 15400 P0H 305A 5.51 #Error > 08922 02011A 4.61 #Error > > Here's my SQL: > SELECT tblProduct.Number, Round([Sales]/[SumOfSales],4)*100 AS Pct, > DSum("Pct","qryTEST1","[tblProduct]![Number] <= """ & tblProduct!Number & > """") AS RunSum > FROM qryTEST INNER JOIN tblProduct ON qryTEST.GroupID = tblProduct.GroupID > ORDER BY Round([Sales]/[SumOfSales],4)*100 DESC; > > > Number is a Text field in my table. I realize Number is a reserved name, so > I referenced the source. > > Any suggestions??? > > > >
From: NEWER USER on 28 Jun 2008 00:45 NO such luck; I removed the tblProduct as instructed and then tried removing tblProduct on the other(right) side of the <= as well with the same results. "Duane Hookom" wrote: > Try something like this that assumes you have a [Number] field in qryTEST1: > > SELECT tblProduct.Number, Round([Sales]/[SumOfSales],4)*100 AS Pct, > DSum("Pct","qryTEST1", "[Number] <= """ & tblProduct!Number & > """") AS RunSum > FROM qryTEST INNER JOIN tblProduct ON qryTEST.GroupID = tblProduct.GroupID > ORDER BY Round([Sales]/[SumOfSales],4)*100 DESC; > > -- > Duane Hookom > Microsoft Access MVP > > > "NEWER USER" wrote: > > > I have read several posts and can not figure out where I have gone wrong - > > ANY HELP appreciated. I get an #Error in the RunSum Column of my query > > results > > > > Number Pct RunSum > > 11 42 7 512 300 6.36 #Error > > 15400 P0H 305A 5.51 #Error > > 08922 02011A 4.61 #Error > > > > Here's my SQL: > > SELECT tblProduct.Number, Round([Sales]/[SumOfSales],4)*100 AS Pct, > > DSum("Pct","qryTEST1","[tblProduct]![Number] <= """ & tblProduct!Number & > > """") AS RunSum > > FROM qryTEST INNER JOIN tblProduct ON qryTEST.GroupID = tblProduct.GroupID > > ORDER BY Round([Sales]/[SumOfSales],4)*100 DESC; > > > > > > Number is a Text field in my table. I realize Number is a reserved name, so > > I referenced the source. > > > > Any suggestions??? > > > > > > > >
From: Duane Hookom on 28 Jun 2008 09:04 Maybe you should provide some information about your table/query fields and what you are attempting to do. -- Duane Hookom Microsoft Access MVP "NEWER USER" wrote: > NO such luck; I removed the tblProduct as instructed and then tried removing > tblProduct on the other(right) side of the <= as well with the same results. > > "Duane Hookom" wrote: > > > Try something like this that assumes you have a [Number] field in qryTEST1: > > > > SELECT tblProduct.Number, Round([Sales]/[SumOfSales],4)*100 AS Pct, > > DSum("Pct","qryTEST1", "[Number] <= """ & tblProduct!Number & > > """") AS RunSum > > FROM qryTEST INNER JOIN tblProduct ON qryTEST.GroupID = tblProduct.GroupID > > ORDER BY Round([Sales]/[SumOfSales],4)*100 DESC; > > > > -- > > Duane Hookom > > Microsoft Access MVP > > > > > > "NEWER USER" wrote: > > > > > I have read several posts and can not figure out where I have gone wrong - > > > ANY HELP appreciated. I get an #Error in the RunSum Column of my query > > > results > > > > > > Number Pct RunSum > > > 11 42 7 512 300 6.36 #Error > > > 15400 P0H 305A 5.51 #Error > > > 08922 02011A 4.61 #Error > > > > > > Here's my SQL: > > > SELECT tblProduct.Number, Round([Sales]/[SumOfSales],4)*100 AS Pct, > > > DSum("Pct","qryTEST1","[tblProduct]![Number] <= """ & tblProduct!Number & > > > """") AS RunSum > > > FROM qryTEST INNER JOIN tblProduct ON qryTEST.GroupID = tblProduct.GroupID > > > ORDER BY Round([Sales]/[SumOfSales],4)*100 DESC; > > > > > > > > > Number is a Text field in my table. I realize Number is a reserved name, so > > > I referenced the source. > > > > > > Any suggestions??? > > > > > > > > > > > >
From: NEWER USER on 28 Jun 2008 21:14 I have a list of part numbers (all numeric or alpha/numeric mixed) and sales for each part number. I want to be able through a Parameter Entred Value to select those numbers that make up 70% or 85% or... of the total sales. I started by sorting the Sales in descending order and then calculating a percentage of the toal sales - Sales/SumOfSales*100. From here, I was trying to calculate a Running Sum of the Percentage column. My Parameter Query Criteria would then be <=[Enter Percent Desired]. I have been playing for hours trying to make this work. I did ALMOST get it to work except the First Record(greatest Percentage is dropped from the results. Here is what I did. SELECT TEST.Number, TEST.Pct, 100-DSum("Pct","TEST","[Pct] <=" & [Pct]) AS RunSum FROM TEST WHERE (((100-DSum("Pct","TEST","[Pct] <=" & [Pct]))<=[Enter Percent Coverage])) ORDER BY 100-DSum("Pct","TEST","[Pct] <=" & [Pct]); Number Pct RunSum 08922 02011A 13.1668811356702 15.7173192435534 90080 91058A 12.6730719388149 28.8842003792236 15400 PLM A01A 12.5966815658208 41.5572723180386 15208 31U00A 9.96257781132487 54.1539538838593 15208 AA080A 4.71528671398626 64.1165316951842 15208 65F01A 4.59160706247186 68.8318184091705 The FIRST number that is not showing should have ABCD1234 and it should have the RunSum that is appearing in the first row above 08922 02011A - OFF by one row. Maybe there is another way????? Any help appreciated. "Duane Hookom" wrote: > Maybe you should provide some information about your table/query fields and > what you are attempting to do. > -- > Duane Hookom > Microsoft Access MVP > > > "NEWER USER" wrote: > > > NO such luck; I removed the tblProduct as instructed and then tried removing > > tblProduct on the other(right) side of the <= as well with the same results. > > > > "Duane Hookom" wrote: > > > > > Try something like this that assumes you have a [Number] field in qryTEST1: > > > > > > SELECT tblProduct.Number, Round([Sales]/[SumOfSales],4)*100 AS Pct, > > > DSum("Pct","qryTEST1", "[Number] <= """ & tblProduct!Number & > > > """") AS RunSum > > > FROM qryTEST INNER JOIN tblProduct ON qryTEST.GroupID = tblProduct.GroupID > > > ORDER BY Round([Sales]/[SumOfSales],4)*100 DESC; > > > > > > -- > > > Duane Hookom > > > Microsoft Access MVP > > > > > > > > > "NEWER USER" wrote: > > > > > > > I have read several posts and can not figure out where I have gone wrong - > > > > ANY HELP appreciated. I get an #Error in the RunSum Column of my query > > > > results > > > > > > > > Number Pct RunSum > > > > 11 42 7 512 300 6.36 #Error > > > > 15400 P0H 305A 5.51 #Error > > > > 08922 02011A 4.61 #Error > > > > > > > > Here's my SQL: > > > > SELECT tblProduct.Number, Round([Sales]/[SumOfSales],4)*100 AS Pct, > > > > DSum("Pct","qryTEST1","[tblProduct]![Number] <= """ & tblProduct!Number & > > > > """") AS RunSum > > > > FROM qryTEST INNER JOIN tblProduct ON qryTEST.GroupID = tblProduct.GroupID > > > > ORDER BY Round([Sales]/[SumOfSales],4)*100 DESC; > > > > > > > > > > > > Number is a Text field in my table. I realize Number is a reserved name, so > > > > I referenced the source. > > > > > > > > Any suggestions??? > > > > > > > > > > > > > > > >
|
Next
|
Last
Pages: 1 2 Prev: Need to find latest date in a query Next: Cross Tab Query with DateSerial |