Running Sum Error in Query

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???

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???

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???

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???

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???

Does the query work as expected without the parameter?
--
Duane Hookom
Microsoft Access MVP


"NEWER USER" wrote:

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???

It works with or without Parameter Query. However, I did make a change in
the RunSum expression. I replaced [Number] where [Pct] was being used and
added quotes for the Number (Text field). The query runs and the missing
number appears along with other incorrect numbers and the Run Sum
calculations are not right. I may end up exporting a table to Excel and
performing the calculation very easily, and then linking the table back to
the database and building a filter query to display the records. Not a first
choice by any means.

"Duane Hookom" wrote:

Does the query work as expected without the parameter?
--
Duane Hookom
Microsoft Access MVP


"NEWER USER" wrote:

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???

I would have kept the [Sales] value in the query so you could sort by it
rather than the calculated Pct. Also, you could use the Sales in the DSum().
You need to be carefull with possible ties.

--
Duane Hookom
Microsoft Access MVP


"NEWER USER" wrote:

It works with or without Parameter Query. However, I did make a change in
the RunSum expression. I replaced [Number] where [Pct] was being used and
added quotes for the Number (Text field). The query runs and the missing
number appears along with other incorrect numbers and the Run Sum
calculations are not right. I may end up exporting a table to Excel and
performing the calculation very easily, and then linking the table back to
the database and building a filter query to display the records. Not a first
choice by any means.

"Duane Hookom" wrote:

Does the query work as expected without the parameter?
--
Duane Hookom
Microsoft Access MVP


"NEWER USER" wrote:

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???