autonumber oops

[rudolfelizabeth]

I have a database with records with a number field representing the clientnumber.

I have a autonumber field representing the Id

I have deleted the id autonumber
and then inserted a new field to reset the id number to one
I have a client with the number one as clientnumber indexed
why is the id number different.
if the database clientnumber is indexed
the clientnumber with number one should have the id field one.

how can I achieve this.

 

[BigRed1212]

Why would you want to?

If your "clientnumber" field is unique and is the primary key, why would you want to set up an id number field to duplicate those values?

 

[rudolfelizabeth]

Cause that the correct way right now it isnot
client number one has an autonumber of 600 I find this is not correct
manualy you will do it as follows if you did not have a computer

Client number one Id no one
now I have delete client number 2

this means client number 3 must have an id no 2
and so on.
thats why you indexed it
to have dinstiction
this means that you have deleted a group of records

this means that when the file is indexed on clientnumber
not on Id all your Id numbers must follows one another this isnot the case right
now.
This doesnot mean that all yours Ids will macth the clientnumbers because when you delete a clientnummber
you won't get the same Id number.

So it isn't usefull to correct the autonumber field
you want to correct the autonumber field for the following reasons,
suppose you want to append the data to another table everytime you do this you will get a higher value,
starting at the value you stopped before.

 

[BigRed1212]

If I am understanding you correctly, you want the 617th record in the table, let's say that record is for the XYZ Company, to have an ID of 617, and if records 312 and 423 in the table are deleted, then the ID for XYZ should change to 615?

 

[JoeAtWork]

Autonumbers are designed for one thing only, to guarantee a unique number. If for some reason you need your numbers to be all sequential, use something else.

now I have delete client number 2

this means client number 3 must have an id no 2

Why? What difference does it make if you have a client # 1 and a client # 3 but no client # 2?

The only purpose for Client Number is to have a unique identifier. And once you assign a number for a person, it should never change. # 3 should always identify the same person.

It would be the same for a paper based system. If I had 100 clients and # 2 fired me, I'm not going to move all my papers from folders # 3 to 100 down one just so that they are all labelled sequentially.

 

[rudolfelizabeth]

Dear joeatwork

I quit understand you but that's why you use a computer
to get the things rignt
here is what I mean

I have a database with 8034 clients

when the these clients withdraw there money I will remove
them from the main file to the history file

the client will be deleted from the original file.
To rearrange the autonumber ounce a year I will delete the autonumber field and run it again

If is correct you should get a in indexexed database with the numbers follow one another that is not the case right now.

Microsoft access remembers when you have input the record number,
so if you have forgotten a client let say client number 3000
and you input client number 3000 as the last client
client number 3000 get the last autonumber.

 

[PHV]

Again, why rearranging an autonumber field ????

 

[rudolfelizabeth]

simple
if you have a clientnumber 1
and if you input it as the last record
it cannot have an unique number last say of 1200
in a database that is constant at 8300
the autonumber must follow the rules of logic
like it is providing an unique logic sequence autonumber in an indexed database indepentley how large the database .
I think the providers microsoft access overlooked this.

 

[PHV]

An autonumber (in ANY rdbms) should have no other meaning than to be unique, period.

 

[lespaul]

To quote our good friend DHookum, "Records in a database table are like marbles in a bag all jumbled up"....if you want the records to be in a certain order you need to order them yourself in a query result.

You are just making your life extremely difficult by trying to do this.

Leslie

Have you met Hardy Heron?

 

[JoeAtWork]

Let's put it this way, what is the advantage of having the numbers in consecutive order? You keep saying it needs to be done because it is "correct" or "logical". "Logical" means it has some purpose - what is this purpose?

Does it make the database more efficient? - NO
Does it make it faster? - NO
Does it protect the integrity of the data better? - NO

There is no benefit to what you are proposing.

However, there are several problems that can occur while you pursue this activity:
1. You may damage the integrity of your data during the update (for example, if related tables do not get "cascade" updated)
2. The data becomes inconsistent with it's previous version - for example what used to be customer # 99 now becomes customer # 18. If you have any paper printouts for customer # 99, you no longer can match that up with your database.
3. You are wasting your own time on a needless task

I think the providers microsoft access overlooked this.

I guess they must have "overlooked" this when they made SQL Server as well. And for that matter, Oracle, and pretty much every major database company that exists, since they all do it this way.

 

[randy700]

If you absolutely insist on keeping the numbers consecutive, the solution is to never delete any. Instead, add some kind of a status field to indicate if the client is still active. This, of course, means you will have to adjust any queries, reports, etc to filter out unwanted clients. The advantage here is that you can re-activate a client if it becomes necessary.


Randy

 

[rudolfelizabeth]

Here is the purpose,
I had an old Database so I deceided to build a new one I appended the the records to a new table belonging to a new
main file.

I had a transaction file I appendend the transaction to a new table transactions.

Then I ran a report of the complete database to see If the
total transaction matched the total it did not macth on the report.
when I open the form the total were correct.

I began trouble shooting the two tables
for a diffence of $ 3000.00

I discoverd that the query appended two of the same clientnumber in the main file causing that the two values are sum twice on the report it took me quit a long time to figure this out
I could not believe that a unique autonumber in a query
could be duplicated.
The query was design no duplications allowed.
the main table had twice the same unique number, in the old the mainfile there were no duplications. how can this be possible.

 

[MichaelRed]

To doggedly repeat the good advice from above ...

Autonumber should ONLY be used to guarantee a unique record, not as an identifier related to anything (except, perhaps, a record in another table).

Maintaining (changing) the autonumber/ Identity NEVER done in a relational database. This is a 'self defeating' exercise.

less repetitive:
You appear to not be aware of / following normalization rules, please search these fora / google for them.

Achieve at least the third normal form.

Please Please Please get some help with the concepts of database design. Any of several publishers offer books / tutorials on the subject.



MichaelRed