Autonumbering

Hi,
I am not experienced user, but...

I wish to create through a query an autonumber column in a table, e.g. if I
add an autonumber field to report I can see report with field that are
numbered from 1-the last record, but only on printed page - I cannot store
this information.

Now I have a problem: I dont know how can I do this in a query.
1. I wish sort, group filter records from more tables.
2. I wish add to these sorted, grouped and filtered records an autonumber
column, that each such record has an ID from 1-the last record.
3. I wish that after each sorting, grouping and filtering above tables
thorugh my query the ID represent the records from 1-the last record in
accordance to the query rule.
Thanks in advance.

Why? Just sort them in the same manner if you ever need them represented in
that order again. Why clutter up the data?

Rick B


"Andrej" wrote in message
...
Hi,
I am not experienced user, but...

I wish to create through a query an autonumber column in a table, e.g. if
I
add an autonumber field to report I can see report with field that are
numbered from 1-the last record, but only on printed page - I cannot store
this information.

Now I have a problem: I dont know how can I do this in a query.
1. I wish sort, group filter records from more tables.
2. I wish add to these sorted, grouped and filtered records an autonumber
column, that each such record has an ID from 1-the last record.
3. I wish that after each sorting, grouping and filtering above tables
thorugh my query the ID represent the records from 1-the last record in
accordance to the query rule.
Thanks in advance.

P.S.:
I am in that stage of solution, that I made an append query and..
1. I delete all the records in the "new" table.
2. I append the records to the "new" table -but the ID is starting from the
/old/ ID+1 (max. ID before deleting records from "new" table)

"Andrej" wrote:

Hi,
I am not experienced user, but...

I wish to create through a query an autonumber column in a table, e.g. if I
add an autonumber field to report I can see report with field that are
numbered from 1-the last record, but only on printed page - I cannot store
this information.

Now I have a problem: I dont know how can I do this in a query.
1. I wish sort, group filter records from more tables.
2. I wish add to these sorted, grouped and filtered records an autonumber
column, that each such record has an ID from 1-the last record.
3. I wish that after each sorting, grouping and filtering above tables
thorugh my query the ID represent the records from 1-the last record in
accordance to the query rule.
Thanks in advance.

Rick B,
please to apologize. I do not search for why? but for how?

I simply use the "new" table in other queries and reports and for these I
simply need this autonumber ID in accordance to the previous sort, filter,
group.
In the second step is this simply used.
As I wrote I can made this on a report, but I need others reports with other
sorting and filtering in which I use the ID from the "new" table.

And now, can you say to me how to solute it?

Thanks in advance.

"Rick B" wrote:

Why? Just sort them in the same manner if you ever need them represented in
that order again. Why clutter up the data?

Rick B


"Andrej" wrote in message
...
Hi,
I am not experienced user, but...

I wish to create through a query an autonumber column in a table, e.g. if
I
add an autonumber field to report I can see report with field that are
numbered from 1-the last record, but only on printed page - I cannot store
this information.

Now I have a problem: I dont know how can I do this in a query.
1. I wish sort, group filter records from more tables.
2. I wish add to these sorted, grouped and filtered records an autonumber
column, that each such record has an ID from 1-the last record.
3. I wish that after each sorting, grouping and filtering above tables
thorugh my query the ID represent the records from 1-the last record in
accordance to the query rule.
Thanks in advance.

My answer like "not why" and simply why" was not so polite.

Mayby you know how to solve this without creating the "new" table. And if
no, than how to start in such "new" table ID from 1.

"Andrej" wrote:

Rick B,
please to apologize. I do not search for why? but for how?

I simply use the "new" table in other queries and reports and for these I
simply need this autonumber ID in accordance to the previous sort, filter,
group.
In the second step is this simply used.
As I wrote I can made this on a report, but I need others reports with other
sorting and filtering in which I use the ID from the "new" table.

And now, can you say to me how to solute it?

Thanks in advance.

"Rick B" wrote:

Why? Just sort them in the same manner if you ever need them represented in
that order again. Why clutter up the data?

Rick B


"Andrej" wrote in message
...
Hi,
I am not experienced user, but...

I wish to create through a query an autonumber column in a table, e.g. if
I
add an autonumber field to report I can see report with field that are
numbered from 1-the last record, but only on printed page - I cannot store
this information.

Now I have a problem: I dont know how can I do this in a query.
1. I wish sort, group filter records from more tables.
2. I wish add to these sorted, grouped and filtered records an autonumber
column, that each such record has an ID from 1-the last record.
3. I wish that after each sorting, grouping and filtering above tables
thorugh my query the ID represent the records from 1-the last record in
accordance to the query rule.
Thanks in advance.

Andrej,

If you compact the database (see under the Tools|Database Utilities
menu) after deleting all the records from the table, the seed for the
Autonumber field will be reset to 1.

--
Steve Schapel, Microsoft Access MVP


Andrej wrote:
P.S.:
I am in that stage of solution, that I made an append query and..
1. I delete all the records in the "new" table.
2. I append the records to the "new" table -but the ID is starting from the
/old/ ID+1 (max. ID before deleting records from "new" table)

Yes,
thanks after intensive searching I have found this solution too, but

it is possible not to close the database? /compact, and than open again/

Simply it is possible for user push the button on a form which will fill
this table?
Maybe it is possible to delete the "new" table with a command and than
create a "new" table?

Thanks,
"Steve Schapel" wrote:

Andrej,

If you compact the database (see under the Tools|Database Utilities
menu) after deleting all the records from the table, the seed for the
Autonumber field will be reset to 1.

--
Steve Schapel, Microsoft Access MVP


Andrej wrote:
P.S.:
I am in that stage of solution, that I made an append query and..
1. I delete all the records in the "new" table.
2. I append the records to the "new" table -but the ID is starting from the
/old/ ID+1 (max. ID before deleting records from "new" table)

Andrej,

It is not possible to compact the database without either closing it, or
manually using the option from the Tools menu.

Why is it important for the Autonumber to start at 1?

--
Steve Schapel, Microsoft Access MVP


Andrej wrote:
Yes,
thanks after intensive searching I have found this solution too, but

it is possible not to close the database? /compact, and than open again/

Simply it is possible for user push the button on a form which will fill
this table?
Maybe it is possible to delete the "new" table with a command and than
create a "new" table?

Thanks,

I am trying to build a database on which we can build a catalogue.

E.g. I have many items in a database. And now I sort, filter items and give
them an order /1-last item/, than I sort these ordered items /1-last item/
with others rules and give them another order. E.g. order surname of items
owners with list of item for each owner.
First:===============
No.*IT*OW
1*ka*A
2*kc*B
3*ke*C
4*kg*A
5*kr*C
6*kt*A
7*kw*A
Second:=============
OW*No
A*1,4,6,7
B*2
C*3,5
==================
In the resulting catalogue are both of these listings. And such listing
cannot begin at N1.

"Steve Schapel" wrote:

Andrej,

It is not possible to compact the database without either closing it, or
manually using the option from the Tools menu.

Why is it important for the Autonumber to start at 1?

--
Steve Schapel, Microsoft Access MVP


Andrej wrote:
Yes,
thanks after intensive searching I have found this solution too, but

it is possible not to close the database? /compact, and than open again/

Simply it is possible for user push the button on a form which will fill
this table?
Maybe it is possible to delete the "new" table with a command and than
create a "new" table?

Thanks,

Andrej

One option would be to use a Number data type instead of an Autonumber,
and use looping code to append the records so that you can ensure it
always starts at 1 and increments.

Another option is to do this in a linked table in a separate .mdb, in
which case you would be able to use DbEngine.CompactDatabase code to
compact the remote file between the delete and the append, thereby
re-setting the Autonumber to 1.

Another option is to keep the AutoNumber field to create your
incrementing numbers, but don't worry about the starting value, and then
put another field in the table for the Item Number, and you can then use
an Update Query to set the value of the Item Number to (AutoNumber value
- Min(Autonumber value) + 1)

--
Steve Schapel, Microsoft Access MVP


Andrej wrote:
I am trying to build a database on which we can build a catalogue.

E.g. I have many items in a database. And now I sort, filter items and give
them an order /1-last item/, than I sort these ordered items /1-last item/
with others rules and give them another order. E.g. order surname of items
owners with list of item for each owner.
First:===============
No.*IT*OW
1*ka*A
2*kc*B
3*ke*C
4*kg*A
5*kr*C
6*kt*A
7*kw*A
Second:=============
OW*No
A*1,4,6,7
B*2
C*3,5
==================
In the resulting catalogue are both of these listings. And such listing
cannot begin at N1.