Query to select bottom ten records per catergory

[Mubz12]

Hi,



Been following this thread with interest. I have taken Esandres data above and put it into an access database. Using Bulzies queries, I too am only getting the top 10 for 101.

What is interesting is if you change it from Select top 10 - to Select top 16 - you would then get all 15 entries for 101 and the 1st entry to 102. Select top 20 would give you all 15 for 101 and 5 for 102 and so on.....

[orange]

Can you post the latest version of the database?

[Esandres]

Hi Orange

Please find a snippet of the database attached below and the two queries:



SELECT UXW.[Branch Code], UXW.Line, Sum(UXW.[Unknown Loss Value]) AS [SumOfUnknown Loss Value]
FROM UXW
GROUP BY UXW.[Branch Code], UXW.Line
ORDER BY UXW.[Branch Code], Sum(UXW.[Unknown Loss Value]);



SELECT qryUXWSorted.[Branch Code], qryUXWSorted.[SumOfUnknown Loss Value], qryUXWSorted.Line
FROM qryUXWSorted
WHERE (((qryUXWSorted.[SumOfUnknown Loss VALUE]) In (SELECT TOP 10 [SumOfUnknown Loss Value] FROM qryUXWSorted)))
ORDER BY qryUXWSorted.[Branch Code], qryUXWSorted.[SumOfUnknown Loss Value];

[Bulzie]

Yep its odd, I swear when i first set up a table and tested it it gave me 10 for Branch 1 and 10 for Branch 2, then it was giving me 10 for branch 1 and 7 for branch 2. Now it only gives 10 for branch 1 even though the option at the top of in the ribbon is set to All. Maybe something in that 2nd select statement is off?

[Bulzie]

Well, if I go off the table only (no summing of UnknownLossValue), it seems to work. Try this code just on your table(change names to yours) and see if it returns 10 records per branch:

SELECT BranchCode, UnknownLossValue, Line
FROM UXW AS t
WHERE (((t.UnknownLossValue) In (SELECT TOP 10 UnknownLossValue
FROM UXW
WHERE BranchCode = t.BranchCode
ORDER BY BranchCode, UnknownLossValue
)))
ORDER BY t.BranchCode, t.UnknownLossValue;

But when I try to sort and sum the UnknownLossValue and use that query and SumOfUnknownLossValue, it still counts the individual records in the 10 count. So if you had 2 records that summed up into 1 record, it would only return 9 records as it sums and groups those 2 records but for the query, it still treats them as individual records in counting the 10.

[Bulzie]

If I saved that first query that Sums the UnknownLossValue as a table and then use that in the 2nd query, it seems to work. Maybe one of the other contributors can get it to work with 1 query or another way.

Run this first one as a MakeTable query, to create table UXW2:

SELECT UXW.BranchCode, Sum(UXW.UnknownLossValue) AS SumOfUnknownLossValue, UXW.Line INTO UXW2
FROM UXW
GROUP BY UXW.BranchCode, UXW.Line
ORDER BY UXW.BranchCode, Sum(UXW.UnknownLossValue), UXW.Line;


Then run this one that should give the top 10. Not sure why the query would not do correctly versus a table.

SELECT t.BranchCode, t.SumOfUnknownLossValue, t.Line
FROM UXW2 AS t
WHERE (((t.SumOfUnknownLossValue) In (SELECT TOP 10 SumOfUnknownLossValue
FROM UXW2
WHERE BranchCode = t.BranchCode
ORDER BY BranchCode, SumOfUnknownLossValue
)))
ORDER BY t.BranchCode, t.SumOfUnknownLossValue;

[orange]

This is my attempt.
It shows a way of using SQL (Group, Sum and TOP)
to display a Sum of the Lowest 10 values of each Group of records.
The data in this sample is from the zip file in post 17.

Try this sql

SELECT esandres.branch
    ,Sum(esandres.UnknownLossValue) AS SumOfUnknownLossValue
FROM esandres
WHERE esandres.UnknownLossValue IN (
        SELECT TOP 10 UnknownLossValue
        FROM esandres AS X
        WHERE x.branch = esandres.branch
        ORDER BY UnknownLossValue
        )
GROUP BY esandres.branch

Result based on your data file is

branch	SumOfUnknownLossValue
101	-2592.939152
102	-3504.529616

I removed spaces from field names and imported into access as table Esandres

[Esandres]

Thank you guys,

Just running them out now.