I want to rank each rep(Mbrid), by highest GDC within each Practice_id. I also want to have a count of the number of members in Practice_ID, Here's the code I have so far that is working. I'm unclear how to assign a rank and a count within this query. Thanks in advance for help!!
SELECT [REP_FILE_12-22-2015].PLTFM, [REP_FILE_12-22-2015].POSITION, [REP_FILE_12-22-2015].Practice_ID, [REP_FILE_12-22-2015].Practice_Name, [REP_FILE_12-22-2015].MBRID, [REP_FILE_12-22-2015].Practice_StrtDate_Member, [REP_FILE_12-22-2015].Denominator_Indicator, Tbl_sales_metrics.[YTD_Sales], IIf([YTD_Sales]>600000,"yes","no") AS QualifiedGDC
FROM [REP_FILE_12-22-2015] LEFT JOIN Tbl_sales_metrics ON [REP_FILE_12-22-2015].MBRID = Tbl_sales_metrics.AdvisorNo
WHERE ((([REP_FILE_12-22-2015].PLTFM)=1) AND (([REP_FILE_12-22-2015].POSITION)=3) AND (([REP_FILE_12-22-2015].Practice_ID) Is Not Null))
ORDER BY [REP_FILE_12-22-2015].Practice_ID, Tbl_sales_metrics.[YTD_Sales] DESC;
SELECT [REP_FILE_12-22-2015].PLTFM, [REP_FILE_12-22-2015].POSITION, [REP_FILE_12-22-2015].Practice_ID, [REP_FILE_12-22-2015].Practice_Name, [REP_FILE_12-22-2015].MBRID, [REP_FILE_12-22-2015].Practice_StrtDate_Member, [REP_FILE_12-22-2015].Denominator_Indicator, Tbl_sales_metrics.[YTD_Sales], IIf([YTD_Sales]>600000,"yes","no") AS QualifiedGDC
FROM [REP_FILE_12-22-2015] LEFT JOIN Tbl_sales_metrics ON [REP_FILE_12-22-2015].MBRID = Tbl_sales_metrics.AdvisorNo
WHERE ((([REP_FILE_12-22-2015].PLTFM)=1) AND (([REP_FILE_12-22-2015].POSITION)=3) AND (([REP_FILE_12-22-2015].Practice_ID) Is Not Null))
ORDER BY [REP_FILE_12-22-2015].Practice_ID, Tbl_sales_metrics.[YTD_Sales] DESC;