auto numbering in a query

[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?

 

[Guest]

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.

 

[BruceM]

Is this to produce data that will be viewed in a report, by any chance?

 

[Franck]

Is this to produce data that will be viewed in a report, by any chance?

is that case a increment textbox will be easier

 

[BruceM]

is that case a increment textbox will be easier

My point exactly.

 

[Guest]

ACC2000: How to Simulate a Dynamic Counter in a Table or a Query to Count
Records
http://support.microsoft.com/default.aspx?scid=kb;en-us;199679

 

[thread]

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

BruceM כתב:

 

[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 ???:

 

[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.

 

[thread]

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

Rick Brandt כתב:

 

[thread]

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

Rick Brandt כתב:

 

[RoyVidar]

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

Rick Brandt כתב:

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

 

[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

 

[Rick Brandt]

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

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.

 

[thread]

can you give me some example for this?

Rick Brandt כתב: