Add digit before each group in a query (1 Viewer)

[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.

 

[Pat Hartman]

What is the point of the number? If you just want to number the items in the printed report, the best solution is the property settings.

If you want the number to be permanent, you should add it to the table as records are added. I generally use serial numbers incremented by 10. That allows me to add rows later between two existing rows and then renumber the set.

 

[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