Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2015
    Posts
    2

    Unanswered: rank query field grouped by another field

    Dear all,
    I have the query below that return a table like:
    PLOTNR; period,Value, ID, Basal_area/ha, Basal_area/ha, perc_BA_sp

    What I want to is to add another field that rank the perc_BA_sp by PLOTNR descending (thus highest perc_BA_sp values rank one etc.)
    could help me out of this?

    Code:
    SELECT 
    [Q:INV1-Basal_area_plot-spp].PLOTNR, 1 AS period, [Q:INV1-Basal_area_plot-spp].Value, [Q:INV1-Basal_area_plot-spp].ID, [Q:INV1-Basal_area_plot-spp].[Basal_area/ha], [Q:INV1-Basal_area_plot].[Basal_area/ha], ([Q:INV1-Basal_area_plot-spp]![Basal_area/ha]/[Q:INV1-Basal_area_plot]![Basal_area/ha])*100 AS perc_BA_sp
    FROM
    [Q:INV1-Basal_area_plot-spp] INNER JOIN [Q:INV1-Basal_area_plot]
    ON
    [Q:INV1-Basal_area_plot-spp].PLOTNR = [Q:INV1-Basal_area_plot].PLOTNR
    GROUP BY
    [Q:INV1-Basal_area_plot-spp].PLOTNR, 1, [Q:INV1-Basal_area_plot-spp].Value, [Q:INV1-Basal_area_plot-spp].ID, [Q:INV1-Basal_area_plot-spp].[Basal_area/ha], [Q:INV1-Basal_area_plot].[Basal_area/ha]
    ORDER BY
    [Q:INV1-Basal_area_plot-spp].PLOTNR, ([Q:INV1-Basal_area_plot-spp]![Basal_area/ha]/[Q:INV1-Basal_area_plot]![Basal_area/ha])*100 DESC;

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    does this need to b e done in a query
    where are you cnbsumning the information (is it a single form or report?

    ...Im not certain there is an easy / straightforward way of doing this in SQL (in a query). in a report certainly.#

    in a report:-
    create an unbound text box
    create a report wide variable to containing the ranking, set it to 1
    in the report detail set the text property of the textbox to the value of your variable
    increment the variable
    if you need to cater ofr equal placings then modify the logic, but the princiiple reamisn the same

    if you need the information is a form or multiple forms & reports then it may be a cleverer approach to save the query into a temporary table and pull the ranking from that

    or you may be able to use a stored procedure
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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