Getting row numbers in a query having repeated values 1

[teach314]

hello to all

This is an extension to an earlier post.

Starting with tbl1, I want to get to tbl3 (far right). In tbl3, the 'TheVal' values are listed in ASC order, and the 'ID_RowNum' numbers each row within each ID group.

I have code that does this perfectly, but it is somewhat indirect. (the complication is that THERE ARE REPEATED values of 'TheVal' within some ID groups. I'm hoping for a more direct solution.

[b]Tbl1[/b][u][/u]                         [b]Qry2[/b][u][/u]                             [b]Tbl3[/b][u][/u]

[b]ID	TheVal[/b]               [b]ID	  TheVal  ID_Rank[/b]             [b]ID   TheVal  ID_RowNum[/b]
1	34                   1	  8	  1                   1	   8	   1
1	8                    1	  12	  2                   1	   12	   2
1	12                   1	  19	  3                   1	   19	   3
1	19                   1	  19	  3                   1	   19	   4
1	19                   1	  21	  5                   1	   21	   5
1	21                   1	  34	  6                   1	   34	   6

2	18                   2	  9	  1                   2	   9	   1
2	18                   2	  18	  2                   2	   18	   2
2	77                   2	  18	  2                   2	   18	   3
2	77                   2	  18	  2                   2	   18	   4
2	9                    2	  77	  5                   2	   77	   5
2	18                   2	  77	  5                   2	   77	   6

3	12                   3	  4	  1                   3	   4	   1
3	4                    3	  12	  2                   3	   12	   2       
3	19                   3	  19	  3                   3	   19	   3
3	21                   3	  21	  4                   3	   21	   4        
3	21                   3	  21	  4                   3	   21	   5
3	34                   3	  34	  6                   3	   34	   6

Here's how I do it now...

a) From tbl1, I produce Qry2 (using code shown below...)

b) Then, I APPEND the results of Qry2 into a temp table that has an AutoNumber field, AutoID.

c) Then, I use a query very similar to qry2, but ranking each ID's records by the AutoID field, producing the ID_RowNum seen in tbl3.

[b]Qry2[/b]

SELECT 
	a.ID, 
	a.TheVal, 
	(SELECT 
		COUNT(*)
	FROM
		q2b_Uni b
	WHERE
		(a.ID = b.ID) AND 
		(a.TheVal > b.TheVal)
	) + 1 AS Rank
FROM 
	q2b_Uni AS a
ORDER BY 
	a.ID, a.TheVal;

Is there a more efficient way to go directly from tbl1 to tbl3?

Thanks in advance!

 

[dhookom]

I don't think you can apply a ranking like Tbl3 without identifying some value in the record that breaks the ties. I don't think I have created a table in the last 25 years that doesn't have a primary key. Are you suggesting Tbl1 doesn't have a unique key?

Duane
Hook'D on Access
MS Access MVP

 

[teach314]

Thanks for responding, Duane.

In simplifying the real situation to isolate the problem, I may have gone too far. Tbl1 is actually the output of a query, so it doesn't have a PK. By sending the output of this query into a table with an Autoincrement field, I was supplying the tie-breaking mechanism for the 'TheVal' field. It could be that the method I described in the original post is the best that can be done in the circumstances.

teach314

 

[MajP]

You could do it in 1 step in code. This creates the new table and numbers. However since tbl1 is an output you could go back to the source and do it in a pure sql solution.

Public Sub CreateTableWithRows()
  Dim RS As DAO.Recordset
  Dim strSql As String
  Dim oldID As Long
  Dim ID As Long
  Dim rowNum As Integer
  
  strSql = "SELECT tbl1.ID, tbl1.Val, 1 AS ID_RowNumber INTO tbl2 "
  strSql = strSql & " FROM tbl1 ORDER BY tbl1.ID, tbl1.Val"
  'create the new table
  'you may want to add code to check if tbl 2 exists and delete if it does before creating
  CurrentDb.Execute strSql
  Set RS = CurrentDb.OpenRecordset("tbl2", dbOpenDynaset)
  Do While Not RS.EOF
      ID = RS!ID
      If oldID <> ID Then
        rowNum = 0
        oldID = ID
      End If
      rowNum = rowNum + 1
      RS.Edit
        RS!ID_rowNumber = rowNum
      RS.Update
    RS.MoveNext
  Loop
  Application.RefreshDatabaseWindow
End Sub

 

[teach314]

Thanks! Instructive code. There's a few other places I can use these ideas.

Teach314