Query to select bottom ten records per catergory

[Esandres]

Hi

I am summing on design menu under totals. I will plug the new query in and let you know. Thanks again for all the help.

[Esandres]

Hi

Just finished running the query out. Hmm seems to have given the same results as before, but switched Sum of unknown around with line.

Branch Code	SumOfUnknown Loss Value	Line
141	628.271875	764910
149	628.271875	764910
238	628.271875	764910
665	628.271875	764910
847	334.564501	532750
847	398.948538	88903
847	495.028248	36953
847	495.3795	801689
847	536.315	879015
847	581.399907	417062
847	628.271875	764910
847	674.56	593735
847	989.407038	446601
847	1258.88	861533

[Bulzie]

So you are grouping on Branch and Line and summing Unknown Lose Value correct? Does the first query return the values in the correct order? Post the first query code again.

[Esandres]

Yes, grouping by branch and line to pull the top ten lines with the highest Sum Unknown Loss Value.

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] DESC , Sum(UXW.[Unknown Loss Value]) DESC;

[Bulzie]

I don't think that code is right. I think you need ASC sort as negitive comes before positive. The Order By defaults to Asc.

If you take the DESC out and run it, does it give you the Branch, Line and Sum of UnknownLossValue in the correct order? So that now you just need it to select the top 10?

[Esandres]

Hi Bulzie, yes it is now in correct order. Will the top 10 query give me top ten for each branch? Or will it give the total 10 Sum Unknown Loss Values?

[Bulzie]

If you run those 2 queries I posted it should work. Make sure the first query sorts them in the correct order and sums that field correctly. Then run that 2nd query, should work.

[Esandres]

Hi Bulzie

Just ran out the query, it has half worked as it has given me the top ten for branch 101. Is there away to extrapolate this query for all branches?

Branch Code	SumOfUnknown Loss Value	Line
101	-1779.099396	91255
101	-1035.652905	53457
101	-970.2	728936
101	-549.746821	833648
101	-533.921515	611725
101	-482.12	86469
101	-479.385922	512333
101	-469.198472	724119
101	-454.8	637909
101	-440.3	477804
173	-970.2	728936
206	-970.2	728936
214	-970.2	728936
223	-970.2	728936
226	-970.2	728936
229	-970.2	728936
237	-440.3	477804
462	-970.2	728936
479	-970.2	728936
684	-970.2	728936
780	-970.2	728936
781	-970.2	728936
815	-970.2	728936

[Bulzie]

That code should not limit to just 1 branch, it should do all of them. Are you sure Branch 173 for example has more then 1 record with different Line values? If not then as it groups Branch and Line, you would only have 1 record.

[Esandres]

Hi Bulzie

It has not reported any for branch 102 for example. The entries under 101 I think are extra values for branches which share the same Unknown Loss Value as 101.

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]);

The sorting worked after I deleted DESC for the query above.




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];

However the (SELECT TOP 10 piece above only seems to be pulling the first 10 rows for 101 which is at the top of the table for the first query.

I hope that makes sense? I feel like you have helped me get very close and any further advice you could give me would be really appreciated. I apologise for the time you are having to spend on this.

[Bulzie]

Not sure what is going on, maybe someone else has ideas. It is odd, when I run the code I get 10 rows for first branch but only 7 for 2nd. But if I change the code to "Top 13" instead, it gives me 13 for branch 1 and the 10 records for branch 2. Not sure why subsequent branch groups number returned is different.

[Esandres]

Thanks for all your help Bulzie.

I have a query below, which gives the top ten for branch 101. Its nearly there, I just need it replicated for all branches.


SELECT TOP 10 qryUXWsorted.[Branch Code], qryUXWsorted.Line, qryUXWsorted.[SumOfUnknown Loss Value]
FROM qryUXWsorted

[Esandres]

Moreover the code below:

SELECT TOP 10 qryUXWsorted.[Branch Code], qryUXWsorted.Line, qryUXWsorted.[SumOfUnknown Loss Value]
FROM qryUXWsorted

gives me the exact same output as your code below:

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]

Well no, the first one is where you selected that option from the ribbon bar and will only give you the first 10 rows of the recordset. The one below should give you the first 10 rows of each branch but not sure why it is only doing partial rows after the first branch.

[Esandres]

Branch Code	Line	SumOfUnknown Loss Value
102	91255	-963.249216
102	593735	-417.28
101	451045	-342.025674
101	88061	-281.742114
101	48727	-280.390644
101	93404	-271.438165
101	39809	-258.544998
102	552211	-248
101	436241	-240.771415
101	727059	-234.93
102	840052	-230.58
101	91255	-227.602572
102	92701	-201.022376
101	19712	-188.470701
101	92701	-185.573776
102	870483	-185.04
102	62005	-182.897651
102	764855	-176.460373
102	55290	-172.649677
101	625429	-167.722645
102	876191	-161.69
101	846440	-160.58
102	62405	-157.650031
102	514000	-153.72
102	59993	-153.595538
102	48727	-152.788783
102	63121	-148.15999
101	27969	-128.813714
101	433110	-122.4
101	809469	-115.164924

Above is 15 lines for 101 and 102.


I placed this in a brand new Access Database.


Used your query to sort it by branch and sumofUnknown Loss Value ascending.


Used the second query you provided and still only got the top 10 for 101. Bizarre - really unsure why this is happening even with a tiny amount of data and a new database.