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 said:
Maybe you should provide some information about your table/query fields and
what you are attempting to do.
--
Duane Hookom
Microsoft Access MVP
:
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.
:
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
:
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???