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 fields and order by, say, their frequency. This is done for two tables, giving me two ranks. Looks like this (and are sorted DESC, so the higher Frequency is on top):

Table 1                    Table 2
Value1   Frequency1         Value2 Frequency2

Table2.Value2 is a subset of Table1.Value1, so I want to match Value2 and Value1 plus Add a Column showing Value2 position (rank) on Table2, so I can compare it's position on Table1 and Table2 ranks.So I'll have something like:

Table3
Value1 Frequency1 Frequency2 PositionTable2

I've searched how to return the line number of a field (couldn't get it to work), and I can put the two tables together and match by Value, but can't get the result I need.

My knowledge of SQL is limited, and I don't understand VB at all, so please be nice :) Thanks in advance

Edit Example:

Table 1                    Table 2
Name    Frequency    Name    Frequency
Mary       5         Paul     2
John       4         John     1
Paul       3

Output I want:

Table 3               
Name    Frequency    Frequency2    RankIn2
Mary       5                                  //doesn't appear in table2, freq=null rankin2=null
John       4            1            2            //second line of table2
Paul       3            2            1            //first line in table2

回答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
    [Table 2].[Name],
    [Table 2].[Frequency],
    Count(*) AS [Rank]
FROM
    [Table 2],
    [Table 2] AS [Self]
WHERE
    [Self].[Frequency]>=[Table 2].[Frequency]
GROUP BY
    [Table 2].[Name],
    [Table 2].[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
    [Table 1].[Name], 
    [Table 1].[Frequency], 
    [Table 2 with Rank].[Frequency] AS [Frequency2], 
    [Table 2 with Rank].Rank AS [RankIn2]
FROM 
    [Table 1] LEFT JOIN [Table 2 with Rank] 
        ON [Table 1].[Name] = [Table 2 with Rank].[Name]
ORDER BY
    [Table 1].[Frequency] DESC;

回答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 As Integer, strQuery As String
   
   strQuery = "SELECT * FROM Table2 ORDER BY Freq DESC"
   
   Set rs = CurrentDb.OpenRecordset(strQuery, dbOpenDynaset)
   
   rs.MoveFirst
   iRank = 1
   
   Do
   
       rs.Edit
       rs.Fields(3) = iRank
       rs.Update
   
       rs.MoveNext
       iRank = iRank + 1
   
   Loop While Not 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