re numbering

D

Dean

I have a data base that keeps track of employee vacation. There are several
tables that have their employee number. I need to change these numbers, is
there an easy way to do this? The new numbers will be in order 1-400 can i
use the find/replace to go through and re number in squential order?
 
J

John Vinson

I have a data base that keeps track of employee vacation. There are several
tables that have their employee number. I need to change these numbers, is
there an easy way to do this? The new numbers will be in order 1-400 can i
use the find/replace to go through and re number in squential order?
Click to expand...

What's the datatype of the employee number field? Sequential by what -
employee lastname/firstname/birthdate, by date hired, by some other
criterion? Have you established Relationships between the employee
table and other tables using this field?

John W. Vinson[MVP]
 
D

Dean

they are text but they have numbers in them.......i want to number them
1,2,3,4,5,6,7,8,9 etc...they are currently1,10, 11 ,12, 15, 16 etc yes
they are relationships est..
 
J

Jason Rice

I do not know of an efficient way to use Find/Replace to do what you are
looking for.

However, there are several ways to do this. One way would be to copy your
data into Excel, add the numbers, and paste the values back into your table.

However, if you do not want to take the data out of Access, I would set up a
form with a command button. The form does not have to be fancy because you
will not need it long.

In the On_Click event of the button you could use a procedure like this:

Dim DB as DAO.Database
Dim rst as DAO.Recordset
Dim i as Integer

Set DB=CurrentDB
Set rst=DB.OpenRecordset(Insert a query name or SQL code to order your list
properly - See Note Below)
i=1
With rst
.MoveFirst
Do Until .EOF
.Edit
!FieldToHoldNumber=i
.Update
i=i+1
.MoveNext
Loop
End With

Note:
You must establish your recordset using some method to ensure the proper
order is established. This might be something like:

Select *
From tblEmployees
Order By SeniorityField;

HTH
Jason
 
J

Jason McAdams

I would just create a new field for an automumber and make it a required
field. It will then assign a new number to each record. At that point,
delete whatever field yu're using now as it would be unnecessary.
 
J

John Vinson

they are text but they have numbers in them.......i want to number them
1,2,3,4,5,6,7,8,9 etc...they are currently1,10, 11 ,12, 15, 16 etc yes
they are relationships est..
Click to expand...

I suspect then that there is nothing wrong with the order. If it's a
Text field, these records are in correct order as shown; sorting a
Text field sorts in alphabetical order, and the text string "19238"
sorts before the text string "2" just as the string "AXYVB" sorts
before the text string "B".

If you want these *text* values sorted in numeric order, create a
Query with a calculated field

SortKey: Val([ID])

where ID is the fieldname of this field; and sort the query ascending
by this value. Don't attempt to sort the table - tables HAVE no order,
you must use a Query to sort records in any reliable way.

John W. Vinson[MVP]
 
L

Larry Daugherty

Hi Jason,

I hope you don't do what you advised OP to do. Autonumbers are not
guaranteed to be sequential, in fact, if they climb into the higher numbers
they may go negative. Once an autonumber has issued into a field, it isn't
re-issued.

If you need a sequential number, create and manage it yourself.

The Autonumber datatype exists solely to provide uniqueness for a surrogate
key.

HTH
 
Top