Numbering the Records of a Query

  • Follow


Hi folks,
I'm using the code module below to create a extra column which will number,
in serial order, the records produced by my query. The problem is that when
it creates the number list and the list reaches a record in the reference
field which is repeated,  it repeats the number used when the record was
first encountered, like shown below. How can I edit the code or correct the
issue so that number continue in serial order '1 thru 6'.

ConstMon     No.
Jan	1
Feb	2
Mar	3
Apr	4
Feb	2
Mar	3
Jan	1

Function Serialize(qryname As String, keyname As String, keyvalue) As Long
On Error GoTo Err_Serialize
     Dim db As Database
Dim rs As DAO.Recordset
      Set db = CurrentDb
    Set rs = db.OpenRecordset(qryname, dbOpenDynaset, dbReadOnly)
     rs.FindFirst Application.BuildCriteria(keyname, rs.Fields(keyname).Type,
keyvalue)
     Serialize = Nz(rs.AbsolutePosition, -1) + 1
Err_Serialize:
    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Function

SQL:
SELECT QryDiscChart7.ConstMon, Serialize("QryDiscChart12","ConstMon",
[ConstMon]) AS Expr1
FROM QryDiscChart7;

Thanks in advance.
Dave

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/200709/1

0
Reply dp724 9/12/2007 1:09:57 PM

dp724 via AccessMonster.com wrote:
>I'm using the code module below to create a extra column which will number,
>in serial order, the records produced by my query. The problem is that when
>it creates the number list and the list reaches a record in the reference
>field which is repeated,  it repeats the number used when the record was
>first encountered, like shown below. How can I edit the code or correct the
>issue so that number continue in serial order '1 thru 6'.
>
>ConstMon     No.
>Jan	1
>Feb	2
>Mar	3
>Apr	4
>Feb	2
>Mar	3
>Jan	1
>
>Function Serialize(qryname As String, keyname As String, keyvalue) As Long
>On Error GoTo Err_Serialize
>     Dim db As Database
>Dim rs As DAO.Recordset
>      Set db = CurrentDb
>    Set rs = db.OpenRecordset(qryname, dbOpenDynaset, dbReadOnly)
>     rs.FindFirst Application.BuildCriteria(keyname, rs.Fields(keyname).Type,
>keyvalue)
>     Serialize = Nz(rs.AbsolutePosition, -1) + 1
>Err_Serialize:
>    rs.Close
>    Set rs = Nothing
>    Set db = Nothing
>End Function
>
>SQL:
>SELECT QryDiscChart7.ConstMon, Serialize("QryDiscChart12","ConstMon",
>[ConstMon]) AS Expr1
>FROM QryDiscChart7;


There is no way to determine the order of records in a
table.

That means that the only way to "serialize" records is when
the table contains one or more fields that can be used to
provide a **unique** sort of the records.

If there are such fields, then you can use a subquery to
calculate each record's position in the sorted list.


-- 
Marsh
MVP [MS Access]
0
Reply Marshall 9/13/2007 4:57:54 PM


Thank you! That was the approach taken to resolve the issue. Changed
'ConstMon' in the SQL to the name of the field which provides unique
numbering for those records and got the extra column which provides a
sequential numbered list starting with the number 1.

Marshall Barton wrote:
>>I'm using the code module below to create a extra column which will number,
>>in serial order, the records produced by my query. The problem is that when
>[quoted text clipped - 31 lines]
>>[ConstMon]) AS Expr1
>>FROM QryDiscChart7;
>
>There is no way to determine the order of records in a
>table.
>
>That means that the only way to "serialize" records is when
>the table contains one or more fields that can be used to
>provide a **unique** sort of the records.
>
>If there are such fields, then you can use a subquery to
>calculate each record's position in the sorted list.
>

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/200709/1

0
Reply dp724 9/14/2007 12:41:30 PM

2 Replies
779 Views

(page loaded in 2.834 seconds)


Reply: