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
|
|||
|
|||
Transferring information from one table to another
My database is the "Orders" database provided with Microsoft Access. The
Orders, Orders Details, Orders by Customer and Invoice all require that I input an employee name. Now that I have worked with the forms in more depth, I find that a table named "Dealer" and "employee" are actually the same info. How do I go about deleting the Dealer table and copying all of the information and transf to the Employee table; thus allowing all forms and subforms, and invoices to function properly??? I have to have the employee table remain. Any time I have attempted to remove the employee table, my entire database has problems. Hindsight IS better than foresight! Thank you in advance, JR |
#2
|
|||
|
|||
JR, what you are proposing to do makes very good sense from a design
point-of-view, but--as you found--it is not a simple thing once the database has already been in use. Presumably your Dealer table has a primary key--perhaps an AutoNumber named DealerID. Your Employee table will also have an EmployeeID primary key. And there may be cases where the DealerID is using the same numbers as the EmployeeID. It is therefore not just a matter of copying the records from one table to another. Further, there are probably other related tables that are using the DealerID and EmployeeID. So any changes have to be made to those tables also. The process would therefore involve something like this: 1. Make sure the Name AutoCorrect boxes are unchecked under: Tools | Options | General Otherwise Access will try to remember the wrong tables and fields. 2. Create a new table that provides the fields from both tables. (I never know what to call this combined one--usually ends up as tblClient: though that's not a perfect name, it ends up with corporate entities (companies, schools, ...) and persons (staff, buyers, ...)). 3. Use an Append query (Append in query design) to copy all the records from the Dealer table into the new one. 4. Use an Append query to copy all records from the Employee table into the new one. But this time populate ClientID field with: 1000 + [EmployeeID] where the number is large enough that it exceeds the largest value in the DealerID. 5. Break all relationships between the Dealer table and related tables, and between the Employee table and related tables. 6. Use an Update query to add the 1000 to the value of all EmployeeID foreign keys throughout the database, so they match the value assigned in step 3. 7. Create the relations between the new Client table and all the other related tables. 8. Track down all queries that use the Dealer and Employee tables, and change them to use the new table instead. There are commercial utilities such as Speed Ferret that can help in this process. You can copy the SQL statment out to Notepad and use search'n'replace. This query might help you trace down the dependencies: SELECT MSysObjects.Name FROM MSysQueries INNER JOIN MSysObjects ON MSysQueries.ObjectId = MSysObjects.Id WHERE MSysQueries.Expression Like "*" & [qryName] & "*" GROUP BY MSysObjects.Name; 9. Rename the Dealer and Employee tables to (say) DealerX and EmployeeX, so that none of the code and queries can accidentally find them. (Or delete them if you are feeling brave and have backups.) 10. Change the RecordSource of all affected forms and reports, and the RowSource of all affected combos and list boxes. 11. Search and replace in code as well. Whether it's worth that effort for this database is up to you, but the design is certainly worth keeping in mind for your next one. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "jwr" wrote in message ... My database is the "Orders" database provided with Microsoft Access. The Orders, Orders Details, Orders by Customer and Invoice all require that I input an employee name. Now that I have worked with the forms in more depth, I find that a table named "Dealer" and "employee" are actually the same info. How do I go about deleting the Dealer table and copying all of the information and transf to the Employee table; thus allowing all forms and subforms, and invoices to function properly??? I have to have the employee table remain. Any time I have attempted to remove the employee table, my entire database has problems. Hindsight IS better than foresight! Thank you in advance, JR |
#3
|
|||
|
|||
Before I begin this process, several more points --
There are no records in the employee table; however, the Orders database requires the employee id to be included in the reports, invoices, etc. Dealer table contains all the information that I would like to see in place of employee id. If I change the employee id to dealer id in design view on, for instance, the invoice, the report does not work. Is this the process I need to follow -- OR -- is there a way to change employee id references to be dealer id? Whenever I remove the employee id fields, forms and reports do not work. Thanks in advance, JR "Allen Browne" wrote in message ... JR, what you are proposing to do makes very good sense from a design point-of-view, but--as you found--it is not a simple thing once the database has already been in use. Presumably your Dealer table has a primary key--perhaps an AutoNumber named DealerID. Your Employee table will also have an EmployeeID primary key. And there may be cases where the DealerID is using the same numbers as the EmployeeID. It is therefore not just a matter of copying the records from one table to another. Further, there are probably other related tables that are using the DealerID and EmployeeID. So any changes have to be made to those tables also. The process would therefore involve something like this: 1. Make sure the Name AutoCorrect boxes are unchecked under: Tools | Options | General Otherwise Access will try to remember the wrong tables and fields. 2. Create a new table that provides the fields from both tables. (I never know what to call this combined one--usually ends up as tblClient: though that's not a perfect name, it ends up with corporate entities (companies, schools, ...) and persons (staff, buyers, ...)). 3. Use an Append query (Append in query design) to copy all the records from the Dealer table into the new one. 4. Use an Append query to copy all records from the Employee table into the new one. But this time populate ClientID field with: 1000 + [EmployeeID] where the number is large enough that it exceeds the largest value in the DealerID. 5. Break all relationships between the Dealer table and related tables, and between the Employee table and related tables. 6. Use an Update query to add the 1000 to the value of all EmployeeID foreign keys throughout the database, so they match the value assigned in step 3. 7. Create the relations between the new Client table and all the other related tables. 8. Track down all queries that use the Dealer and Employee tables, and change them to use the new table instead. There are commercial utilities such as Speed Ferret that can help in this process. You can copy the SQL statment out to Notepad and use search'n'replace. This query might help you trace down the dependencies: SELECT MSysObjects.Name FROM MSysQueries INNER JOIN MSysObjects ON MSysQueries.ObjectId = MSysObjects.Id WHERE MSysQueries.Expression Like "*" & [qryName] & "*" GROUP BY MSysObjects.Name; 9. Rename the Dealer and Employee tables to (say) DealerX and EmployeeX, so that none of the code and queries can accidentally find them. (Or delete them if you are feeling brave and have backups.) 10. Change the RecordSource of all affected forms and reports, and the RowSource of all affected combos and list boxes. 11. Search and replace in code as well. Whether it's worth that effort for this database is up to you, but the design is certainly worth keeping in mind for your next one. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "jwr" wrote in message ... My database is the "Orders" database provided with Microsoft Access. The Orders, Orders Details, Orders by Customer and Invoice all require that I input an employee name. Now that I have worked with the forms in more depth, I find that a table named "Dealer" and "employee" are actually the same info. How do I go about deleting the Dealer table and copying all of the information and transf to the Employee table; thus allowing all forms and subforms, and invoices to function properly??? I have to have the employee table remain. Any time I have attempted to remove the employee table, my entire database has problems. Hindsight IS better than foresight! Thank you in advance, JR |
#4
|
|||
|
|||
If you have no data in the Employee table, and therefore no related values
in any EmployeeID foreign key in any other table, that simplies the process. You can just break all the relationships, rename the Dealer table, drop the Employee talbe, and then create all the relationships to the newly named table. As explained in the prior reply, you will need to go through your database and find every affected query, form, report, macro, and code reference, and change them all. As you say, it just fouls up and doesn't work until you make these changes. There are probably many hours work--possibly days--to achieve that change after the database has already been built. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "jwr" wrote in message news Before I begin this process, several more points -- There are no records in the employee table; however, the Orders database requires the employee id to be included in the reports, invoices, etc. Dealer table contains all the information that I would like to see in place of employee id. If I change the employee id to dealer id in design view on, for instance, the invoice, the report does not work. Is this the process I need to follow -- OR -- is there a way to change employee id references to be dealer id? Whenever I remove the employee id fields, forms and reports do not work. Thanks in advance, JR "Allen Browne" wrote in message ... JR, what you are proposing to do makes very good sense from a design point-of-view, but--as you found--it is not a simple thing once the database has already been in use. Presumably your Dealer table has a primary key--perhaps an AutoNumber named DealerID. Your Employee table will also have an EmployeeID primary key. And there may be cases where the DealerID is using the same numbers as the EmployeeID. It is therefore not just a matter of copying the records from one table to another. Further, there are probably other related tables that are using the DealerID and EmployeeID. So any changes have to be made to those tables also. The process would therefore involve something like this: 1. Make sure the Name AutoCorrect boxes are unchecked under: Tools | Options | General Otherwise Access will try to remember the wrong tables and fields. 2. Create a new table that provides the fields from both tables. (I never know what to call this combined one--usually ends up as tblClient: though that's not a perfect name, it ends up with corporate entities (companies, schools, ...) and persons (staff, buyers, ...)). 3. Use an Append query (Append in query design) to copy all the records from the Dealer table into the new one. 4. Use an Append query to copy all records from the Employee table into the new one. But this time populate ClientID field with: 1000 + [EmployeeID] where the number is large enough that it exceeds the largest value in the DealerID. 5. Break all relationships between the Dealer table and related tables, and between the Employee table and related tables. 6. Use an Update query to add the 1000 to the value of all EmployeeID foreign keys throughout the database, so they match the value assigned in step 3. 7. Create the relations between the new Client table and all the other related tables. 8. Track down all queries that use the Dealer and Employee tables, and change them to use the new table instead. There are commercial utilities such as Speed Ferret that can help in this process. You can copy the SQL statment out to Notepad and use search'n'replace. This query might help you trace down the dependencies: SELECT MSysObjects.Name FROM MSysQueries INNER JOIN MSysObjects ON MSysQueries.ObjectId = MSysObjects.Id WHERE MSysQueries.Expression Like "*" & [qryName] & "*" GROUP BY MSysObjects.Name; 9. Rename the Dealer and Employee tables to (say) DealerX and EmployeeX, so that none of the code and queries can accidentally find them. (Or delete them if you are feeling brave and have backups.) 10. Change the RecordSource of all affected forms and reports, and the RowSource of all affected combos and list boxes. 11. Search and replace in code as well. Whether it's worth that effort for this database is up to you, but the design is certainly worth keeping in mind for your next one. "jwr" wrote in message ... My database is the "Orders" database provided with Microsoft Access. The Orders, Orders Details, Orders by Customer and Invoice all require that I input an employee name. Now that I have worked with the forms in more depth, I find that a table named "Dealer" and "employee" are actually the same info. How do I go about deleting the Dealer table and copying all of the information and transf to the Employee table; thus allowing all forms and subforms, and invoices to function properly??? I have to have the employee table remain. Any time I have attempted to remove the employee table, my entire database has problems. Hindsight IS better than foresight! Thank you in advance, JR |
#5
|
|||
|
|||
What would I and why would I rename the Dealer table? I don't understand.
I was thinking that if I could locate, perhaps by query, every instance where Employee ID was used, I could substitute Dealer ID. Again, I do not know how to do what I am suggesting. "Allen Browne" wrote in message ... If you have no data in the Employee table, and therefore no related values in any EmployeeID foreign key in any other table, that simplies the process. You can just break all the relationships, rename the Dealer table, drop the Employee talbe, and then create all the relationships to the newly named table. As explained in the prior reply, you will need to go through your database and find every affected query, form, report, macro, and code reference, and change them all. As you say, it just fouls up and doesn't work until you make these changes. There are probably many hours work--possibly days--to achieve that change after the database has already been built. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "jwr" wrote in message news Before I begin this process, several more points -- There are no records in the employee table; however, the Orders database requires the employee id to be included in the reports, invoices, etc. Dealer table contains all the information that I would like to see in place of employee id. If I change the employee id to dealer id in design view on, for instance, the invoice, the report does not work. Is this the process I need to follow -- OR -- is there a way to change employee id references to be dealer id? Whenever I remove the employee id fields, forms and reports do not work. Thanks in advance, JR "Allen Browne" wrote in message ... JR, what you are proposing to do makes very good sense from a design point-of-view, but--as you found--it is not a simple thing once the database has already been in use. Presumably your Dealer table has a primary key--perhaps an AutoNumber named DealerID. Your Employee table will also have an EmployeeID primary key. And there may be cases where the DealerID is using the same numbers as the EmployeeID. It is therefore not just a matter of copying the records from one table to another. Further, there are probably other related tables that are using the DealerID and EmployeeID. So any changes have to be made to those tables also. The process would therefore involve something like this: 1. Make sure the Name AutoCorrect boxes are unchecked under: Tools | Options | General Otherwise Access will try to remember the wrong tables and fields. 2. Create a new table that provides the fields from both tables. (I never know what to call this combined one--usually ends up as tblClient: though that's not a perfect name, it ends up with corporate entities (companies, schools, ...) and persons (staff, buyers, ...)). 3. Use an Append query (Append in query design) to copy all the records from the Dealer table into the new one. 4. Use an Append query to copy all records from the Employee table into the new one. But this time populate ClientID field with: 1000 + [EmployeeID] where the number is large enough that it exceeds the largest value in the DealerID. 5. Break all relationships between the Dealer table and related tables, and between the Employee table and related tables. 6. Use an Update query to add the 1000 to the value of all EmployeeID foreign keys throughout the database, so they match the value assigned in step 3. 7. Create the relations between the new Client table and all the other related tables. 8. Track down all queries that use the Dealer and Employee tables, and change them to use the new table instead. There are commercial utilities such as Speed Ferret that can help in this process. You can copy the SQL statment out to Notepad and use search'n'replace. This query might help you trace down the dependencies: SELECT MSysObjects.Name FROM MSysQueries INNER JOIN MSysObjects ON MSysQueries.ObjectId = MSysObjects.Id WHERE MSysQueries.Expression Like "*" & [qryName] & "*" GROUP BY MSysObjects.Name; 9. Rename the Dealer and Employee tables to (say) DealerX and EmployeeX, so that none of the code and queries can accidentally find them. (Or delete them if you are feeling brave and have backups.) 10. Change the RecordSource of all affected forms and reports, and the RowSource of all affected combos and list boxes. 11. Search and replace in code as well. Whether it's worth that effort for this database is up to you, but the design is certainly worth keeping in mind for your next one. "jwr" wrote in message ... My database is the "Orders" database provided with Microsoft Access. The Orders, Orders Details, Orders by Customer and Invoice all require that I input an employee name. Now that I have worked with the forms in more depth, I find that a table named "Dealer" and "employee" are actually the same info. How do I go about deleting the Dealer table and copying all of the information and transf to the Employee table; thus allowing all forms and subforms, and invoices to function properly??? I have to have the employee table remain. Any time I have attempted to remove the employee table, my entire database has problems. Hindsight IS better than foresight! Thank you in advance, JR |
#6
|
|||
|
|||
The renaming is not necessary.
The suggestion was merely an addendum to my previous post, suggesting how to integrate the previous suggestions into where you were. There is nothing further I can add that would be of use to where you are up to here. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "jwr" wrote in message . .. What would I and why would I rename the Dealer table? I don't understand. I was thinking that if I could locate, perhaps by query, every instance where Employee ID was used, I could substitute Dealer ID. Again, I do not know how to do what I am suggesting. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Help with relationship plase | Rock | Database Design | 5 | July 4th, 2005 03:54 AM |
unable to repair inobox | Sudheer Mumbai | General Discussion | 1 | February 20th, 2005 11:55 AM |
transpose | john | Using Forms | 1 | November 24th, 2004 06:16 PM |
Here's a shocker | Mike Labosh | General Discussion | 2 | October 26th, 2004 05:04 PM |
COMPARE THE TWO TABLES | Stefanie | General Discussion | 0 | June 4th, 2004 04:36 PM |