DSum Ineffective

[Intui_Sol]

Please help. I have not been to get DSUM to work with the following
query:

SELECT Totals.[Sold-to], Totals.[SumOfBilling Item Net Val],
Totals.AcctRep,
(Select Count (*) from TotalsCount Where [SumofBilling Item Net Val] >
[Totals].[SumofBilling Item Net Val]+1+1 AS Ranking,
FormatPercent(([SumOfBilling Item Net Val]/(Select Sum([SumofBilling
Item Net Val]) from TotalsCount),1) AS Percentage
FROM Totals
ORDER BY Totals.[SumOfBilling Item Net Val] DESC;

Even when i take out the formatpercent function, i cannot get a
running sum/total.

 

[Michel Walsh]

You have to sum the rows 'preceding' the actual one:


SELECT ...,
(SELECT SUM([SumOfBilling Item Net Val]) FROM Totals As x WHERE
x.[SumofBilling Item Net Val] >
[Totals].[SumofBilling Item Net Val] ) / (SELECT SUM([SumofBilling Item
Net Val]) from TotalsCount ) AS percentage
FROM ...


assuming the tables you really want are Totals and TotalsCount.



Vanderghast, Access MVP

 

[Intui_Sol]

You have to sum the rows 'preceding' the actual one:

SELECT ...,
(SELECT SUM([SumOfBilling Item Net Val]) FROM Totals As x WHERE
x.[SumofBilling Item Net Val] >
[Totals].[SumofBilling Item Net Val] ) / (SELECT SUM([SumofBilling Item
Net Val]) from TotalsCount ) AS percentage
FROM ...

assuming the tables you really want are Totals and TotalsCount.

Vanderghast, Access MVP

Please help. I have not been to getDSUMto work with the following
query:

SELECT Totals.[Sold-to], Totals.[SumOfBilling Item Net Val],
Totals.AcctRep,
(Select Count (*) from TotalsCount Where [SumofBilling Item Net Val] >
[Totals].[SumofBilling Item Net Val]+1+1 AS Ranking,
FormatPercent(([SumOfBilling Item Net Val]/(Select Sum([SumofBilling
Item Net Val]) from TotalsCount),1) AS Percentage
FROM Totals
ORDER BY Totals.[SumOfBilling Item Net Val] DESC;

Even when i take out the formatpercent function, i cannot get a
running sum/total.

Thanks! I was getting hung up with the DSUM function where it is much
easier to use the Select statement.

Its messy but here's the end result for my running sum and running
cumulative percent:

SELECT Totals.[Sold-to], Totals.[SumOfBilling Item Net Val],
Totals.AcctRep, (SELECT SUM([SumOfBilling Item Net Val]) FROM Totals
As x WHERE
x.[SumofBilling Item Net Val] >= [Totals].[SumofBilling Item Net
Val] AS Ranking, (Select Count (*) from TotalsCount Where
[SumofBilling Item Net Val] >
[Totals].[SumofBilling Item Net Val]+1+1 AS Ranking1,
FormatPercent(([SumOfBilling Item Net Val]/(Select Sum([SumofBilling
Item Net Val]) from TotalsCount),1) AS Percentage1,
FormatPercent(([Ranking]/(Select ([SumOfSumOfBilling Item Net Val])
from Totalsum),1) AS Percentage100
FROM Totals
ORDER BY Totals.[SumOfBilling Item Net Val] DESC;