Auto increment value in queries

[hribek25]

Hello folks,

is there any way how to create autoincrement value in query? So every line of my result of query will have values 1,2,3 etc.?

Thank you in advance.

Petr

[Rabbit]

Why do you need this?

[developing]

make a table with an autonumber field?

[hribek25]

Hi,

I need select a limited number of rows returned from query.
But this number of records is calculated from this query (I dont know this number before I run this query and this number will vary) - it is located in some field as constant.

for instance:
there is some query with one field named maxrecords = 4 and I need show 4 rows only from this query.

[hribek25]

make a table with an autonumber field?

yes it could be answer. But is there any way how to hadle it without any temp table?

[Rabbit]

There is a way, but before that, how is maxrecords calculated?

[hribek25]

it is calculated as formula of some other fields from related query. Used functions are: Round, * and -

[Rabbit]

What's the exact expression?

[hribek25]

What's the exact expression?

newfield: Round((0,3-([field01]/[field02]))*[field02];0)
this function returns integer appx. 1-30, constant for every line. And this number is number of records that I need.

SQL query: SELECT adresy_jen_nove.*, adresy_nahodne_jeden_odkazdeho3_celkovypocet.pocet CATIkontr, adresy_jen_nove_celkovypocet.celkovypocet, Round((0.3-([pocetCATIkontr]/[celkovypocet]))*[celkovypocet],0) AS dovyber_pocet
FROM adresy_jen_nove, adresy_nahodne_jeden_odkazdeho3_celkovypocet, adresy_jen_nove_celkovypocet
WHERE (((adresy_jen_nove.ID) Not In (select id from adresy_nahodne_jeden_odkazdeho3_expdoCATI)))
ORDER BY adresy_jen_nove.typ, adresy_jen_nove.nahoda;

[Rabbit]

newfield: Round((0,3-([field01]/[field02]))*[field02];0)
this function returns integer appx. 1-30, constant for every line. And this number is number of records that I need.

SQL query: SELECT adresy_jen_nove.*, adresy_nahodne_jeden_odkazdeho3_celkovypocet.pocet CATIkontr, adresy_jen_nove_celkovypocet.celkovypocet, Round((0.3-([pocetCATIkontr]/[celkovypocet]))*[celkovypocet],0) AS dovyber_pocet
FROM adresy_jen_nove, adresy_nahodne_jeden_odkazdeho3_celkovypocet, adresy_jen_nove_celkovypocet
WHERE (((adresy_jen_nove.ID) Not In (select id from adresy_nahodne_jeden_odkazdeho3_expdoCATI)))
ORDER BY adresy_jen_nove.typ, adresy_jen_nove.nahoda;

Create a new query based on the query above with this field.

DCount("*", "[Name of Above Query]", "adresy_jen_nove.typ<=" & adresy_jen_nove.typ & " AND adresy_jen_nove.nahoda<=" & adresy_jen_nove.nahoda) As Rank

However, this will give every record that has the same typ and nahoda the same Rank. If this is a problem then you may have to go the extra table route.