Add digit before each group in a query

[gojets1721]

I have the a table full of customer complaints. The below query groups the data by Complaint Type and includes an average of the Product Cost.

SELECT TOP 10 ComplaintType, Avg([ProductCost]) AS AvgOfCost
FROM tblComplaintData
WHERE (((ReportYear)="2023"))
GROUP BY ComplaintType
ORDER BY Avg([ProductCost]) DESC;

I want to include a "1.", "2.", etc. in front of each Complaint Type. Any idea how to build that into the query?

Essentially the data looks like this right now:

ComplaintType​	AvgOfCost​
Service	$223.50
Timeliness	$123.41

And I want it to show as this in the query:

ComplaintType​	AvgOfCost​
1. Service	$223.50
2. Timeliness	$123.41

 

[GPGeorge]

Here's Allen Browne's method.

Microsoft Access tips:

How to rank records in Microsoft Access

allenbrowne.com

 

[LarryE]

Just include a CompaintTypeNumber field in your table. Then sort by ComplaintTypeNumber.

 

[ebs17]

Any idea how to build that into the query?

Save your query under a name, e.g. qryXX. You can then set up another query.

SELECT
   DCount("*", "qryXX", "ComplaintType = '" & Q.ComplaintType & "' AND AvgOfCost > " & Str(Q.AvgOfCost)) + 1 & "." & Q.ComplaintType AS ComplaintTypeX,
   Q.AvgOfCost
FROM
   qryXX AS Q