autonumbering without using autonumber

[madrappin]

I have a field that lists every record with a number as the primary key.
Example 1:

ID Record
1 1
2 2
3 3

How would I add a new record in there without overwriting anything and making the old records just drop down a number?
Example 2:

ID Record
1 1
2 New record
3 2
4 3

So basically I'm just trying to add in a new record in the middle and have the ID value of every record below it to automatically drop down. Can anyone help? thanks

 

[MoLaker]

I have a question. How does one know where to insert the new record? If its location is based on ordering (sorting) another field(s) such as ordering on lastname, firstname - or whatever, you could insert the new record with the next higher ID. Then update all ID fields by sequentially renumbering the records in the order of the sort field - BUT, you have to determine the recordcount for a starting point and update the records in descending order to keep from encountering "duplicate key" error.

There are other ways, but why do you need to do this?" How are you using the ID so that it requires you to insert records in the middle, so to speak? That is not very good database procedure.

 

[willir]

Folks

Autonumber is used by Access to uniquely reference the record. Any apparent relationship to referring to a sequence of order in the table is purely cosmetic.

This may seem to be disappointing to new users to access, but the autonumber should not have any other function.

Why?
- You will loose numbers in the sequence. Try this. Start to create a record in a form, but don't commit / write the record. Take note of the autonumber. Now go to another workstation, and start to create another record on the same form. The numbers may be the same. Hmmm, what would happen if both records were committed at about the same time??

http://www.tek-tips.com/viewthread.cfm?SQID=864289&SPID=700&page=1

- You will loose numbers in the sequence. Hit the ESCape key part way through creating a record, and try entering another number.
- You delete a record. Does this mean you have renumber the entire table??

Richard

 

[madrappin]

The ID is not an autonumber field, I just need it to act almost like an autonumber field. The numbers are important to the database though it has to start at 1. So if I have 1300 records, it numbers them 1 to 1300. Then if I want to add one on line 1200. I can just type 1200 in a new record, the database sorts that record in and drops every other line down 1. Sorry if this is kind of hard for me to explain or a little different, but I need to find a way to do this without exporting it excel everytime. Thanks.

 

[lespaul]

I just need it to act almost like an autonumber field

Then you are misunderstanding how an autonumber in Access works.

If you have a blank database and add 1000 records you will have records from 1 to 1000. But then you realize that the last 500 records you entered are bad and you delete them. Now you have records numbered from 1 - 500. The next record you enter in the database will be 1001.

To get records in a particular order in a database you use the ORDER BY clause in a query. The record order in the table shouldn't be relevant.

Leslie

 

[MoLaker]

Sorry back, but I'm having a harder time understanding why you must insert a record into the "middle". I understand what you are trying to do, but don't understand why. How does one know where to insert it? If you can explain this, perhaps the answer to your problem is simpler than it appears.

But, if you insist on doing it that way then perhaps this will work for you...

1. Open a recordset in descending order of the ID
2. Step through the records in rs.Edit mode and update each ID beginning with "to be" highest ID (recordcount + 1), incrementing by minus one.
3. Stop the renumbering after you renumber the current record with the ID where the new record goes. (for x = rs.recordcount + 1 to targetID should do it)
4. Insert the new record. (rs.AddNew)

 

[madrappin]

I'm sorry I'm giving everyone the wrong idea. I dont mean autonumber, I mean automatically renumber I guess. For example, in excel you can use expressions and drag the field down to automatically renumber everything. But I need to do this in access to keep the data intact.

 

[lespaul]

Again, why? If you would explain to us why it's important that this information be in this order, maybe we can help you come up with something that will work for you. But what you have been describing is something that DOESN'T MATTER in database design. To us it makes no difference if the record is at the beginning of a table or the end of a table. As long as each record has a unique identifier, I can find it and order the information any way I want it.

To keep the data intact, I use Primary Keys. I have a table:

JurorMain - it has a PK of JURNUM. For Jane Doe that number is 15006. In the table that I keep track of hours worked I have:

TblJurorHours
JurNum
ServeDate
TimeIN
TimeOUT

any time I need to get information about Jane Doe's hours I search tblJurorHours for 15006. The data is intact and I really don't care how many time entries she has or what order they are in the table. If I want them in order of date worked:

SELECT * FROM tblJurorHours where JURNUM = 15006 ORDER BY ServeDate


If I need her name in the list as well:

SELECT JurorFName, JurorLName, tblJurorHours.* FROM tblJurorHours INNER JOIN tblJurorMain on tblJurorMain.Jurnum = tblJurorHours.Jurnum
ORDER BY ServeDate

Again, it doesn't matter where in the table the record is.

So, if you will explain a little better what you are trying to accomplish, perhaps we can come up with an easier solution than renumbering your entire table every time you need to add something.



Leslie

 

[madrappin]

Sorry for my poor explanations, but what you described is a pretty good example of my database. The only difference is my PK is LN_NUM and it has to be numbered 1 - 1400. It still is unique to each record. Maybe what I am trying to do is just not possible in Access. But I can't change the way they assign these numbers, because I dont assign them. If you dont think what I'm trying to do is possible let me know.

The way my table is now is if I delete the second record in my table right now I end up with 1,3,4,5... I want it to automatically renumber everything back to 1,2,3,4,5...
Of if I want to switch or what have you.

I understand this isn't the best database design, but I am just trying to work with the way I'm told to make this database. thanks

jordan

 

[PHV]

I hope for you that this table has NO relationship with any other table on LN_NUM, in other words that NO ONE table rely on LN_NUM.
The downward browsing recordset solution gave to you should works.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[madrappin]

There are no relationships setup at all based on LN_NUM

 

[lespaul]

again, why does it need to be in order in the table?

So your table has:

1
16
32
4
17
99
250
1262

When you write your query you say:

ORDER BY LN_NUM

then your result set will display:

1
4
16
17
32
99
250
1262

that's in order!!!

leslie

 

[madrappin]

Sort of, the problem wasn't putting the table in order it was inserting a number in the middle without having to go line by line and change numbers. here is how my table looks:

1
2
3
4
5
6
7
8
..until..
1400

I ended up just adding a new record with the LN_NUM that I wanted and then cutting and pasting from excel my own numbers. Again, I'm really not sure how I can explain the purpose of these numbers to anyone. They dont serve any purpose as far as the design of the database is concerned. They are just numbers that are unique to each record. Thanks all

 

[willir]

madrappin

"Inserting" a record in the middle of your sequence is not the way Access handles new records.

I believe the Jet engine inserts records at the end of the record set regardless of autnumber, LINE_NUM or whatever.

What controls the output are the SQL verbs ORDER BY, GROUPY BY, which will work best with indexes.

When you look at the mechanics...
- Autonumber is just a convienent way Access identifies a record.
- Other ways of assigning a primary key, or key fields are "data" until used by the system in a meaningful way.
- Access does use indexes for speeding up searches and record retrieval.
- How you create a record does not allow you too much in the way of control of where the record is placed. This is controlled by the Jet engine.
- As per the rules of "normalization", third form, calculated numbers are generally not stored on the table since they can be calculated at enytme. For example SELECT COUNT(*)...
- Manipulation of the SELECT clause and supporting verbs are a very powerful tool for retrieving information from the data. As end users, developers and programmers of Access, we are generally more concerned about retrieving information and let the Jet engine "do it's thing".

 

[MichaelRed]

The general consensus is quite correct. The process you are describing is quite foregin to relational database design. It is also correct that you intrinsically have no control over the actual ordering of the record within hte table.

All of that being said, and whole heartly agrrred to makes little difference if the ID for the record is assigned from out of your purview. The interesting question is much more closely aligned with lespaul's issue, for me, it is more like how do (the great nebolous) "THEY" who asign these values know what that assignment is, and as importantly, how it affects the other records?

It would appear that the assignment must have some meaning for "THEM", as it appears to have noe for the db itself, so they must use it somehow, and therefore must understand that it 'reindexes' the values in the data to some degree on each occurance of adding a record.

Personally, if I were employed to do this operation, my first task would be to understand the process a bit more so as to be able to discuss what is happening and the consequences of the manual index alteration on a frequent basis.

On the other hand, a simple update query would appear to do the actual work, just make the field Increment the field [LN_NUM] for all vaLues OF [LN_NUM] >= to the new record value, or decrement them for deletions. Seems rather trivial from here.





MichaelRed
mlred@verizon.net

 

[madrappin]

Ahh, the update query works. Thanks Red.