How to select percentage of records based on their values

[hugoj]

Hi

I have as a start made a Query with the following field: Running total: DSum("Ordersumma";"Order";"[Ordersumma]<=" & [Order sum])

That line produces the total sum at the first record and then continues down to zero.


The result is fine but in the end I want to select e.g. the order values that make up 80% of the total value. So how do I continue to select only those records.

In the query's property sheet I can select 10% of the total records but that is not valid in this case since I want to base the percentage upon the values.

Best regards

Hugo

[June7]

So the cutoff would be where the DSum >= 20% of the total? The total is [Order sum]?

[hugoj]

The cutoff is where the DSum >=20% but the total is the Running total. "Ordersumma" is the field in the table. [Order sum] is a renamed field which is the "copy" of "Ordersumma". So this field I use for comparison against the field in the table.

[June7]

Need to know the grand total to calculate the % target.

Don't know why you would need to copy Ordersumma field.

If you want to provide raw data for analaysis, attach file (Access or Excel). Follow instructions at bottom of my post.

[hugoj]

I have attached one table with orders and the query I use. I searched online how to use the DSum function with this purpose.
And according to Microsoft online help I should use it like that. I tried to skip the field I created and compare Ordersumma to Ordersumma but Access complained when I did that. So this function works fine when showing the values. But dont reach my ultimate goal of showing only the orders that make up 80% of the total.

Hugo

[June7]

Consider:

SELECT Order_2.Ordersumma AS Ordervärde, Sum(Order_2.Ordersumma) AS SumOfOrdersumma, DSum("Ordersumma","Order_2","[Ordersumma]<=" & [Ordervärde]) AS [Running total], qryEnbart_total.Total, DSum("Ordersumma","Order_2","[Ordersumma]<=" & [Ordervärde])/[Total]*100 AS Pct
FROM Order_2, (SELECT Sum(Ordersumma) AS Total FROM Order_2) AS qryEnbart_total
GROUP BY Order_2.Ordersumma, qryEnbart_total.Total
ORDER BY Order_2.Ordersumma DESC;

Can delete the qryEnbart_total object because the SQL is nested into the above.

However, when I apply filter criteria to the Pct field I get an input popup.

So build another query that uses the above as source.

[hugoj]

Thanks! Works perfect!