Ms Access Rank And Match Two Querys

I've been stuck with this problems for a while, and couldn't get it right yet. Here it is: I have some tables in my Acces database and some querys. I have a query to select some fi

Solution 1:

You might do this in two steps:

Step 1 -- Create a query (say, named "Table 2 with Rank") that calculates the rank for Table 2. The SQL might look something like this:

SELECT
    [Table2].[Name],
    [Table2].[Frequency],
    Count(*) AS [Rank]
FROM
    [Table2],
    [Table2] AS [Self]
WHERE
    [Self].[Frequency]>=[Table2].[Frequency]
GROUPBY
    [Table2].[Name],
    [Table2].[Frequency];

If there are "ties" in Table 2 (that is, different names with the same frequency), this query will assign the same rank to both. If you don't want this, change the WHERE clause to specify how you want to break ties. For example, in the event of a tie, the WHERE clause...

WHERE
    [Self].[Frequency]>[Table 2].[Frequency]
    OR
    ([Self].[Frequency]=[Table 2].[Frequency] AND [Self].[Name]<=[Table 2].[Name])

...will assign the lower numbered rank to the name that comes first the in the alphabet.

Step 2 -- Create another query that joins the first query to Table 1. The SQL might look something like this:

SELECT
    [Table1].[Name], 
    [Table1].[Frequency], 
    [Table2with Rank].[Frequency] AS [Frequency2], 
    [Table2with Rank].Rank AS [RankIn2]
FROM 
    [Table1] LEFTJOIN [Table2with Rank] 
        ON [Table1].[Name] = [Table2with Rank].[Name]
ORDERBY
    [Table1].[Frequency] DESC;

Solution 2:

I would use VBA because Access' limited SQL doesn't understand the rank concept very well. Here are the steps.

1. Create a 3rd field for Table2 and call it Rank.

2. Create the following subroutine by pressing Alt+F11, Insert->Module and pasting the following code in the editor window that opens.

   Public Sub RankTable()

   Dim rs As Recordset, iRank AsInteger, strQuery AsString
   
   strQuery = "SELECT * FROM Table2 ORDER BY Freq DESC"Set rs = CurrentDb.OpenRecordset(strQuery, dbOpenDynaset)
   
   rs.MoveFirst
   iRank = 1Do
   
       rs.Edit
       rs.Fields(3) = iRank
       rs.Update
   
       rs.MoveNext
       iRank = iRank + 1LoopWhileNot rs.EOF
   
   rs.Close
   

   End Sub

3. Run the above subroutine. (Remember you have to run this every time there is an update to Table2.)

4. Create the query

   SELECT Table1.Name, Table1.Frequency, Table2.Frequency AS Frequency2, Table2.Rank FROM Table1 LEFT OUTER JOIN Name ON Table1.Name = Table2.Name ORDER BY Table2.Frequency