Table Order Conundrum

[Geezer]

Table containing ID field on following format:

GEF001
GEF002
.
.
.
GEF800

I'm wanting to add a new autonumber field to the table but want the table to be ordered by the existing ID field before adding this new field. Right clicking on the field in Datasheet view, order the ID field in Ascending order, save and go to the design view. Open back into design view, everything looks good, still ordered. Back to design view, and add an autonumber field. Save and return to datasheet view, it's no longer ordered and the autonumbers don't match up with the older ID field....

Any clue as to why?

BTW, this kinda relates to my other questin in the Tables forum.

 

[Geezer]

It gets even odder

When I right click on the table in design view and open the properties and select the Id field to order on and save, everthing is fine in datasheet view, as one would expect. The minute I add an autonumber field the Order By in the properties becomes null, i.e. the field previously there (ID) disappears...how bizarre!

 

[georgedwilkinson]

You're running around in circles. You put your order by clause in your query or form's query. You shouldn't be opening tables for direct manipulation.

 

[The_Doc_Man]

Tables have no particular order. The first time you update, insert, or delete a record in a table, you probably change its physical order. Access won't change a table's order - but if you use a query, it will present the recordset in a different order than appears in the table. It won't alter the underlying table, though. After all, if you have a crazy enough table and a half-a-dozen different order-by queries on different fields, only one of them would ever possibly be right if you re-ordered the table. But Access solves this problem another way. Tables have no order. Queries with ORDER BY clauses have order.

So, the moral of this story is, if you want it in a particular order, assure that a field exists to provide the information needed to assert that order in a query.

 

[Geezer]

Tell me about it

Not entireley sure how I go about adding a new autonumber field with a specific format in a query. Any pointers?

Thx

 

[georgedwilkinson]

Open the query editor and select the table and all of it's fields. Go to the field you want to order by, and click on the little button to say which order (ascending or descending). Save the query.

 

[Geezer]

Have no problems with running general queries

Open the query editor and select the table and all of it's fields. Go to the field you want to order by, and click on the little button to say which order (ascending or descending). Save the query.

Not a problem with running a query. However, I have no idea how to add an autonumber field to my table (once it's sorted) via a query.

Here is the underlying issue. I already have a table with an older ID field which is a simple text field with ID's GEF001, GEF002...GEF886. I've created a form with and would ideally like an auto ID field. I'm unable to convert the existing ID field to a autonumber field, Access won't let me. To add to this there are various records which have been deleted along the way, hence there might not be a GEF125, etc. So, I've created dummy entries to make up for these missing values. The idea was to order my existing table in Ascending order by ID (with dummy entries), GEF001 thru GEF886. Then I was going to add an autonumber field of the format "GEF"0000. Then I'd go along and delete the dummy entries. This way the old IDs should match the new autonumber IDs. But, we know the rest....

Once sorted I can add the autonumber field to the form and not have to worry about user input errors.

Hope that makes sense?

If you have a way around this I'd really appreciate it.

Thx

 

[boblarson]

Re: Have no problems with running general queries

Not a problem with running a query. However, I have no idea how to add an autonumber field to my table (once it's sorted) via a query.

Here is the underlying issue. I already have a table with an older ID field which is a simple text field with ID's GEF001, GEF002...GEF886. I've created a form with and would ideally like an auto ID field. I'm unable to convert the existing ID field to a autonumber field, Access won't let me. To add to this there are various records which have been deleted along the way, hence there might not be a GEF125, etc. So, I've created dummy entries to make up for these missing values. The idea was to order my existing table in Ascending order by ID (with dummy entries), GEF001 thru GEF886. Then I was going to add an autonumber field of the format "GEF"0000. Then I'd go along and delete the dummy entries. This way the old IDs should match the new autonumber IDs. But, we know the rest....

Once sorted I can add the autonumber field to the form and not have to worry about user input errors.

Hope that makes sense?

If you have a way around this I'd really appreciate it.

Thx

I believe that an autonumber is NOT what you need. What you should have is an autonumber primary key, but the field that is GEF0000 should not be reliant on an autonumber. It should be based on your own creation. And, you should store the numeric part separately from the alpha part.

 

[boblarson]

I also refer you to this:
http://www.access-programmers.co.uk/forums/showthread.php?t=128935

 

[Pat Hartman]

To make use of your existing sequence numbers, you will need to separate the text part of the field from the numeric part. Then create a new empty table with an autonumber PK and the text part of your old PK as a separate field. The final step is an append query that appends your old data to the new table. As long as there are no duplicates, all of your old sequence numbers will become the new autonumber values. There is no need to dummy up missing values. Going forward, expect to see occassional gaps in the autonumber values.