Extract Top 5 Vendors By TOTAL (SUM) spend By Group

[dmax17]

Hi,

I am having trouble extracting top 5 vendor with the sumOfSpend (actual Field name is Spend which is dis-aggregated) for each of 5 groups.

Each group has thousands of transactions for dozens of vendors. The Table is designed as follow.

Table1:
GroupName : Text
VendorName : Text
Spend : Currency.

Thank you,

 

[jdraw]

Can you show us the SQL for the query you have created?
You may also get ideas from this article by Allen Browne

See Top N in the article

 

[dmax17]

Thank you. Here is my SQL code.

SELECT a.WBSKey, Sum(a.[Receipt amount]) AS SumReceipt, a.VendorName
FROM JoinCardspendTNEFull AS a
WHERE (((a.CLSNAME) Is Not Null) AND ((a.WbsKey) Not Like "[#]" And (a.WbsKey) In ("SB-1000085","SB-1000261","LO-1010525","LO-1003585","SB-1000307"))) 

AND

a.VendorName in (SELECT TOP 5 VendorName FROM JoinCardspendTNEFull AS b
WHERE b.WBSKey = a.WBSKEY
GROUP BY b.VENDORNAME
ORDER BY SUM(b.[Receipt Amount]))
 
GROUP BY a.WBSKey, a.VendorName
ORDER BY Sum(a.[Receipt amount]) DESC;

 

[jdraw]

And what exactly is the error or issue?
Can you attach a zipped copy of your database?