I have a query (SQL below) that ranks the surveys of projects against one another. What I want to do is write the query so that it ranks Bill's Development 4 projects (1, 2, 3 or 4) then it ranks Resource Systems Projects (1 or 2), etc.....
What it's doing, however, is ranking all of the projects against one another. I tried modifying the group by portions and I know there is probably somewhere in the code that I need to say where the vendor ID = (some other vendor ID). This query (as is) works nicely if we want to look at all of the projects as a whole - but if we want to drill down a level and look at the projects per vendor, I'm not seeing an easy way to rank. It could be that it's 5 PM and I'm tired - but I can't come up with any ideas.... :-/
I'm trying to avoid doing multiple queries - which I'm sure I'll have to - but even by breaking it apart, I don't know an easy way to view the data of 33 different vendors we are trying to rate. I wouldn't want to run each individually -I'd ideally like to click a button and it will go in and provide a query/report for each vendor.
Bill's Development Inc 0000027387 75.69% 7
Bill's Development Inc 0000030245 83.45% 4
Bill's Development Inc 0000030364 77.93% 6
Bill's Development Inc 0000034311 89.48% 3
Resource Systems, Inc 0000033861 97.24% 1
Resource Systems, Inc 0000033852 74.25 9
Retail Services, Inc. 0000032331 81.21% 5
Russ GC 0000033420 91.72% 2
Tri-City 0000033783 75.17% 8
SQL
SELECT TBLVendorInfo.VendorName, GCRank.ProjectID, GCRank.ScorePercent, (Select Count (*) from Query_Qtr_Vendor_Report_Card Where [ScorePercent]>[GCRank].[ScorePercent])+1 AS Ranking
FROM TBLVendorInfo INNER JOIN Query_Vendor_Report_Card AS GCRank ON TBLVendorInfo.VendorID = GCRank.TBLVendorInfo.VendorID
GROUP BY TBLVendorInfo.VendorName, GCRank.ProjectID, GCRank.ScorePercent;
What it's doing, however, is ranking all of the projects against one another. I tried modifying the group by portions and I know there is probably somewhere in the code that I need to say where the vendor ID = (some other vendor ID). This query (as is) works nicely if we want to look at all of the projects as a whole - but if we want to drill down a level and look at the projects per vendor, I'm not seeing an easy way to rank. It could be that it's 5 PM and I'm tired - but I can't come up with any ideas.... :-/
I'm trying to avoid doing multiple queries - which I'm sure I'll have to - but even by breaking it apart, I don't know an easy way to view the data of 33 different vendors we are trying to rate. I wouldn't want to run each individually -I'd ideally like to click a button and it will go in and provide a query/report for each vendor.
Bill's Development Inc 0000027387 75.69% 7
Bill's Development Inc 0000030245 83.45% 4
Bill's Development Inc 0000030364 77.93% 6
Bill's Development Inc 0000034311 89.48% 3
Resource Systems, Inc 0000033861 97.24% 1
Resource Systems, Inc 0000033852 74.25 9
Retail Services, Inc. 0000032331 81.21% 5
Russ GC 0000033420 91.72% 2
Tri-City 0000033783 75.17% 8
SQL
SELECT TBLVendorInfo.VendorName, GCRank.ProjectID, GCRank.ScorePercent, (Select Count (*) from Query_Qtr_Vendor_Report_Card Where [ScorePercent]>[GCRank].[ScorePercent])+1 AS Ranking
FROM TBLVendorInfo INNER JOIN Query_Vendor_Report_Card AS GCRank ON TBLVendorInfo.VendorID = GCRank.TBLVendorInfo.VendorID
GROUP BY TBLVendorInfo.VendorName, GCRank.ProjectID, GCRank.ScorePercent;
