Access Record Ranking by Group and Percent

test this

SELECT TableX.GroupID, TableX.[Mkt Share Percent], (select Count(*) from TableX as Y where Y.GroupID=TableX.GroupID And Y.[Mkt Share Percent]>=TableX.[Mkt Share Percent]) AS [Mkt Share Ranking]
FROM TableX
ORDER BY TableX.GroupID, TableX.[Mkt Share Percent] DESC;


also, it is not advisable to store calculated values to the table, you can always get the correct values running the query in case the values for the Mkt Share Percent changed

Thanks for your response Rey.

I must have something wrong.  When I put in the actual table and field names here's what I have.  I'm getting an error.  Can you help me understand what I'm doing wrong?  Thanks

SELECT [BidFileTable-OutputForIT].drug_id_38, [BidFileTable-OutputForIT].market_share_pct, (select count(*) from [BidFileTable-OutputForIT] as Y where Y.drug_id_38= [BidFileTable-OutputForIT]drug_id_38 and Y.market_share_percent >=  [BidFileTable-OutputForIT].market_share_pct) AS [market_share_rank]
FROM [BidFileTable-OutputForIT]
ORDER BY [BidFileTable-OutputForIT].drug_id_38, [BidFileTable-OutputForIT].market_share_pct DESC;

You missed a dot in the WHERE clause.
where Y.drug_id_38= [BidFileTable-OutputForIT]drug_id_38
fixed it.

SELECT [BidFileTable-OutputForIT].drug_id_38, 
       [BidFileTable-OutputForIT].market_share_pct, 
       (SELECT COUNT(*) 
          FROM [BidFileTable-OutputForIT] AS Y 
         WHERE Y.drug_id_38 = [BidFileTable-OutputForIT].drug_id_38 
           AND Y.market_share_pct >= [BidFileTable-OutputForIT].market_share_pct) AS [Mkt Share Ranking] 
  FROM [BidFileTable-OutputForIT] 
 ORDER BY [BidFileTable-OutputForIT].drug_id_38, 
          [BidFileTable-OutputForIT].market_share_pct DESC;

Select allOpen in new window

Thanks Rey and Sharath

I got the query to work, but when I changed it to a make table query, the query would not finish making the table.

BidFileTable-OutputForIT has 34K records it has to sort through to create the table.  Would this many records be an issue for this type of query?

SELECT [BidFileTable-OutputForIT].drug_id_38, [BidFileTable-OutputForIT].market_share_pct, (select count(*) from [BidFileTable-OutputForIT] as Y where Y.drug_id_38= [BidFileTable-OutputForIT].drug_id_38 and Y.market_share_pct >=  [BidFileTable-OutputForIT].market_share_pct) AS market_share_rank1 INTO tbl_TEMP_DrugId38MktShare
FROM [BidFileTable-OutputForIT]
ORDER BY [BidFileTable-OutputForIT].drug_id_38, [BidFileTable-OutputForIT].market_share_pct DESC;

as i've mentioned above

it is not advisable to store calculated values to the table, you can always get the correct values running the query in case the values for the Mkt Share Percent changed

I understand and agree.  However, my current need is to have the percent and ranking stored.

Thank you both for your help in getting this to work!!