changining to autonumber after 1300 entries 1

[DanEaton]

I hate string ID's. My first database, and now, I realize that I have shot myself in the foot for not using autonumber. I have changed one relationship already, now I want the change the last one. However, I want to do so without deleting info because if I did, I'd probably be fired. Ok, I have a catalog storage database.

tblCatalog tblCatDetails tblSubCategory
CatID<(one-many)>CatID SubCategory
CatDate SubCatID<(many-one)>SubCatID
etc. CategoryID
(one-many)
^
tblProdCategory |
CategoryID
Category

The field that I'd like to be autonumber is SubCatID in the tblSubCategory. This would make my life much easier. However, we have already entered 600 catalogs in, containing over 1200 products, all tagged with strings. I have tried to overcome this obstacle in the following manner:

1)add a new field in tblSubCategory called SubCategoryID(new ID).
2)kill the relationship b/w tblSubCategory & tblCatDetails
3)make SubCategoryID autonumber...which works, it updates it with the number seemingly corresponding to when they were entered. (if this part doesnt' work, it kills my whole idea)
4)re-create the relationship and look for ways to make it update all of the records with the new autonumber values........

And I am stuck, if anyone knows how to do this (and if my assumption of it updating in order is correct) I would forever be in your debt. Dan Eaton
deaton@caemachinery.com

 

[ClydeDoggie]

I am just a little confused about what exactly you did already, but I think I can help you.
1. Add new field to tblSubCategory named SubCategoryID with a type of Autonumber. This will automatically fill with the new ID's. (I believe you have done this).
2.Kill relationship between tblSubCategory and tblCatDetails.
3. Run an update query to update the key in tblCatDetails to the new ID. The SQL would look like this:
UPDATE tblSubCategory
INNER JOIN tblSubCategory ON tblCatDetails.SubCatID = tblSubCategory.SubCatID
SET tblCatDetails.SubCatID = tblSubCategory.SubCategoryID;
4. If tblCatDetails.SubCatID was a text field, change to Number(long).
5. Reset relationship between tables using tblSubCategory.SubCategoryID and tblCatDetails.SubCatID.
6. Delete the old id field from tblSubCategory (SubCatID).

I believe that will work but a few notes.
TRY IT ON A BACKUP OF THE DATABASE FIRST!!!!!!
After step 3, if it worked incorrectly, your DOA. In fact I would make two backups of the db first, one to try it on and one to save in case something goes wrong and you don't notice before you do it on the production db.

Use better naming conventions. If you have two tables like tblCategory and tblCatalog, using the abbreviation Cat is useless. Also, don't name the foreign keys the same as the id they are linked to. If you search some of the Access forums for Naming Conventions you will find other posts that give good guidelines for naming table fields.

Good Luck! Let me know if it works.

 

[DanEaton]

I'm think I see what you're are saying and I think it will work. However, I must admit, I have managed thus far to stay away from all forms of action queries. This means that I am unfamiliar with the SQL required to update fields. I tried to copy and paste your expression (with minor changes) and it didn't work. So I tried to understand what was going on. In your sql, I was wondering why you would update tblSubCategory. Perhaps I just don't know what I'm talking about, but wouldn't tblCatDetails be the choice as it currently has strings ID tags?

UPDATE tblSubCategory
INNER JOIN tblSubCategory ON tblCatDetails.SubCatID = tblSubCategory.SubCatID
SET tblCatDetails.SubCatID = tblSubCategory.SubCategoryID;

Sorry, I understand queries well enough, but the SQL for update is unfamiliar. Dan Eaton
deaton@caemachinery.com

 

[DanEaton]

Thanks for the help, Got it! This will save me some serious time. Dan Eaton
deaton@caemachinery.com