If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Not to Mess Up by Replacing Relationship with Different Primery Ke
Hi Guys!
I have a questions. In my database there is a field called customer id. it is present in two tables and used to build a relationship one -many between them. A field type is number so you have to enter it manually. I messed up as I added a number outside the numeric sequence and followed it up. So instead of 89, I added 99. I want to correct it by adding a new field with autonumber. In this way, I would not mess up as my database creates new numbers for me. However, I am a bit afraid that by adding a new field and changing relationship I will destroy the balance between tables and make wrong records joined. How can I avoid it?? Thank you for your time and help Anna |
#2
|
|||
|
|||
Not to Mess Up by Replacing Relationship with Different Primery Ke
Here's one way -
Create your autonum in the master table (don't change your relationship yet). Add a numeric field to the child table, run an update query that adds the value from the master autonum field to the new numeric field in the child table. Because the tables are still correctly related with the old numeric field you will have the correct number added to the new numeric field. Then you can change your relationship to the master.autonum and child.newnumeric fields. Bonnie http://www.dataplus-svc.com A.P. wrote: Hi Guys! I have a questions. In my database there is a field called customer id. it is present in two tables and used to build a relationship one -many between them. A field type is number so you have to enter it manually. I messed up as I added a number outside the numeric sequence and followed it up. So instead of 89, I added 99. I want to correct it by adding a new field with autonumber. In this way, I would not mess up as my database creates new numbers for me. However, I am a bit afraid that by adding a new field and changing relationship I will destroy the balance between tables and make wrong records joined. How can I avoid it?? Thank you for your time and help Anna -- Message posted via http://www.accessmonster.com |
#3
|
|||
|
|||
Not to Mess Up by Replacing Relationship with Different Primery Ke
Don't forget to back up first!
A.P. wrote: Hi Guys! I have a questions. In my database there is a field called customer id. it is present in two tables and used to build a relationship one -many between them. A field type is number so you have to enter it manually. I messed up as I added a number outside the numeric sequence and followed it up. So instead of 89, I added 99. I want to correct it by adding a new field with autonumber. In this way, I would not mess up as my database creates new numbers for me. However, I am a bit afraid that by adding a new field and changing relationship I will destroy the balance between tables and make wrong records joined. How can I avoid it?? Thank you for your time and help Anna -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200907/1 |
#4
|
|||
|
|||
Not to Mess Up by Replacing Relationship with Different Primery Ke
Hi Anna
First, if you want your customer id values in order with no gaps, then an autonumber is NOT the way to go. Autonumbers are not really intended for human consumption. They might as well be (and in fact can be) completely random. Even with incremental autonumbers, you can get gaps when a record is created but is then cancelled without being saved. However, is you do want to add an autonumber primary key to an existing table, here is how: 1. VERY IMPORTANT!! Before you start, make a copy of your database file in case you mess up. Also, ensure that the Name Autocorrect option is turned of. 2. Open your table in design view and add a new field with the same name as the old primary key field, and with data type Autonumber. Rename the old field to "OldPK" but do not change the PK yet. Save the table. When you open it you should see the new field populated. Repeat the following steps (3-5) for EACH related table: 3. Open the related table and add a new field with the same name as the old foreign key field in the relationship, and with data type numeric. Set its Size property to Long and delete the "0" from the DefaultValue property. Rename the old FK field to "OldFK" and save the table. 4. Create a query with the two tables (they should automatically join on the OldPK and OldFK fields). Change the query to an update query and add the new FK field to the grid. In the "Update to" cell, enter the fully qualified name of the new PK field - e.g. [tblCustomers].[CustomerID]. Run the query. 5. Open the related table and check that the new FK field has been populated. You don't need to save the query. 6. Once all the related tables have been dealt with, open the relationships window and delete the relationships between the main table and each related table. 7. Open the main table in design view and change the PK to the new field. Then delete the OldPK field. Save the table. 8. Open the relationships window again and create a new relationship between the new PK field and each of the new FK fields. 9. Open each of the related tables and delete the OldFK field. All of your old queries/forms/reports should function as before, except of course you won't be able to edit the new customer id field. -- Good Luck :-) Graham Mandeno [Access MVP] Auckland, New Zealand "A.P." wrote in message ... Hi Guys! I have a questions. In my database there is a field called customer id. it is present in two tables and used to build a relationship one -many between them. A field type is number so you have to enter it manually. I messed up as I added a number outside the numeric sequence and followed it up. So instead of 89, I added 99. I want to correct it by adding a new field with autonumber. In this way, I would not mess up as my database creates new numbers for me. However, I am a bit afraid that by adding a new field and changing relationship I will destroy the balance between tables and make wrong records joined. How can I avoid it?? Thank you for your time and help Anna |
#5
|
|||
|
|||
Not to Mess Up by Replacing Relationship with Different Primer
Hi Again
Can you explain me how I can update it ? What should I write down in a query? Thank you A.P. "bhicks11 via AccessMonster.com" wrote: Here's one way - Create your autonum in the master table (don't change your relationship yet). Add a numeric field to the child table, run an update query that adds the value from the master autonum field to the new numeric field in the child table. Because the tables are still correctly related with the old numeric field you will have the correct number added to the new numeric field. Then you can change your relationship to the master.autonum and child.newnumeric fields. Bonnie http://www.dataplus-svc.com A.P. wrote: Hi Guys! I have a questions. In my database there is a field called customer id. it is present in two tables and used to build a relationship one -many between them. A field type is number so you have to enter it manually. I messed up as I added a number outside the numeric sequence and followed it up. So instead of 89, I added 99. I want to correct it by adding a new field with autonumber. In this way, I would not mess up as my database creates new numbers for me. However, I am a bit afraid that by adding a new field and changing relationship I will destroy the balance between tables and make wrong records joined. How can I avoid it?? Thank you for your time and help Anna -- Message posted via http://www.accessmonster.com |
#6
|
|||
|
|||
Not to Mess Up by Replacing Relationship with Different Primer
Hi Graham
But after doing it, can I still update my table by adding new customers? I will check this out tomorrow If I have any problems, I knock at your doors. Thanks Anna "Graham Mandeno" wrote: Hi Anna First, if you want your customer id values in order with no gaps, then an autonumber is NOT the way to go. Autonumbers are not really intended for human consumption. They might as well be (and in fact can be) completely random. Even with incremental autonumbers, you can get gaps when a record is created but is then cancelled without being saved. However, is you do want to add an autonumber primary key to an existing table, here is how: 1. VERY IMPORTANT!! Before you start, make a copy of your database file in case you mess up. Also, ensure that the Name Autocorrect option is turned of. 2. Open your table in design view and add a new field with the same name as the old primary key field, and with data type Autonumber. Rename the old field to "OldPK" but do not change the PK yet. Save the table. When you open it you should see the new field populated. Repeat the following steps (3-5) for EACH related table: 3. Open the related table and add a new field with the same name as the old foreign key field in the relationship, and with data type numeric. Set its Size property to Long and delete the "0" from the DefaultValue property. Rename the old FK field to "OldFK" and save the table. 4. Create a query with the two tables (they should automatically join on the OldPK and OldFK fields). Change the query to an update query and add the new FK field to the grid. In the "Update to" cell, enter the fully qualified name of the new PK field - e.g. [tblCustomers].[CustomerID]. Run the query. 5. Open the related table and check that the new FK field has been populated. You don't need to save the query. 6. Once all the related tables have been dealt with, open the relationships window and delete the relationships between the main table and each related table. 7. Open the main table in design view and change the PK to the new field. Then delete the OldPK field. Save the table. 8. Open the relationships window again and create a new relationship between the new PK field and each of the new FK fields. 9. Open each of the related tables and delete the OldFK field. All of your old queries/forms/reports should function as before, except of course you won't be able to edit the new customer id field. -- Good Luck :-) Graham Mandeno [Access MVP] Auckland, New Zealand "A.P." wrote in message ... Hi Guys! I have a questions. In my database there is a field called customer id. it is present in two tables and used to build a relationship one -many between them. A field type is number so you have to enter it manually. I messed up as I added a number outside the numeric sequence and followed it up. So instead of 89, I added 99. I want to correct it by adding a new field with autonumber. In this way, I would not mess up as my database creates new numbers for me. However, I am a bit afraid that by adding a new field and changing relationship I will destroy the balance between tables and make wrong records joined. How can I avoid it?? Thank you for your time and help Anna |
#7
|
|||
|
|||
Not to Mess Up by Replacing Relationship with Different Primer
Hi Anna
Sure, you can add new customers - you just don't get to choose the customer id for the new records because Access will do that for you. -- Good Luck :-) Graham Mandeno [Access MVP] Auckland, New Zealand "A.P." wrote in message ... Hi Graham But after doing it, can I still update my table by adding new customers? I will check this out tomorrow If I have any problems, I knock at your doors. Thanks Anna "Graham Mandeno" wrote: Hi Anna First, if you want your customer id values in order with no gaps, then an autonumber is NOT the way to go. Autonumbers are not really intended for human consumption. They might as well be (and in fact can be) completely random. Even with incremental autonumbers, you can get gaps when a record is created but is then cancelled without being saved. However, is you do want to add an autonumber primary key to an existing table, here is how: 1. VERY IMPORTANT!! Before you start, make a copy of your database file in case you mess up. Also, ensure that the Name Autocorrect option is turned of. 2. Open your table in design view and add a new field with the same name as the old primary key field, and with data type Autonumber. Rename the old field to "OldPK" but do not change the PK yet. Save the table. When you open it you should see the new field populated. Repeat the following steps (3-5) for EACH related table: 3. Open the related table and add a new field with the same name as the old foreign key field in the relationship, and with data type numeric. Set its Size property to Long and delete the "0" from the DefaultValue property. Rename the old FK field to "OldFK" and save the table. 4. Create a query with the two tables (they should automatically join on the OldPK and OldFK fields). Change the query to an update query and add the new FK field to the grid. In the "Update to" cell, enter the fully qualified name of the new PK field - e.g. [tblCustomers].[CustomerID]. Run the query. 5. Open the related table and check that the new FK field has been populated. You don't need to save the query. 6. Once all the related tables have been dealt with, open the relationships window and delete the relationships between the main table and each related table. 7. Open the main table in design view and change the PK to the new field. Then delete the OldPK field. Save the table. 8. Open the relationships window again and create a new relationship between the new PK field and each of the new FK fields. 9. Open each of the related tables and delete the OldFK field. All of your old queries/forms/reports should function as before, except of course you won't be able to edit the new customer id field. -- Good Luck :-) Graham Mandeno [Access MVP] Auckland, New Zealand "A.P." wrote in message ... Hi Guys! I have a questions. In my database there is a field called customer id. it is present in two tables and used to build a relationship one -many between them. A field type is number so you have to enter it manually. I messed up as I added a number outside the numeric sequence and followed it up. So instead of 89, I added 99. I want to correct it by adding a new field with autonumber. In this way, I would not mess up as my database creates new numbers for me. However, I am a bit afraid that by adding a new field and changing relationship I will destroy the balance between tables and make wrong records joined. How can I avoid it?? Thank you for your time and help Anna |
#8
|
|||
|
|||
Not to Mess Up by Replacing Relationship with Different Primer
Hi Graham
This is what I entered Parent table CAT Detailis Child Table Queries & Graffiti I want to update a new field Customer ID in the child table. I opened a query, selected a query type. I chose as following Field: Customer ID Table: Queries & Graffiti Update to: [tblCAT Detailis].[Customer ID] Then there is a window popping out ' enter parameter value'. What did I do wrong? Anna "Graham Mandeno" wrote: Hi Anna Sure, you can add new customers - you just don't get to choose the customer id for the new records because Access will do that for you. -- Good Luck :-) Graham Mandeno [Access MVP] Auckland, New Zealand "A.P." wrote in message ... Hi Graham But after doing it, can I still update my table by adding new customers? I will check this out tomorrow If I have any problems, I knock at your doors. Thanks Anna "Graham Mandeno" wrote: Hi Anna First, if you want your customer id values in order with no gaps, then an autonumber is NOT the way to go. Autonumbers are not really intended for human consumption. They might as well be (and in fact can be) completely random. Even with incremental autonumbers, you can get gaps when a record is created but is then cancelled without being saved. However, is you do want to add an autonumber primary key to an existing table, here is how: 1. VERY IMPORTANT!! Before you start, make a copy of your database file in case you mess up. Also, ensure that the Name Autocorrect option is turned of. 2. Open your table in design view and add a new field with the same name as the old primary key field, and with data type Autonumber. Rename the old field to "OldPK" but do not change the PK yet. Save the table. When you open it you should see the new field populated. Repeat the following steps (3-5) for EACH related table: 3. Open the related table and add a new field with the same name as the old foreign key field in the relationship, and with data type numeric. Set its Size property to Long and delete the "0" from the DefaultValue property. Rename the old FK field to "OldFK" and save the table. 4. Create a query with the two tables (they should automatically join on the OldPK and OldFK fields). Change the query to an update query and add the new FK field to the grid. In the "Update to" cell, enter the fully qualified name of the new PK field - e.g. [tblCustomers].[CustomerID]. Run the query. 5. Open the related table and check that the new FK field has been populated. You don't need to save the query. 6. Once all the related tables have been dealt with, open the relationships window and delete the relationships between the main table and each related table. 7. Open the main table in design view and change the PK to the new field. Then delete the OldPK field. Save the table. 8. Open the relationships window again and create a new relationship between the new PK field and each of the new FK fields. 9. Open each of the related tables and delete the OldFK field. All of your old queries/forms/reports should function as before, except of course you won't be able to edit the new customer id field. -- Good Luck :-) Graham Mandeno [Access MVP] Auckland, New Zealand "A.P." wrote in message ... Hi Guys! I have a questions. In my database there is a field called customer id. it is present in two tables and used to build a relationship one -many between them. A field type is number so you have to enter it manually. I messed up as I added a number outside the numeric sequence and followed it up. So instead of 89, I added 99. I want to correct it by adding a new field with autonumber. In this way, I would not mess up as my database creates new numbers for me. However, I am a bit afraid that by adding a new field and changing relationship I will destroy the balance between tables and make wrong records joined. How can I avoid it?? Thank you for your time and help Anna |
#9
|
|||
|
|||
Not to Mess Up by Replacing Relationship with Different Primer
Try using an exclamation point (aka bang) instead of the dot:
[tblCAT Detailis]![Customer ID] What exactly is the text of the parameter prompt? "A.P." wrote in message ... Hi Graham This is what I entered Parent table CAT Detailis Child Table Queries & Graffiti I want to update a new field Customer ID in the child table. I opened a query, selected a query type. I chose as following Field: Customer ID Table: Queries & Graffiti Update to: [tblCAT Detailis].[Customer ID] Then there is a window popping out ' enter parameter value'. What did I do wrong? Anna "Graham Mandeno" wrote: Hi Anna Sure, you can add new customers - you just don't get to choose the customer id for the new records because Access will do that for you. -- Good Luck :-) Graham Mandeno [Access MVP] Auckland, New Zealand "A.P." wrote in message ... Hi Graham But after doing it, can I still update my table by adding new customers? I will check this out tomorrow If I have any problems, I knock at your doors. Thanks Anna "Graham Mandeno" wrote: Hi Anna First, if you want your customer id values in order with no gaps, then an autonumber is NOT the way to go. Autonumbers are not really intended for human consumption. They might as well be (and in fact can be) completely random. Even with incremental autonumbers, you can get gaps when a record is created but is then cancelled without being saved. However, is you do want to add an autonumber primary key to an existing table, here is how: 1. VERY IMPORTANT!! Before you start, make a copy of your database file in case you mess up. Also, ensure that the Name Autocorrect option is turned of. 2. Open your table in design view and add a new field with the same name as the old primary key field, and with data type Autonumber. Rename the old field to "OldPK" but do not change the PK yet. Save the table. When you open it you should see the new field populated. Repeat the following steps (3-5) for EACH related table: 3. Open the related table and add a new field with the same name as the old foreign key field in the relationship, and with data type numeric. Set its Size property to Long and delete the "0" from the DefaultValue property. Rename the old FK field to "OldFK" and save the table. 4. Create a query with the two tables (they should automatically join on the OldPK and OldFK fields). Change the query to an update query and add the new FK field to the grid. In the "Update to" cell, enter the fully qualified name of the new PK field - e.g. [tblCustomers].[CustomerID]. Run the query. 5. Open the related table and check that the new FK field has been populated. You don't need to save the query. 6. Once all the related tables have been dealt with, open the relationships window and delete the relationships between the main table and each related table. 7. Open the main table in design view and change the PK to the new field. Then delete the OldPK field. Save the table. 8. Open the relationships window again and create a new relationship between the new PK field and each of the new FK fields. 9. Open each of the related tables and delete the OldFK field. All of your old queries/forms/reports should function as before, except of course you won't be able to edit the new customer id field. -- Good Luck :-) Graham Mandeno [Access MVP] Auckland, New Zealand "A.P." wrote in message ... Hi Guys! I have a questions. In my database there is a field called customer id. it is present in two tables and used to build a relationship one -many between them. A field type is number so you have to enter it manually. I messed up as I added a number outside the numeric sequence and followed it up. So instead of 89, I added 99. I want to correct it by adding a new field with autonumber. In this way, I would not mess up as my database creates new numbers for me. However, I am a bit afraid that by adding a new field and changing relationship I will destroy the balance between tables and make wrong records joined. How can I avoid it?? Thank you for your time and help Anna |
#10
|
|||
|
|||
Not to Mess Up by Replacing Relationship with Different Primer
In the windown I have as following:
Enter Parameter Value(name of message box) tblCAT Detailis!Business Number( message) Gap to fill in "BruceM" wrote: Try using an exclamation point (aka bang) instead of the dot: [tblCAT Detailis]![Customer ID] What exactly is the text of the parameter prompt? "A.P." wrote in message ... Hi Graham This is what I entered Parent table CAT Detailis Child Table Queries & Graffiti I want to update a new field Customer ID in the child table. I opened a query, selected a query type. I chose as following Field: Customer ID Table: Queries & Graffiti Update to: [tblCAT Detailis].[Customer ID] Then there is a window popping out ' enter parameter value'. What did I do wrong? Anna "Graham Mandeno" wrote: Hi Anna Sure, you can add new customers - you just don't get to choose the customer id for the new records because Access will do that for you. -- Good Luck :-) Graham Mandeno [Access MVP] Auckland, New Zealand "A.P." wrote in message ... Hi Graham But after doing it, can I still update my table by adding new customers? I will check this out tomorrow If I have any problems, I knock at your doors. Thanks Anna "Graham Mandeno" wrote: Hi Anna First, if you want your customer id values in order with no gaps, then an autonumber is NOT the way to go. Autonumbers are not really intended for human consumption. They might as well be (and in fact can be) completely random. Even with incremental autonumbers, you can get gaps when a record is created but is then cancelled without being saved. However, is you do want to add an autonumber primary key to an existing table, here is how: 1. VERY IMPORTANT!! Before you start, make a copy of your database file in case you mess up. Also, ensure that the Name Autocorrect option is turned of. 2. Open your table in design view and add a new field with the same name as the old primary key field, and with data type Autonumber. Rename the old field to "OldPK" but do not change the PK yet. Save the table. When you open it you should see the new field populated. Repeat the following steps (3-5) for EACH related table: 3. Open the related table and add a new field with the same name as the old foreign key field in the relationship, and with data type numeric. Set its Size property to Long and delete the "0" from the DefaultValue property. Rename the old FK field to "OldFK" and save the table. 4. Create a query with the two tables (they should automatically join on the OldPK and OldFK fields). Change the query to an update query and add the new FK field to the grid. In the "Update to" cell, enter the fully qualified name of the new PK field - e.g. [tblCustomers].[CustomerID]. Run the query. 5. Open the related table and check that the new FK field has been populated. You don't need to save the query. 6. Once all the related tables have been dealt with, open the relationships window and delete the relationships between the main table and each related table. 7. Open the main table in design view and change the PK to the new field. Then delete the OldPK field. Save the table. 8. Open the relationships window again and create a new relationship between the new PK field and each of the new FK fields. 9. Open each of the related tables and delete the OldFK field. All of your old queries/forms/reports should function as before, except of course you won't be able to edit the new customer id field. -- Good Luck :-) Graham Mandeno [Access MVP] Auckland, New Zealand "A.P." wrote in message ... Hi Guys! I have a questions. In my database there is a field called customer id. it is present in two tables and used to build a relationship one -many between them. A field type is number so you have to enter it manually. I messed up as I added a number outside the numeric sequence and followed it up. So instead of 89, I added 99. I want to correct it by adding a new field with autonumber. In this way, I would not mess up as my database creates new numbers for me. However, I am a bit afraid that by adding a new field and changing relationship I will destroy the balance between tables and make wrong records joined. How can I avoid it?? Thank you for your time and help Anna |
|
Thread Tools | |
Display Modes | |
|
|