Results 1 to 12 of 12
  1. #1
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316

    Unanswered: Ranking Number in Top 10 Query

    Here is a simple Top 10 query:

    Code:
    SELECT TOP 10 PHDept, PartNo, Sum(NetSales) AS SumOfNetSales
    FROM tblWeeklyProdSalesBaseData
    GROUP BY PHDept, PartNo
    ORDER BY Sum(NetSales) DESC;
    Is there a simple way to get the ranking number next to each line?

  2. #2
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    Look at "DemoQryCountRowsA2000.mdb" (attachment).
    I think it is what you need.
    Look at Query2 (CountW: column), look at Module1,
    Open Form1 and try.
    I suggest make a report on query.
    Attached Files Attached Files

  3. #3
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    Thanks for that. However, I'm getting odd results, perhaps because I am using GROUP BY?

  4. #4
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    Here is my full query:

    Code:
    SELECT TOP 10 tblPHDept.DeptDesc, tblWeeklyProdSalesBaseData.PartNo, tblWeeklyProdSalesBaseData.PartDesc, Sum(tblWeeklyProdSalesBaseData.NetSales) AS SumOfNetSales, [NetSales]-[TotalCost] AS &#163;NetMargin, IIf([NetSales]<1,0,([NetSales]-[TotalCost])/[NetSales]) AS [%NetMargin]
    FROM tblWeeklyProdSalesBaseData INNER JOIN tblPHDept ON tblWeeklyProdSalesBaseData.PHDept = tblPHDept.DeptCode
    WHERE (((tblWeeklyProdSalesBaseData.PHDept) Is Not Null And (tblWeeklyProdSalesBaseData.PHDept)<>""))
    GROUP BY tblPHDept.DeptDesc, tblWeeklyProdSalesBaseData.PartNo, tblWeeklyProdSalesBaseData.PartDesc, [NetSales]-[TotalCost], IIf([NetSales]<1,0,([NetSales]-[TotalCost])/[NetSales])
    HAVING (((tblWeeklyProdSalesBaseData.PartNo) Is Not Null And (tblWeeklyProdSalesBaseData.PartNo)<>""))
    ORDER BY Sum(tblWeeklyProdSalesBaseData.NetSales) DESC;
    When I add this column: CountW: RS(1,[DeptDesc]) I get the following results:

    CountW
    52502
    50504
    51056
    53084
    53078
    51896
    52504
    51338
    51204
    51980

  5. #5
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    OK, I tried doing a SELECT from my original query (using a button on a form to reset wRec before the query opens), but I still don't get the right results:

    CountW
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33

  6. #6
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    Send me a short example of your mdb. (tbl, query, form, module).

  7. #7
    Join Date
    Sep 2006
    Posts
    265
    You could also try treating the one query to collect and analyse the data and the report itself having its own query shall we say "render" the information. Your recordset from the first query is now reduced to 10 records as the number crunching is done. I don't have any qualms about layering queries as some queries are the "basic building blocks" of a database whilst other sit on top of these basic queries and are available for re-iteration anywhere in the database.

  8. #8
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    I haven't had time to post up anything from my mdb yet as it's quite large and cutting it up will take some effort. As a temporary measure, I ended up doing it all in code, which doesn't strike me as being the most elegant solution, but it will have to do for now...

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Is this for export or something? If it's for reporting, then you have more options.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  10. #10
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    It's for reporting. I needed to do some other stuff to the data, so I decided to do the rank numbers in VBA in the end...

  11. #11
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    I guess it's after the fact now, but you could have numbered the rows in the report with a running sum.
    Inspiration Through Fermentation

  12. #12
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    That's a good idea! I'll remember that for next time.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •