Add autonumber field in query

  • Thread starter Thread starter Freshke via AccessMonster.com
  • Start date Start date
F

Freshke via AccessMonster.com

Hi ppls,

Maybe it's an easy question, so don't shoot me.
I want to do following thing:

I want to make a query in which I load (calculate) some fields (on which i
do calculations). No problem for that. Now it comes. I want also a create a
field that doesn't exist, namely a Autonumber field (ID). Why?

--> i've got two tables with data. First table is the reference table and
second is reference table that is updated after some calculations (new
numbers are added and so on). I want to compare the positions of people in
such way that i can say, you've gained positions or lost positions with
when we compare the reference table and new table (by comparing the ID's in
the two tables).

So conclusion:

OR i need to find out how i can add a field in the query that makes
automatic an ID (autonumber the list that is the result of the query) so
that i can test the position in another to make query.

OR you guys know a better solution for my problem.


Thanx in advance,

Freshke
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Sounds like a ranking query, if so read this article:

http://support.microsoft.com/default.aspx?scid=kb;en-us;208946

If you want to just number the rows in the result set, here's what I did
once. Created a function to return a number when run from a query:

SELECT pseudoAutoNumber(ID) As RowNo, ...
FROM ...

The function was stored in a standard module:

' Declaration section
Public fakeAutoNumber As Long
=====
Function pseudoAutoNumber(i As Variant) As Variant

pseudoAutoNumber = fakeAutoNumber
fakeAutoNumber = fakeAutoNumber + 1

End Function

Before running the query I set the fakeAutoNumber to zero, or whatever
starting number I wanted.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQnKY6YechKqOuFEgEQL8RgCgqXv8hjKusj8Ad0k6M7q3sGGRjVIAoNXx
vDkUhRWrjR0IE0ycQ6LdEPzA
=4ymw
-----END PGP SIGNATURE-----
 
Hi,

i tried your proposition, but i doesn't return what i want.

The thing you say, returns the same ID for all rows when you run the query.
When you run the query a second time, the field ID will be +1 higher but
all the rows will have the same ID.

So visualised, i did get this:

Field AutoNumber Field1
1 d
1 ssg
1 sdfs
1 df
1 s
1 hzyzh


But i need this:


Field AutoNumber Field1
1 d
2 ssg
3 sdfs
4 df
5 s
6 hzyzh


Maybe i do something wrong and if so, i apologize.

I hope you can clarify what i'm doing wrong.

Tnx
 
Sorry for this dubble adding on previous post.

If i am correct about my previous thought,
could the solution be some loop nested in the function as you do in visual
basic BOF and EOF and so on?????
 
Back
Top