Page 1 of 3 123 LastLast
Results 1 to 15 of 38
  1. #1
    Esandres is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2017
    Posts
    18

    Query to select bottom ten records per catergory

    Hi All

    I am very new to access but I was wondering if there is any way to write query which would give me the bottom ten records per branch/category. I am able to get top/bottom ten Unknown Loss Values Sum out of the whole database but I want that for each branch.


    SELECT UXW1.JOURNAL, UXW1.[Branch Code], Sum(UXW1.[Unknown Loss Value]) AS [SumOfUnknown Loss Value]
    FROM UXW1


    GROUP BY UXW1.JOURNAL, UXW1.[Branch Code]
    HAVING (((UXW1.JOURNAL) Is Null));





    Is this possible?

    Kindest regards

    Emerson

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    make a query to sort those records to the top. *descend or acsending
    set TOP VALUES=10
    but you need a query for each Group.
    (you could use a listbox of all goups, then cycle thru the list to run a query on each)

  3. #3
    Esandres is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2017
    Posts
    18
    Thank you ranman256. How do I use a listbox? Is there a way to loop this query through the groups/branches?

  4. #4
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Can you post a sample of your data, maybe 2 groups worth.

  5. #5
    Esandres is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2017
    Posts
    18
    Hi I should probably edit my initial query. I need the top ten lines for Unknown Loss value per branch. Please find a screen shot and zip file attached.
    Attached Thumbnails Attached Thumbnails Access.PNG  
    Attached Files Attached Files

  6. #6
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Will they all have more then 10 lines or some could be less(so you would include all of those if less then 10 records?)

  7. #7
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    I created a table with just ID(auto), BranchCode(num), UnknownLossValue(Double). Create first query below to sort the data. Maybe others know of a way to include the sort in the bottom query but I could not, it would always bring back the 10 based on the ID field of the table.

    qryUXWSorted:

    SELECT UXW.BranchCode, UXW.UnknownLossValue
    FROM UXW
    ORDER BY UXW.BranchCode, UXW.UnknownLossValue;

    See if this query below gets what you need, copy in query designer and then go to design if needed. I quess you can add other fields to the Select statement.
    Got code from http://www.allenbrowne.com/subquery-01.html#TopN

    SELECT qryUXWSorted.BranchCode, qryUXWSorted.UnknownLossValue
    FROM qryUXWSorted
    WHERE (((qryUXWSorted.UnknownLossValue) In (SELECT TOP 10 UnknownLossValue FROM qryUXWSorted)))
    ORDER BY qryUXWSorted.BranchCode, qryUXWSorted.UnknownLossValue;

  8. #8
    Esandres is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2017
    Posts
    18
    Hi Bulzie

    Thank you for this. I will give it a go now

  9. #9
    Esandres is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2017
    Posts
    18
    Hi Bulzie

    Thank you for this. From your qry sorted piece. How would I add 'Line'?

    The out put of the query ideally would give me the below for each branch:


    Branch Line Unknown Loss Sum

    101 77059 -£100
    101 3456 -£99
    101 34256 -£78

  10. #10
    Esandres is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2017
    Posts
    18
    Hi Bulzie


    I have added sum and Line to qryUXWsorted:

    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;

    For the second part of your query, do you know how I could add the line aspect in?

    many thanks in advance

  11. #11
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    qryUXWSorted:

    SELECT UXW.BranchCode, UXW.UnknownLossValue, UXW.Line
    FROM UXW
    ORDER BY UXW.BranchCode, UXW.UnknownLossValue;


    SELECT qryUXWSorted.BranchCode, qryUXWSorted.UnknownLossValue, qryUXWSorted.Line
    FROM qryUXWSorted
    WHERE (((qryUXWSorted.UnknownLossValue) In (SELECT TOP 10 UnknownLossValue FROM qryUXWSorted)))
    ORDER BY qryUXWSorted.BranchCode, qryUXWSorted.UnknownLossValue;

  12. #12
    Esandres is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2017
    Posts
    18
    Hi Bulzie. Just ran it out and received the below.
    Branch Code Line SumOfUnknown Loss Value
    141 764910 628.271875
    149 764910 628.271875
    238 764910 628.271875
    665 764910 628.271875
    847 532750 334.564501
    847 88903 398.948538
    847 36953 495.028248
    847 801689 495.3795
    847 879015 536.315
    847 417062 581.399907
    847 764910 628.271875
    847 593735 674.56
    847 446601 989.407038
    847 861533 1258.88


    However I was looking more towards getting the top ten lines based on SUM of unknown loss for each branch. Similar to the below. Is this possible?

    Branch Line SUM of Unknown Loss Value
    101 77059 -£100
    101 33456 -£99
    101 78694 -£97
    101 23453 -£96
    101 12345 -£94
    101 34566 -£93
    101 434545 -£92
    101 456567 -£91
    101 676777 -£90
    101 455546 -£89
    102 34355 -£500
    102 67890 -£480
    102 45675 -£200
    102 678907 -£150
    102 45632 -£100
    102 45673 -£99
    102 12345 -£80
    102 567890 -£56
    102 678543 -£30
    102 556799 -£27

  13. #13
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    How are you summing that Unknown Loss value, what fields are you grouping by to do this, Branch and Line?

  14. #14
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Maybe this if grouped by Branch and Line:

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

    SELECT qryUXWSorted.BranchCode, qryUXWSorted.SumOfUnknownLossValue, qryUXWSorted.Line
    FROM qryUXWSorted
    WHERE (((qryUXWSorted.SumOfUnknownLossValue) In (SELECT TOP 10 SumOfUnknownLossValue FROM qryUXWSorted)))
    ORDER BY qryUXWSorted.BranchCode, qryUXWSorted.SumOfUnknownLossValue;

  15. #15
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Query to Select Records and Alphabetize them
    By MarcieFess in forum Queries
    Replies: 2
    Last Post: 10-24-2013, 09:34 AM
  2. SELECT TOP 10 Query returns 12 records
    By Paul H in forum Queries
    Replies: 8
    Last Post: 09-11-2013, 03:38 PM
  3. Replies: 1
    Last Post: 04-02-2013, 08:41 AM
  4. Replies: 1
    Last Post: 07-10-2011, 11:20 PM
  5. Replies: 3
    Last Post: 01-04-2011, 07:06 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums