Add prefix to top 10 query

[gojets1721]

I have a query which groups and counts values, and only shows the ten highest (SELECT TOP 10). It basically looks like this:

SubCategory	CountofSubCategory
Henry	67
John	53
....and so on

I want to add a prefix number to the subcategory field so that it basically shows up as:

SubCategory	CountofSubCategory
1. Henry	67
2. John	53
...and so on

Suggestions on if that's possible? I can post my SQL code if beneficial

 

[cheekybuddha]

If you have saved your query (eg 'qrySubCategoryCounts'), you should be able to do something like:

SELECT
  c.Counter & '. ' & a.SubCategory AS SubCategory,
  a.CountOfSubCategory
FROM qrySubCategoryCounts a
INNER JOIN (
  SELECT
    COUNT(*) AS Counter
  FROM qrySubCategoryCounts b
  WHERE b.CountOfSubCategory <= a.CountOfSubCategory
) c
ORDER BY
  a.CountOfSubCategory DESC;

(NB Untested!)

 

[tvanstiphout]

I have a query which groups and counts values, and only shows the ten highest (SELECT TOP 10). It basically looks like this:

SubCategory	CountofSubCategory
Henry	67
John	53
....and so on

I want to add a prefix number to the subcategory field so that it basically shows up as:

SubCategory	CountofSubCategory
1. Henry	67
2. John	53
...and so on

Suggestions on if that's possible? I can post my SQL code if beneficial

 

[tvanstiphout]

If this data will be shown in a report, you can use the technique described here: https://support.microsoft.com/en-us/office/counting-in-reports-489791e0-dee2-46dd-8a72-f835998bd72e

 

[ebs17]

Untested!

Table a is unknown in subquery c and will throw an error.

SELECT
   DCount("*", "qrySubCategoryCounts", "CountOfSubCategory > " & a.CountOfSubCategory) + 1 & '. ' & a.SubCategory AS SubCategory,
   a.CountOfSubCategory
FROM
   qrySubCategoryCounts a

 

[gojets1721]

Table a is unknown in subquery c and will throw an error.

SELECT
   DCount("*", "qrySubCategoryCounts", "CountOfSubCategory > " & a.CountOfSubCategory) + 1 & '. ' & a.SubCategory AS SubCategory,
   a.CountOfSubCategory
FROM
   qrySubCategoryCounts a

That worked! Thanks so much!