auto numbering in a query

T

thread

Hi all,
I need to build a query that have in a field auto numbering and in a
certain formating
for example:
0001
0002
.......
0012
.......
is it posible?
 
K

KARL DEWEY

Queries do not have autonumbering.
Number do not have leading zeros. Text has leading zeros.
Do you want your query output records numbered?

Search on Numbering.
 
T

thread

i'm exproting the query to excel,
this is why i need the autonumering

BruceM כתב:
 
B

BruceM

I expect the link provided elsewhere in the thread will guide you in that
direction. I wondered about a report because of a specific technique for
using the running sum property of an unbound text box.

i'm exproting the query to excel,
this is why i need the autonumering

BruceM ???:
 
R

Rick Brandt

i'm exproting the query to excel,
this is why i need the autonumering

You could build a basic tabular report and export that to Excel. Reports are
processed sequentially so things like counters and running sums are trivial to
set up. Queries are processed as a set so while doing that sort of thing is
possible, it is more difficult and is very inefficient. On larger sets of data
the performance could be a real problem.
 
T

thread

so i understand it not possible to do t direcly in the query

Rick Brandt כתב:
 
T

thread

so i understand it not possible to do t direcly in the query

Rick Brandt כתב:
 
R

RoyVidar

thread said:
so i understand it not possible to do t direcly in the query

Rick Brandt כתב:
Click to expand...

If you have something unique to sort on (SortField), you could do
something like this

SELECT t.field1, t.field1, ... ,
Format(
(SELECT Count(*)
FROM yourTable s
WHERE s.SortField <= t.SortField)
) As MyRank
FROM yourTable t
ORDER BY t.SortField

though it would probably be very inefficient
 
R

RoyVidar

<[email protected]>:

Forgott the formatting, sorry

SELECT t.field1, t.field1, ... ,
Format(
(SELECT Count(*)
FROM yourTable s
WHERE s.SortField <= t.SortField),
"0000") As MyRank
FROM yourTable t
ORDER BY t.SortField
 
R

Rick Brandt

thread said:
so i understand it not possible to do t direcly in the query
Click to expand...

I don't believe my post indicated that. Only that it might be very slow if
dealing with a large data set. It is "possible" by using a sub-query IF you
have a unique field in your data that can be used for sorting.

See Roy-Vidar's post.
 
Top