Changing Table ID/Auto Number Format

[PATRICKPBME]

I had a huge table (several fields) that I realized because of input on this forum was not a good idea and probably explained a lot of the "Access not responding" errors I was getting. So I decided to break this one table into many which I was able to do. I then started setting up a relationship between the tables, using the ID inserted by Access (Auto Number) as the primary in my main table and as the common key to connect to the rest. All went well until I found that the ID in one table had been designated as "text" instead of "auto number".



Naturally, just trying to reset the field would have been too easy. Access told me I had to insert a new field in the table and set it as "auto number which I did. I then saved the field and, as promised, Access inserted the numbers into the table in the new field. However, while it did insert all 649 numbers for all 649 records, for some reason it started with number 3 and stuck numbers 1 and 2 at the bottom of the field. Obviously, that is not going to work if I want this table to relate to all of the others.

Anyone have a thought as to why this happened and how to fix this?

Thanks.

[aytee111]

Not sure I understand - are you concerned about the order of the records on the table? Do not attach any meaning to the order of the records on a table, if you don't like the way it looks then sort it. This is purely a visual thing. Also, do not attach any meaning to autonumbers, they are just numbers to be used as pointers. They are meaningless in and of themselves.

[PATRICKPBME]

Maybe my concern is misplaced but here is what I thought.

I have one table that has the auto # as the primary key. Let's say that #1 is associated with John. In table 2 I have John's pay but the auto number instead of being 1 is 3.

I thought that when I set up a relationship between tables using the auto number as the primary and foreign key that this would tell access to display John's name with his pay because Access would look and in effect say, "John is #1 in this table so I will pull the pay from #1 in the related table." If that IS the case, my problem is that John is #1 in the first table and #3 in the second.

[PATRICKPBME]

PS: I do understand that it doesn't matter what order the numbers are in but did think the number attached to a record in one table had to match that in any others IF that number is used to establish a relationship.

[aytee111]

John's autonumber ID is 1 in the People(?) table. This number - 1 - will be carried over to all other tables that have a foreign key needed for linking to John, with a datatype of Number and a format of Long Integer. The pay table autonumber, 3, means nothing until you use it to link to a foreign key in a subsequent table that is associated with pay.

One thing I always do is call the fields by the same name so that it is immediately apparent where the number comes from - so a PK on People table will be called "PeopleID" and this same name will be used on all tables that link to this. Don't have names that Access provides, such as "ID", it becomes confusing.

[PATRICKPBME]

Okay. I really do appreciate your input. So are you saying I should ignore the auto number and use something else common to all the tables, such as the contract number, when setting up a relationship between tables?

[aytee111]

No, one purpose of a primary key is to link to other tables in a one-to-many relationship, so always use the PK (most of the time this is an autonumber) on the "one" table and add it (with the same name) as an FK on the "many" table. In this case the PK of the "many" table doesn't mean anything. The "one" table will have a value of 1 in the PK and the "many" table will have a value of 1 in the FK.

[PATRICKPBME]

I am certain the disconnect is on my end but this still just does not make sense to me. I understand the PK is there to link to the same key in the many table. My issue remains getting the same key with the same person in each table.

It seems like we are saying the same thing but I still have the problem. I do have, as you say, a primary key in the "one" table. It is also a foreign key in all of the other tables except the one "many" I am having trouble with. I still can't get that primary key from the "one" into that "many" table as a foreign key. Copying it in does no good because it comes out as text and not auto number. Adding the field in design view and designating it as "auto number" results in "John" being #1 in every other table and #3 in the problem table.

I appreciate all of your attempts and again I am sure this missing link is on my end of the chain!

[aytee111]

Post your database with some sample data.

[PATRICKPBME]

I'll see what I can do. Thanks.

[PATRICKPBME]

In the end, I just found it easier to switch the PK to the contract which also appears in all of the other tables. Working like a charm now. Thanks again for everything.

[aytee111]

Pity to give up on this thread, one of us wasn't going in the right direction! If you can, post a copy of your database anyway, I am going to lose sleep over this