MS Access – how to rank with 3 variables

I am trying to rank using 3 variables from the sample data below:

+-------------+----------+-----------------+--------+
| Case Number | Doc Type | Document Number | Rank   |
+-------------+----------+-----------------+--------+
|           1 | A        |         3787104 |    1   |
|           1 | B        |         3786773 |    2   |
|           2 | A        |         3787505 |    1   |
|           3 | A        |         3787525 |    1   |
|           3 | B        |         3786892 |    3   |
|           3 | A        |         3786891 |    2   |
|           4 | B        |         3787514 |    3   |
|           4 | B        |         3787513 |    2   |
|           4 | A        |         3787512 |    1   |
|           5 | A        |         3786436 |    6   |
|           5 | A        |         3786499 |    5   |
|           5 | A        |         3786456 |    4   |
|           5 | A        |         3786371 |    3   |
|           5 | A        |         3786481 |    2   |
|           6 | A        |         3786815 |    1   |
|           7 | B        |         3786998 |    3   |
|           7 | B        |         3786989 |    2   |
|           7 | A        |         3786988 |    1   |
|           8 | B        |         3787033 |    4   |
|           8 | A        |         3787078 |    2   |
|           8 | B        |         3787037 |    3   |
|           8 | A        |         3787074 |    1   |
+-------------+----------+-----------------+--------+

First Rank by Group, Then by Doc Type, Then by Document Number. Here’s what I currently have after looking through some of the entries here:

SELECT [Case Number], [Doc Type], [Document Number], (
  SELECT  COUNT(*)  from [Document Tracker] AS tbl2 WHERE
(
   tbl1.[Doc Type] < tbl2.[Doc Type] AND
   tbl1.[Case Number] = tbl2.[Case Number] 
)
)
+1 AS Rank
FROM [Document Tracker] AS tbl1
ORDER BY [Case Number], [Doc Type] DESC , [Document Number];

This gives me this output:

+-------------+----------+-----------------+--------+
| Case Number | Doc Type | Document Number | Rank   |
+-------------+----------+-----------------+--------+
|           1 | A        |         3787104 |    1   |
|           1 | B        |         3786773 |    2   |
|           2 | A        |         3787505 |    1   |
|           3 | A        |         3787525 |    1   |
|           3 | B        |         3786892 |    3   |
|           3 | A        |         3786891 |    1   |
|           4 | B        |         3787514 |    3   |
|           4 | B        |         3787513 |    3   |
|           4 | A        |         3787512 |    1   |
|           5 | A        |         3786436 |    1   |
|           5 | A        |         3786499 |    1   |
|           5 | A        |         3786456 |    1   |
|           5 | A        |         3786371 |    1   |
|           5 | A        |         3786481 |    1   |
|           6 | A        |         3786815 |    1   |
|           7 | B        |         3786998 |    2   |
|           7 | B        |         3786989 |    2   |
|           7 | A        |         3786988 |    1   |
|           8 | B        |         3787033 |    3   |
|           8 | A        |         3787078 |    1   |
|           8 | B        |         3787037 |    3   |
|           8 | A        |         3787074 |    1   |
+-------------+----------+-----------------+--------+

I can’t figure out the missing code to have it doc numbers ranked as well. I appreciate any help on this.

Answer

You want to number the rows per [Case Number] ordered by [Doc Type] and [Document Number]. For this to happen, for each row you count the rows before it. These rows must have the same [Case Number], but either the [Doc Type] is lesser or the [Doc Type] is the same and the [Document Number] is lesser.

SELECT [Case Number], [Doc Type], [Document Number], 
  (
    SELECT  COUNT(*)
    FROM [Document Tracker] AS tbl2 
    WHERE tbl1.[Case Number] = tbl2.[Case Number]
    AND 
    (
      tbl1.[Doc Type] < tbl2.[Doc Type]
      OR
      (
        tbl1.[Doc Type] = tbl2.[Doc Type]
        AND
        tbl1.[Document Number] < tbl2.[Document Number]
      )
    )
  ) +1 AS Rank
FROM [Document Tracker] AS tbl1
ORDER BY [Case Number], [Doc Type] DESC , [Document Number];