adding numbering column in a query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, I am relatively new to Access and was hoping someone could help me out. I
have created a query that accumulates various columns of information from
different tables. In this query the user selects, as criteria for a "symbol"
field, a symbol of interest, for subsequent export of the accumulated
information for that specific symbol into excel. However, before export I
would like to add a column to the query (in query design view, I think) that
will contain a number for every row in the query, listed sequentially. In
other words if the query results in 20 rows of information, I would like a
column that lists 1 to 20. Is there any way to do this in the query design
view, either with an expression or otherwise?? Any help would be appreciated
 
Not exactly what you asked for, but queries are generally the record source
behind forms and reports. Those form/reports provide ways (grouping,
running count/sum, etc.) to display record/line numbering.
-Ed
 
See:
http://www.lebans.com/rownumber.htm
http://www.lebans.com/DownloadFiles/rownumber.zip is a database
containing functions for the automatic row numbering of Forms, SubForms
and Queries.

Updated Oct. 13 by Allen Browne. Includes error handling and cleaned
code.



Here's an update to the Serialize function by Peter Schroeder:

Good to hear. FWIW, here's the version I came up with today, based off
of your code and Ken's(Getz) suggestion, with a few changes:


Function Serialize(qryname As String, keyname As String, keyvalue) As
Long

Dim rs As Recordset


On Error GoTo Err_Serialize

Set rs = CurrentDb.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

End Function


Peter Schroeder

--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.


Giz said:
Hi, I am relatively new to Access and was hoping someone could help me out. I
have created a query that accumulates various columns of information from
different tables. In this query the user selects, as criteria for a "symbol"
field, a symbol of interest, for subsequent export of the accumulated
information for that specific symbol into excel. However, before export I
would like to add a column to the query (in query design view, I think) that
will contain a number for every row in the query, listed sequentially. In
other words if the query results in 20 rows of information, I would like a
column that lists 1 to 20. Is there any way to do this in the query design
view, either with an expression or otherwise?? Any help would be
Click to expand...
appreciated
 
thanks for the reply... looks like access wants me to "define" the function,
however (I received an error dialog upon running the query stating "undefined
function "serialize" in expression"), which I am not sure of how to do. any
tips??
 
Did you import the code module from my Sample MDB into your own MDB?

--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.
 
Hi, I just did import the code module. When I went to run the expression I
received in VBEditor the following error "compile error: user-defined type
not defined".
The following section of code is then highlighted, with "dbs As Database"
specifically "grayed" out:

Function Serialize(qryname As String, keyname As String, keyvalue) As Long
Dim dbs As Database
Dim rs As Recordset

Set dbs = CurrentDb

I am new to VB code as well, so I am confused on what to do here. Thanx
 
Also, now when I try to get out of the code window, and get back into access,
it seems I am caught in some sort of loop and I keep receiving the compile
error message in VB. In other words, I can not get back into access or close
the VB editor. ??
 
You must have a Reference set to DAO that is higher than any current
Reference to ADO.

--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.
 
The code is called for each row returned by your Query. You could be
there for a while!

If you can get into the Code editing window(VBIDE) then place a
breakpoint in the Serialize function. WHen the code stops there STOP the
function.

Otherwise if you cannot get into the Editor then I think it is Ctrl+Alt+
ScrollLock. This will stop the current Query from running.

--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.
 
Back
Top