Autonumbering

Steve,
I made it so:

I made an make table query with all my needed sort/filter and than an query
without table with this SQL statement
alter table EndingStory add column IDD autoincrement(3)

and this append to my "new" table EndingStory my needed autoincrement.

I have only one little and one big problem.
Little: Each creation of this table (EndingStory) I need agree to delete the
old table (EndingStory). i dont know how to delete table with a command.
Big:
I dont know how to collect /sort,filter/ the data from
======================
IDD*ITem*OWner
1*ka*A
2*kc*B
3*ke*C
4*kg*A
5*kr*C
6*kt*A
7*kw*A
to this table
=============
OWner*IDD
A*1,4,6,7
B*2
C*3,5
==================
because I cannot sum the IDDs. I wish only to create in one list of owners a
sublist of owner items.

Thanks,
Andrej

"Steve Schapel" wrote:

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.

The query problem was solved by other MVP Steve in access.query section of
this discussion group.
(now its only deleting of a table)

Thanks,



"Andrej" wrote:

Steve,
I made it so:

I made an make table query with all my needed sort/filter and than an query
without table with this SQL statement
alter table EndingStory add column IDD autoincrement(3)

and this append to my "new" table EndingStory my needed autoincrement.

I have only one little and one big problem.
Little: Each creation of this table (EndingStory) I need agree to delete the
old table (EndingStory). i dont know how to delete table with a command.
Big:
I dont know how to collect /sort,filter/ the data from
======================
IDD*ITem*OWner
1*ka*A
2*kc*B
3*ke*C
4*kg*A
5*kr*C
6*kt*A
7*kw*A
to this table
=============
OWner*IDD
A*1,4,6,7
B*2
C*3,5
==================
because I cannot sum the IDDs. I wish only to create in one list of owners a
sublist of owner items.

Thanks,
Andrej

"Steve Schapel" wrote:

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.

Andrej,

Have a look at DeleteObject macro, or DoCmd.DeleteObject method if using
VBA procedure.

However, this may not be necessary. Your Make-Table query will
overwrite the existing table of the same name.

--
Steve Schapel, Microsoft Access MVP

Andrej wrote:
The query problem was solved by other MVP Steve in access.query section of
this discussion group.
(now its only deleting of a table)

Thanks,

Andrej,

I considered this approach, and did not include it in my suggestions to
you because I don't think it will reliably give you control over the
order of the autonumbering. Any sorting you do in the maketable query
will essentially be meaningless, as the data in the table is always
un-ordered, so when you apply the ALTER TABLE the sequence is not
guaranteed to be in the order you want. Still, if it works for you,
that's good.

--
Steve Schapel, Microsoft Access MVP


Andrej wrote:
Steve,
I made it so:

I made an make table query with all my needed sort/filter and than an query
without table with this SQL statement
alter table EndingStory add column IDD autoincrement(3)

and this append to my "new" table EndingStory my needed autoincrement.

Oh,
I have not controlled if my "new" table /made with maketable query/ was
altered in other way as only with a new autonumber column. It would be
devastating if this alter command can unorder the "new" table.


"Steve Schapel" wrote:

Andrej,

I considered this approach, and did not include it in my suggestions to
you because I don't think it will reliably give you control over the
order of the autonumbering. Any sorting you do in the maketable query
will essentially be meaningless, as the data in the table is always
un-ordered, so when you apply the ALTER TABLE the sequence is not
guaranteed to be in the order you want. Still, if it works for you,
that's good.

--
Steve Schapel, Microsoft Access MVP


Andrej wrote:
Steve,
I made it so:

I made an make table query with all my needed sort/filter and than an query
without table with this SQL statement
alter table EndingStory add column IDD autoincrement(3)

and this append to my "new" table EndingStory my needed autoincrement.

Andrej

There is no such thing as "unorder" a table. The data in a table is
always unordered by definition. If the order of records is important, I
certainly would not rely on the table produced by a make-table query to
be in the required order at the point where the autonumber field is
added. In practice, it may appear to be correct most of the time, but
you can't rely on it.

--
Steve Schapel, Microsoft Access MVP

Andrej wrote:
Oh,
I have not controlled if my "new" table /made with maketable query/ was
altered in other way as only with a new autonumber column. It would be
devastating if this alter command can unorder the "new" table.