Avatar of dhemple
dhemple
Flag for United States of America asked on

Access Record Ranking by Group and Percent

Hello Experts

I need your help with an update query that will populate the Mkt Share Ranking column in the example below.  Highest market share would get a ranking of 1, the next highest a ranking of 2, etc.

Thank you.

GroupId      Mkt Share Percent      Mkt Share Ranking
a1567      32%      2
a1567      10%      3
a1567      58%      1
b4382      12%      3
b4382      25%      2
b4382      63%      1
c9253      15%      3
c9253      12%      4
c9253      27%      2
c9253      46%      1
Microsoft Access

Avatar of undefined
Last Comment
dhemple

8/22/2022 - Mon
Rey Obrero (Capricorn1)

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
dhemple

ASKER
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;
ASKER CERTIFIED SOLUTION
Rey Obrero (Capricorn1)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Sharath S

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;

Open in new window

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
dhemple

ASKER
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;
Rey Obrero (Capricorn1)

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
dhemple

ASKER
I understand and agree.  However, my current need is to have the percent and ranking stored.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
dhemple

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