--- In AccessDevelopers@yahoogroups.com, "Jay Beckham" <jay@...> wrote:
>
> I have an existing table with a profile number I want to generate on
> additions to the table automatically. The numbers are between 1 and 1500
> with some missing numbers. I tried just change the profile (is also the
> Key) and it won't let me.
>
>
>
> How can I take care of this?
>
>
>
> Thanks
>
>
>
> Jay Beckham
>
>
Jay... the very FIRST thing you do is make a backup... maybe make two backups.
It sounds to me like you have TWO related tables that use your profile key to create the relationship. If so, you need to import both tables into NEW tables then, relink the new tables using code/queries to properly do this.
I will call your tables - Tbl1 and Tbl2 with Tbl1 being the primary table.
Create a new table, duplicating all the fields from Tbl1. Change the field type for your Profile Key to numeric/long and then create a new Primary key field. Import your data from Tbl1 to this new Table.
You should now have a new table with all the data from Tbl1 PLUS a new primary key field. Your OLD Profile Key field should now be a numeric field in the NEW Table.
VERIFY! VERIFY! VERIFY!
Now, do the same for Tbl2 - WITH THE FOLLOWING DIFFERENCES
I don't know the structure of your tables and if Tbl2 has a primary key or not. So, my suggestion here might need some tweaking.
The Profile Key in Tbl2 should be numeric/long (the same as in new Tbl1). You now need to create a NEW foreign key field (numeric/long). When you complete your import from Tbl2... this field should be empty. Import your data from Tbl2
Again.... VERIFY! VERIFY! VERIFY!
Now, you need to create an update query using these two NEW tables to match the original Profile Key from each table and update the NewTbl2 with the Primary key from your NewTbl1.
VERIFY! VERIFY! VERIFY!
Once complete, you need to reestablish your relationship with these NEW tables, rename your OLD tables (don't delete right away) and then rename the NEW tables using the OLD table names (if you use forms, etc.)
Not sure if I missed anything but, hope this helps.
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (5) |
Please zip all files prior to uploading to Files section.
Tidak ada komentar:
Posting Komentar