How to sum all the records, excluding the top N ones?

P

Paulo

I have no difficulty creating a query that sums the top 20 records. What I
need, however, is how to sum the rest, that is, all the other records,
excluding the top 20 ones.

Thanks in advance,

Paulo
 
J

John Spencer

Care to post your existing query that sums the TOP 20?

The sum of the rest is basically the sum of ALL minus the Sum of the Top 20.



John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
K

KARL DEWEY

Try this method. Save your top 20 query.

Create a totals query with your table left joined to the top 20 query on
primary key. Use criteria where top 20 query primary key field is null.

Union the two queries.
 
K

KARL DEWEY

I missed the part about summing the top 20 so change what I said about the
'primary key' to use the GROUP BY fields of the top 20.
 
P

Paulo

It's a rather long SQL string for the Top 20 query:

SELECT TOP 20 Orders.Supplier, Sum(Orders.[Order Qty]) AS [SumOfOrder Qty],
Sum(Orders.[Received Qty]) AS [SumOfReceived Qty],
Sum(IIf([Orders]![Channel]="WHOLESALE",IIf([Orders]![Status]="REC",[Orders]![Received
Qty]*[WSale Price]*(1-[Seasons]![VAT Rate]),[Orders]![Order Qty]*[WSale
Price]*(1-[Seasons]![VAT
Rate])),IIf([Orders]![Status]="REC",[Orders]![Received Qty]*[Std Retail
Price]*(1-[Seasons]![VAT Rate]),[Orders]![Order Qty]*[Std Retail
Price]*(1-[Seasons]![VAT Rate])))) AS Revenue,
Sum(IIf([Orders]![Status]="REC",[Orders]![Received Qty]*[Landed GBP
Cost],[Orders]![Order Qty]*[Landed GBP Cost])) AS Costs, IIf([SumOfOrder
Qty]=0,0,[SumOfReceived Qty]/[SumOfOrder Qty]) AS RecRatio
FROM Seasons INNER JOIN (Currencies INNER JOIN Orders ON Currencies.Original
= Orders.Currency) ON Seasons.Season = Orders.[Col Ssn]
WHERE (((Orders.[Col Ssn])=[Forms]![Orders Tracking]![SelectionString]))
GROUP BY Orders.Supplier
ORDER BY
Sum(IIf([Orders]![Channel]="WHOLESALE",IIf([Orders]![Status]="REC",[Orders]![Received
Qty]*[WSale Price]*(1-[Seasons]![VAT Rate]),[Orders]![Order Qty]*[WSale
Price]*(1-[Seasons]![VAT
Rate])),IIf([Orders]![Status]="REC",[Orders]![Received Qty]*[Std Retail
Price]*(1-[Seasons]![VAT Rate]),[Orders]![Order Qty]*[Std Retail
Price]*(1-[Seasons]![VAT Rate])))) DESC;

I am also trying to get the rest by subtracting the Top 20 query from the
ALL query, but it has not been so straightforward, because I am failing to
get a single line query that shows the REST totals for each field in the
query.

Paulo
 
J

John Spencer

So your query (qTop20)is query one.

One option is to use the first query to eliminate records before doing the sum
SELECT Sum(Orders.[Order Qty]) AS [SumOfOrder Qty]
, Sum(Orders.[Received Qty]) AS [SumOfReceived Qty]
, Sum(IIf([Orders]![Channel]="WHOLESALE",
IIf([Orders]![Status]="REC"
, [Orders]![Received Qty]*[WSale Price]*(1-[Seasons]![VAT Rate])
, [Orders]![Order Qty]*[WSale Price]*(1-[Seasons]![VAT Rate]))
, IIf([Orders]![Status]="REC"
,[Orders]![Received Qty]*[Std Retail Price]*(1-[Seasons]![VAT Rate])
, [Orders]![Order Qty]*[Std Retail Price]*(1-[Seasons]![VAT Rate]
)))) AS Revenue
, Sum(IIf([Orders]![Status]="REC",[Orders]![Received Qty]*[Landed GBP Cost]
, [Orders]![Order Qty]*[Landed GBP Cost])) AS Costs
, IIf([SumOfOrder Qty]=0,0,[SumOfReceived Qty]/[SumOfOrder Qty]) AS RecRatio
FROM (Seasons INNER JOIN (Currencies
INNER JOIN Orders ON Currencies.Original = Orders.Currency)
ON Seasons.Season = Orders.[Col Ssn])
LEFT JOIN qTop20 ON Orders.Supplier = qTop20.Supplier
WHERE (((Orders.[Col Ssn])=[Forms]![Orders Tracking]![SelectionString]))
AND qTop20.Supplier is Null


Another option is to make second query (qTotalAll) just like you first query
without the TOP 20 and without the supplier so you get grand totals for all
the items.

SELECT Sum(Orders.[Order Qty]) AS [SumOfOrder Qty]
, Sum(Orders.[Received Qty]) AS [SumOfReceived Qty]
, Sum(IIf([Orders]![Channel]="WHOLESALE",
IIf([Orders]![Status]="REC"
, [Orders]![Received Qty]*[WSale Price]*(1-[Seasons]![VAT Rate])
, [Orders]![Order Qty]*[WSale Price]*(1-[Seasons]![VAT Rate]))
, IIf([Orders]![Status]="REC"
,[Orders]![Received Qty]*[Std Retail Price]*(1-[Seasons]![VAT Rate])
, [Orders]![Order Qty]*[Std Retail Price]*(1-[Seasons]![VAT Rate]
)))) AS Revenue
, Sum(IIf([Orders]![Status]="REC",[Orders]![Received Qty]*[Landed GBP Cost]
, [Orders]![Order Qty]*[Landed GBP Cost])) AS Costs
, IIf([SumOfOrder Qty]=0,0,[SumOfReceived Qty]/[SumOfOrder Qty]) AS RecRatio
FROM Seasons INNER JOIN (Currencies
INNER JOIN Orders ON Currencies.Original = Orders.Currency)
ON Seasons.Season = Orders.[Col Ssn]
WHERE (((Orders.[Col Ssn])=[Forms]![Orders Tracking]![SelectionString]))

Now you need a third query 9qTotalTop20) that makes a grand total of the first
query.
SELECT Sum(SumOfOrder qty) as SumOqty
, Sum(SumOfRecieved qty) As SumRqty
, Sum(Revenue) as SumRevenure
, Sum(Costs) as SumCosts
FROM qTop20

FINALLY, you can run
SELECT qTotalAll.[SumOfOrder Qty] - qTotalTop20.SumOqty
, ...
FROM qTotalAll, qTotalTop20

You can try both solutions and see if they work. AND if one is faster than
the other.

Hope this has given you some ideas.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
It's a rather long SQL string for the Top 20 query:

SELECT TOP 20 Orders.Supplier, Sum(Orders.[Order Qty]) AS [SumOfOrder Qty],
Sum(Orders.[Received Qty]) AS [SumOfReceived Qty],
Sum(IIf([Orders]![Channel]="WHOLESALE",IIf([Orders]![Status]="REC",[Orders]![Received
Qty]*[WSale Price]*(1-[Seasons]![VAT Rate]),[Orders]![Order Qty]*[WSale
Price]*(1-[Seasons]![VAT
Rate])),IIf([Orders]![Status]="REC",[Orders]![Received Qty]*[Std Retail
Price]*(1-[Seasons]![VAT Rate]),[Orders]![Order Qty]*[Std Retail
Price]*(1-[Seasons]![VAT Rate])))) AS Revenue,
Sum(IIf([Orders]![Status]="REC",[Orders]![Received Qty]*[Landed GBP
Cost],[Orders]![Order Qty]*[Landed GBP Cost])) AS Costs, IIf([SumOfOrder
Qty]=0,0,[SumOfReceived Qty]/[SumOfOrder Qty]) AS RecRatio
FROM Seasons INNER JOIN (Currencies INNER JOIN Orders ON Currencies.Original
= Orders.Currency) ON Seasons.Season = Orders.[Col Ssn]
WHERE (((Orders.[Col Ssn])=[Forms]![Orders Tracking]![SelectionString]))
GROUP BY Orders.Supplier
ORDER BY
Sum(IIf([Orders]![Channel]="WHOLESALE",IIf([Orders]![Status]="REC",[Orders]![Received
Qty]*[WSale Price]*(1-[Seasons]![VAT Rate]),[Orders]![Order Qty]*[WSale
Price]*(1-[Seasons]![VAT
Rate])),IIf([Orders]![Status]="REC",[Orders]![Received Qty]*[Std Retail
Price]*(1-[Seasons]![VAT Rate]),[Orders]![Order Qty]*[Std Retail
Price]*(1-[Seasons]![VAT Rate])))) DESC;

I am also trying to get the rest by subtracting the Top 20 query from the
ALL query, but it has not been so straightforward, because I am failing to
get a single line query that shows the REST totals for each field in the
query.

Paulo



John Spencer said:
Care to post your existing query that sums the TOP 20?

The sum of the rest is basically the sum of ALL minus the Sum of the Top 20.



John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How do I remove the top half of a block interval? 1
Cumulative formula for word table 0
Tracked changes by multiple people modified to one 0
SUM NOT INCLUDING ALL RECORDS 4
top 20 records by group 5
Append Query with Oldest top Records by Group 2
Split form 0
Adding data to MS Access from csv file 0

Top