Adding consecutive no from append query (1 Viewer)

[xaysana]

Hi there,
Is there a way to add a consecutive no to a ID field in table?

I have a database contains a table named tblemployees. I have this link to a form which i use this form for editing and deleting records.

When record(s) was deleted in PK field shows missing numbers eg. 123...6...9.. i do not like database showing missing numbers.

What i prefer is to set an autonumber field and apply this code:

If ID=0 or Isnull(ID) then
ID= Dmax("[ID]", "tblemployees")+1

so that everytime when record was deleted it still creates a consecutive no from the last number.

With above code works fine from form button. Since i use append query for adding new info into tblemployees.

Is there any workaround this please?
Here is my SQL code:

INSERT INTO tblemployees ( ID, Firstname, Familyname )
SELECT tempwholesales.ID, tempwholesales.Firstname, tempwholesales.Familyname
FROM tempemployees;

Any help would appriciate

 

[vbaInet]

Don't try to refactor your numbers. It's not recommended neither is it sensible.

 

[xaysana]

Don't try to refactor your numbers. It's not recommended neither is it sensible.

Thank you for your advice. But I really like to do this. What i create one more column called ID2 (number) and add consecutive number from there by using append query is it possible?
Thank you in advance.
Regards,

 

[vbaInet]

And what would happen if:

1. You have 100,000 records?
2. You saw a piece of paper containing ONLY the EmployeeID and you wanted to know who it belonged to? How do you know if that is a deleted employee or a current employee?

 

[xaysana]

And what would happen if:

1. You have 100,000 records?
2. You saw a piece of paper containing ONLY the EmployeeID and you wanted to know who it belonged to? How do you know if that is a deleted employee or a current employee?

Thank you vbaInet.
That's good point.
In fact, I like to apply a proven approach to a wholesaler sales table which has different columns. when user does wrong data entry, he has right access to delete. at same time, once new record is added a consecutive no should be used. The sample given was only a trial.

please advise

Thank you
Regards,

 

[vbaInet]

If the user creates a new record a number is generated. If the user decides to delete this new record because he or she entered it in error, it can be done before the record is saved.

Now if the record gets deleted before the record gets saved, the number will continue from the last increment and I believe this is not what you want.

So in order to handle this you will have to use the Number data type and perform the increment using the DMax() function.

 

[xaysana]

If the user creates a new record a number is generated. If the user decides to delete this new record because he or she entered it in error, it can be done before the record is saved.

Now if the record gets deleted before the record gets saved, the number will continue from the last increment and I believe this is not what you want.

So in order to handle this you will have to use the Number data type and perform the increment using the DMax() function.

This is something not easy to work out. I can simply create increment number by using Dmax() function. Since my case is that i need to add multiple increment numbers at the time depends on append query. I think i will leave it as what it is.

Thank you so much for your valueable time and advice.
Regards,

 

[vbaInet]

Yea, that's the way to do it.

Happy to help!

 

[gemma-the-husky]

I am not sure if this has already been said, but if you want an intact sequence, you need to disallow deletions, and use a deleted flag for the deleted items.

the trouble is, if you infill the deleted slots, you aren't solving anything. OK, you may now HAVE an intact sequence, but it is meaningless, if you cannot pinpoint why the infill was required. And furthermore the dates of the infilled items (if you use them) will be out of sequence anyway.