INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Getting row numbers in a query having repeated values

Getting row numbers in a query having repeated values

(OP)
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.


CODE

Tbl1                         Qry2                             Tbl3

ID	TheVal               ID	  TheVal  ID_Rank             ID   TheVal  ID_RowNum
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.


CODE

Qry2

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!

RE: Getting row numbers in a query having repeated values

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

RE: Getting row numbers in a query having repeated values

(OP)
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

RE: Getting row numbers in a query having repeated values

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.

CODE -->

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 

RE: Getting row numbers in a query having repeated values

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

Teach314

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close