Search code examples
sqlvbams-accessalphabeticalletter

SQL Query in MSAccess to Rank a Value Column with Letters based on it's Sort Order

I am trying to write an SQL Query on a table in MSAccess to add a virtual Column that will add sequential Letters of the Alphabet based on a Value column sorted in Descending order. 

------------------------------------------------
|    Filename    |    Zone    |    ValueCol    |
------------------------------------------------
|    abc         | Zone_MEA   |      33        |
|    abc         | Zone_DEA   |      29        |
|    abc         | Zone_SEO   |      21        |
|    abc         | Zone_GUY   |      09        |
|-----------------------------------------------
|    def         | Zone_SEO   |      30        |
|    def         | Zone_DEA   |      22        |
|    def         | Zone_MEA   |      07        |
|    def         | Zone_GUY   |      06        |
|----------------------------------------------|
|    ghi         | Zone_GUY   |      21        |
|    ghi         | Zone_MEA   |      12        |
|    ghi         | Zone_SEO   |      04        |
|    ghi         | Zone_DEA   |      04        |
------------------------------------------------

So all values in ValueCol sorted in descending order will receive a sequential letter starting from A per Zone set.

                                                  Virtual Col
---------------------------------------------------------------
|    Filename    |    Zone    |    ValueCol    |    Letter    |
---------------------------------------------------------------
|    abc         | Zone_MEA   |      33        |       A      |
|    abc         | Zone_DEA   |      29        |       B      |
|    abc         | Zone_SEO   |      21        |       C      |
|    abc         | Zone_GUY   |      09        |       D      |
|-------------------------------------------------------------|
|    def         | Zone_SEO   |      30        |       A      |
|    def         | Zone_DEA   |      22        |       B      |
|    def         | Zone_MEA   |      07        |       C      |
|    def         | Zone_GUY   |      06        |       D      |
|-------------------------------------------------------------|
|    ghi         | Zone_GUY   |      21        |       A      |
|    ghi         | Zone_MEA   |      12        |       B      |
|    ghi         | Zone_SEO   |      04        |       C      |
|    ghi         | Zone_DEA   |      04        |       D      |
---------------------------------------------------------------

Is there a way to write such an SQL query in MSAccess without resorting to creating any physical helper tables? (Exception maybe a virtual helper table, but don't know how to create one or how it may be used.)

EDIT: Each section is one particular filename.

Wrote this query on suggestions from @Erik A. Here's the query:

SELECT M.FILENAME, M.ZONE,M.[VALUECOL],

CHR(64 +  (
        SELECT COUNT(*) 
        FROM tblTest AS S
        WHERE 
            S.[FILENAME] = M.[FILENAME]
             AND S.[ZONE] <= M.[ZONE]
            AND S.[VALUECOL] <= M.[VALUECOL]
            AND S.[FILENAME]&S.[ZONE]&S.[VALUECOL]<=M.[FILENAME]&M.[ZONE]&M.[VALUECOL]
    ) ) AS POS

FROM tblTest AS M
GROUP BY M.[FILENAME], M.[ZONE], M.[VALUECOL]
ORDER BY M.[FILENAME] ASC, M.[VALUECOL] DESC,M.[ZONE] ASC
  • The Alphabetical order is still not sequential as can be seen in the below output.
  • Also getting duplicate letters within a particular FILENAME section.

enter image description here

Edit...once again: This takes of point 2 i.e. Duplicates, but not point 1.

SELECT M.FILENAME, M.ZONE,M.[VALUECOL],

CHR(64 +  (
        SELECT COUNT(*) 
        FROM tblTest AS S
        WHERE 
            S.[FILENAME] = M.[FILENAME]
            AND S.[FILENAME]&S.[ZONE] <= M.[FILENAME]&M.[ZONE]
             AND S.[FILENAME]&S.[ZONE]&S.[VALUECOL]<=M.[FILENAME]&M.[ZONE]&M.[VALUECOL]
    ) ) AS POS

FROM tblTest AS M
GROUP BY M.[FILENAME], M.[ZONE], M.[VALUECOL]
ORDER BY M.[FILENAME] ASC, M.[VALUECOL] DESC,M.[ZONE] ASC

enter image description here

Solution

  • A very general solution for a very general question:

    If you have well-defined ordering (you order by a column that doesn't have duplicates) and grouping, you can use a subquery to achieve this:

    It would look like this:

    SELECT 
    (
        SELECT COUNT(*)
        From MyTable s
        WHERE 
            s.GroupingColumn1 = m.GroupingColumn1
            AND s.GroupingColumnN = m.GroupingColumnN
            AND s.SortingColumn1 <= m.SortingColumn1
    )
FROM MyTable m
GROUP BY GroupingColumn1, GroupingColumnN
ORDER BY SortingColumnN

    That gets you the position of the items within the groups.

    You can easily convert this to capital letters using a little knowledge of the ASCII table (A = position 65, capitals are all sequential, so by incrementing the position by 64 and looking up the ASCII character for the position, you'll get A for 1, B for 2, etc) 

    Chr(MyPosition + 64)

    Of course, if the table is stored in a backend that supports window functions, this can be done more clearly, concisely, and faster. Unfortunately, Access does not support them.

    Ordering should be implemented using > and <, which makes the statement fairly long for multiple ordering conditions:

    SELECT M.[FILENAME], M.[ZONE],M.[VALUECOL],

CHR(64 +  (
        SELECT COUNT(*) 
        FROM tblTest AS S
        WHERE 
            (S.[FILENAME] = M.[FILENAME])
            AND (
                           (s.VALUECOL > m.VALUECOL)
                          OR (
                                    (s.VALUECOL = m.VALUECOL) AND (s.ZONE <= m.ZONE)
                                )
                       )
            ) ) AS LETTER
FROM tblTest AS M
GROUP BY M.[FILENAME], M.[ZONE], M.[VALUECOL]
ORDER BY M.[FILENAME] ASC, M.[VALUECOL] DESC,M.[ZONE] ASC