Column with a Sequential Number created by a query

[harleyhead]

This should be an easy one, but I need a point in the right direction. I have a table with two columns. I want to create a query that would result in three columns. The first two from the table and the third be an sequence number, starting from zero and incrementing by one.

 

[nabiS2K]

What you need is a function that remains static while code is running. Nothing complicated as long as the function retains its value.

Also, I think I may have seen something like this in one of the FAQ titles. You might check out the FAQ section.

Let me know if you need help designing the function.

God Bless
Mike ;-)

 

[gol4]

This should work if you have an index and are sorting by that index

(select count(indexfieldname) from table1 as table2 where table1.indexfieldname < table1.indexfieldname )

or
dcount(&quot;indexfieldname&quot;,&quot;table1&quot;,&quot;indexfiledname <indexfieldname&quot
may work also

 

[JoeMiller]

Here's a function that I've used, paste this into a module:
[tt]
Global Cntr

Public Function QCntr(x) As Long
Cntr = Cntr + 1
QCntr = Cntr
End Function

Public Function SetToZero()
Cntr = 0
End Function
[/tt]

Now in your before you run your query, make sure that you run the SetToZero function to make Cntr = 0. In your query itself, use the QCntr function to make your RecordNumber. Make an aliased field like this:

RecordNum: Qcntr([AnyFieldNameFromTheTableHere])

Now you'll have a column named RecordNum that is sequential in your query. A word to the wise, this function works best when the query in question is NOT going to be viewed on the screen. This is because everytime you scroll up or down on the screen the function fires again incrementing the numbers giving you odd results (try it to see what I mean). If you need to show the results on the screen, make another query that selects the data from the query with the function and show the SECOND query to the user. Then you won't have the problem.

HTH Joe Miller
joe.miller@flotech.net