469,284 Members | 2,505 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,284 developers. It's quick & easy.

Auto increment value in queries

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
Mar 29 '07 #1
9 10518
Rabbit
12,516 Expert Mod 8TB
Why do you need this?
Mar 29 '07 #2
developing
110 100+
make a table with an autonumber field?
Mar 29 '07 #3
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.
Mar 29 '07 #4
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?
Mar 29 '07 #5
Rabbit
12,516 Expert Mod 8TB
There is a way, but before that, how is maxrecords calculated?
Mar 29 '07 #6
it is calculated as formula of some other fields from related query. Used functions are: Round, * and -
Mar 30 '07 #7
Rabbit
12,516 Expert Mod 8TB
What's the exact expression?
Mar 30 '07 #8
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;
Mar 30 '07 #9
Rabbit
12,516 Expert Mod 8TB
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.
Expand|Select|Wrap|Line Numbers
  1. 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.
Mar 30 '07 #10

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Tom | last post: by
2 posts views Thread by Brian Bendtsen | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.