Solved Help please. I can't create an autonumber field in the order I want. (1 Viewer)

[MikeT1941]

Hello everyone.
I created a chart of accounts (attached) and then found I had to add a few blank heading titles to get the displayed P&L to show correctly.
Following what I had picked up, I decided to create a new autonumber field to replace the existing one. After removing the old primary key autonumber field I saved the table and then added a new field, having previously sorted the table on CategoryID and saved it. CategoryID is used for sorting purposes in the P&L by grouping on 1, 2, 3, and 4 letters in the report. However, the table re-emerged with the added records (3) still stuck at the bottom and out of order. Exported into a new database and tried again- the same. Can anyone please tell me what I am doing wrong so I can move on! Thanks in advance.

Mike

 

[Gasman]

There is no inherent order in a table?
You order it as you need.?

I've just sorted the table by CategoryID as you can see by the the autonumber field.?

Just use a query to get the order you want.

 

[The_Doc_Man]

First, let us be absolutely clear - ANY time you try to warp an autonumber to have specific order or purpose, you are already in trouble. The single purpose of an autonumber is to provide you with a UNIQUE record identifier that doesn't depend on any other value - INCLUDING whatever number was used in chronologically previous records.

Second, in table order, there IS no order. Tables have no order because the theory on which they are based sees them as UNORDERED sets of records. IF you use a query with an ORDER BY clause, THEN you can say that there is some order to the recordset. But you can't say that for a native table opened in datasheet view.

If there is some specific order to be imposed, you will have to "roll your own" by using some variant of the DMax+1 method.

 

[gemma-the-husky]

You need sequence fields completely separate from the autonumber.

I would have multiple fields - sort of

categoryID, hierarchy2, hierarchy3, hierarchy4 (depends on how many breaks in your structure)

so a header for categoryID No 4, would most likely have values 4,0,0,0, and no other record could have this value.

 

[MikeT1941]

Thank you GTH, you set me thinking. I'm going to keep the alphameric code called CategoryID and create an autonumber field in the query that feeds the report and in the combo boxes that set the limits of the accounts to be included (Thanks AllenBrowne) so that whatever happens to the account categories in the future, they will always be in the right order for the report. I only realised after your comment that I had created my own problem by using alphameric code as you can sort on it but not use it to set the limits of a list. Anyweay if this fails I can replace the existing code with an 8 digit number as you suggest, divided into 4 pairs giving 99 steps to each level, which is more than enough.
Thanks all again
Mike

 

[gemma-the-husky]

Anyweay if this fails I can replace the existing code with an 8 digit number as you suggest, divided into 4 pairs giving 99 steps to each level, which is more than enough.

I wouldn't use an 8 digit number. I would use a 8-character string. I don't know if that's what you meant though.

 

[Pat Hartman]

Mike, just to be clear, the autonumber should be the PK of the table and that is what is used in All relationships. The CategoryID should be renamed to CategoryNum or CategoryCD for clarity. In my world, only autonumbers are "ID"'s. Then, make a unique index for CategoryCD.

I think someone mentioned that most charts of accounts break the account code into pieces. This is conventional and a remnant of the pre-computer days when it wasn't so easy to sort stuff and pull out all the "expense" types from the various accounts.