Numbering the Records of a Query

  • Thread starter Thread starter dp724 via AccessMonster.com
  • Start date Start date
D

dp724 via AccessMonster.com

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
 
dp724 said:
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;
Click to expand...


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.
 
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 said:
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
Click to expand...
[quoted text clipped - 31 lines]
[ConstMon]) AS Expr1
FROM QryDiscChart7;
Click to expand...

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.
Click to expand...
 
Back
Top