Autonumbering

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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)
 
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.
 
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,

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

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?
 
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 N>1.
 
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,
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
======================to this table
=============because I cannot sum the IDDs. I wish only to create in one list of owners a
sublist of owner items.

Thanks,
Andrej
 
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,

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.
 
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.
 
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.
 
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.
 
Back
Top