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
|
|||
|
|||
Delete Query
I was wondering how this would be done. Currently I have a database set up tracking employees and all kinds of training, disciplinary, errors, etc. They are all linked to the main Employees table by the EmployeeID with referential integrity on. The EmployeeID is or is part of the primary key in all of my other tables. On my Employees table I have a yes/no box for if they are separated. What I was wanting to know, since I don't want to delete the past employee basic info such as wage and reason for leaving, is how I can make one delete query that would delete all their info from these other tables, or at least a one button push to do it.
I tried combining them all in one delete query but got the statement couldn't delete from specified tables. Currently I have a separate delete query for each table, which means I would have to run about 7 different queries to get the results I was wanting. Thanks |
#2
|
|||
|
|||
Delete Query
Dear Shanin:
A simple approach might work. If you set Cascade Delete in all the relationships in the same dialog where you set up the referential integrity, then when you delete the row from the Employee table all the dependent rows in the tables will be removed as well. There are some special circumstances in which this cannot be done, but it will tell you this when you try to implement the Cascade Deletes. However, you should take a bit of care. Use a copy of the database to set this up, and test it. Another approach is to write delete queries to be run when you remove an employee. These queries must start at the "top" of the "tree" of related tables. On a piece of paper, put the Employee table at the bottom. Add all the tables that depend on this table (that have EmployeeID as a single part referential key) as "branches" of that table, putting them above the Employee table on your paper. Next, consider whether there are tables that depend on these first-level branches. If so, add them similarly. The point of this exercise is that you cannot delete a row from a "parent" table unless all the related rows in all "child" tables have been removed FIRST. The diagram gives you a visual reference to see this. You can also do this in the Relationship Diagram for your system. I usually organize this left-to-right instead of bottom-to-top, but the important point is to add the "tree" organization to the diagram so it is readable as the hierarchy it represents, with parent on the left and child on the right. From this you can quickly derive the order in which tables must have rows deleted. You cannot leave a "child" as an orphan even though you intend to come back later and remove that child row. The rows of the child tables must be removed first or you have created an "orphan" which violates the referential integrity rules you've set up. Anyway, my guess is that this is one problem you must address. However, in no case could this be done in one delete query. It would be a series of delete queries, or you can use cascading as already described. Tom Ellison Microsoft Access MVP Ellison Enterprises - Your One Stop IT Experts On Fri, 30 Jul 2004 07:45:01 -0700, Shanin wrote: I was wondering how this would be done. Currently I have a database set up tracking employees and all kinds of training, disciplinary, errors, etc. They are all linked to the main Employees table by the EmployeeID with referential integrity on. The EmployeeID is or is part of the primary key in all of my other tables. On my Employees table I have a yes/no box for if they are separated. What I was wanting to know, since I don't want to delete the past employee basic info such as wage and reason for leaving, is how I can make one delete query that would delete all their info from these other tables, or at least a one button push to do it. I tried combining them all in one delete query but got the statement couldn't delete from specified tables. Currently I have a separate delete query for each table, which means I would have to run about 7 different queries to get the results I was wanting. Thanks |
#3
|
|||
|
|||
Delete Query
Actually I do have cascading deletes selected, but I don't plan on deleting the employee from the tblEmployees. We still want to be able to pull up information in order to do employment verification. Once they leave though, we know longer need to know all their trainings, etc, so I want to delete them from that. All the tables are only related to the tblEmployees and not to each other. Is there a macro or code you could write that would assign running all of those queries one after another?
"Tom Ellison" wrote: Dear Shanin: A simple approach might work. If you set Cascade Delete in all the relationships in the same dialog where you set up the referential integrity, then when you delete the row from the Employee table all the dependent rows in the tables will be removed as well. There are some special circumstances in which this cannot be done, but it will tell you this when you try to implement the Cascade Deletes. However, you should take a bit of care. Use a copy of the database to set this up, and test it. Another approach is to write delete queries to be run when you remove an employee. These queries must start at the "top" of the "tree" of related tables. On a piece of paper, put the Employee table at the bottom. Add all the tables that depend on this table (that have EmployeeID as a single part referential key) as "branches" of that table, putting them above the Employee table on your paper. Next, consider whether there are tables that depend on these first-level branches. If so, add them similarly. The point of this exercise is that you cannot delete a row from a "parent" table unless all the related rows in all "child" tables have been removed FIRST. The diagram gives you a visual reference to see this. You can also do this in the Relationship Diagram for your system. I usually organize this left-to-right instead of bottom-to-top, but the important point is to add the "tree" organization to the diagram so it is readable as the hierarchy it represents, with parent on the left and child on the right. From this you can quickly derive the order in which tables must have rows deleted. You cannot leave a "child" as an orphan even though you intend to come back later and remove that child row. The rows of the child tables must be removed first or you have created an "orphan" which violates the referential integrity rules you've set up. Anyway, my guess is that this is one problem you must address. However, in no case could this be done in one delete query. It would be a series of delete queries, or you can use cascading as already described. Tom Ellison Microsoft Access MVP Ellison Enterprises - Your One Stop IT Experts On Fri, 30 Jul 2004 07:45:01 -0700, Shanin wrote: I was wondering how this would be done. Currently I have a database set up tracking employees and all kinds of training, disciplinary, errors, etc. They are all linked to the main Employees table by the EmployeeID with referential integrity on. The EmployeeID is or is part of the primary key in all of my other tables. On my Employees table I have a yes/no box for if they are separated. What I was wanting to know, since I don't want to delete the past employee basic info such as wage and reason for leaving, is how I can make one delete query that would delete all their info from these other tables, or at least a one button push to do it. I tried combining them all in one delete query but got the statement couldn't delete from specified tables. Currently I have a separate delete query for each table, which means I would have to run about 7 different queries to get the results I was wanting. Thanks |
#4
|
|||
|
|||
Delete Query
Dear Shanin:
That is clearer. What I posted applies to deleting dependeing rows from a set of related tables nonetheless. As far as having me write the queries, I don't have access to your table designs. I don't know specifically what you need. Apparently, you'd need to delete using a filter criterion of the EmployeeID selected in a form. This could be accomplished with saved queries that reference a control on the form then. Stringing them together in a macro or in VBA code would be rather simple if the relational diagram I explained is itself quite simple. But yes, I expect I could do a project like this without any trouble. However, the free help here is intended more to teach you how to do it yourself. Were you able to follow and apply what I posted before? Are there some specific questions you could ask about what you need to know to implement it? If so, I think you'll be able to get the help you need here just fine. Tom Ellison Microsoft Access MVP Ellison Enterprises - Your One Stop IT Experts On Fri, 30 Jul 2004 10:01:03 -0700, Shanin wrote: Actually I do have cascading deletes selected, but I don't plan on deleting the employee from the tblEmployees. We still want to be able to pull up information in order to do employment verification. Once they leave though, we know longer need to know all their trainings, etc, so I want to delete them from that. All the tables are only related to the tblEmployees and not to each other. Is there a macro or code you could write that would assign running all of those queries one after another? "Tom Ellison" wrote: Dear Shanin: A simple approach might work. If you set Cascade Delete in all the relationships in the same dialog where you set up the referential integrity, then when you delete the row from the Employee table all the dependent rows in the tables will be removed as well. There are some special circumstances in which this cannot be done, but it will tell you this when you try to implement the Cascade Deletes. However, you should take a bit of care. Use a copy of the database to set this up, and test it. Another approach is to write delete queries to be run when you remove an employee. These queries must start at the "top" of the "tree" of related tables. On a piece of paper, put the Employee table at the bottom. Add all the tables that depend on this table (that have EmployeeID as a single part referential key) as "branches" of that table, putting them above the Employee table on your paper. Next, consider whether there are tables that depend on these first-level branches. If so, add them similarly. The point of this exercise is that you cannot delete a row from a "parent" table unless all the related rows in all "child" tables have been removed FIRST. The diagram gives you a visual reference to see this. You can also do this in the Relationship Diagram for your system. I usually organize this left-to-right instead of bottom-to-top, but the important point is to add the "tree" organization to the diagram so it is readable as the hierarchy it represents, with parent on the left and child on the right. From this you can quickly derive the order in which tables must have rows deleted. You cannot leave a "child" as an orphan even though you intend to come back later and remove that child row. The rows of the child tables must be removed first or you have created an "orphan" which violates the referential integrity rules you've set up. Anyway, my guess is that this is one problem you must address. However, in no case could this be done in one delete query. It would be a series of delete queries, or you can use cascading as already described. Tom Ellison Microsoft Access MVP Ellison Enterprises - Your One Stop IT Experts On Fri, 30 Jul 2004 07:45:01 -0700, Shanin wrote: I was wondering how this would be done. Currently I have a database set up tracking employees and all kinds of training, disciplinary, errors, etc. They are all linked to the main Employees table by the EmployeeID with referential integrity on. The EmployeeID is or is part of the primary key in all of my other tables. On my Employees table I have a yes/no box for if they are separated. What I was wanting to know, since I don't want to delete the past employee basic info such as wage and reason for leaving, is how I can make one delete query that would delete all their info from these other tables, or at least a one button push to do it. I tried combining them all in one delete query but got the statement couldn't delete from specified tables. Currently I have a separate delete query for each table, which means I would have to run about 7 different queries to get the results I was wanting. Thanks |
#5
|
|||
|
|||
Delete Query
Dear Shanin:
That is clearer. What I posted applies to deleting dependeing rows from a set of related tables nonetheless. As far as having me write the queries, I don't have access to your table designs. I don't know specifically what you need. Apparently, you'd need to delete using a filter criterion of the EmployeeID selected in a form. This could be accomplished with saved queries that reference a control on the form then. Stringing them together in a macro or in VBA code would be rather simple if the relational diagram I explained is itself quite simple. But yes, I expect I could do a project like this without any trouble. However, the free help here is intended more to teach you how to do it yourself. Were you able to follow and apply what I posted before? Are there some specific questions you could ask about what you need to know to implement it? If so, I think you'll be able to get the help you need here just fine. Tom Ellison Microsoft Access MVP Ellison Enterprises - Your One Stop IT Experts On Fri, 30 Jul 2004 10:01:03 -0700, Shanin wrote: Actually I do have cascading deletes selected, but I don't plan on deleting the employee from the tblEmployees. We still want to be able to pull up information in order to do employment verification. Once they leave though, we know longer need to know all their trainings, etc, so I want to delete them from that. All the tables are only related to the tblEmployees and not to each other. Is there a macro or code you could write that would assign running all of those queries one after another? "Tom Ellison" wrote: Dear Shanin: A simple approach might work. If you set Cascade Delete in all the relationships in the same dialog where you set up the referential integrity, then when you delete the row from the Employee table all the dependent rows in the tables will be removed as well. There are some special circumstances in which this cannot be done, but it will tell you this when you try to implement the Cascade Deletes. However, you should take a bit of care. Use a copy of the database to set this up, and test it. Another approach is to write delete queries to be run when you remove an employee. These queries must start at the "top" of the "tree" of related tables. On a piece of paper, put the Employee table at the bottom. Add all the tables that depend on this table (that have EmployeeID as a single part referential key) as "branches" of that table, putting them above the Employee table on your paper. Next, consider whether there are tables that depend on these first-level branches. If so, add them similarly. The point of this exercise is that you cannot delete a row from a "parent" table unless all the related rows in all "child" tables have been removed FIRST. The diagram gives you a visual reference to see this. You can also do this in the Relationship Diagram for your system. I usually organize this left-to-right instead of bottom-to-top, but the important point is to add the "tree" organization to the diagram so it is readable as the hierarchy it represents, with parent on the left and child on the right. From this you can quickly derive the order in which tables must have rows deleted. You cannot leave a "child" as an orphan even though you intend to come back later and remove that child row. The rows of the child tables must be removed first or you have created an "orphan" which violates the referential integrity rules you've set up. Anyway, my guess is that this is one problem you must address. However, in no case could this be done in one delete query. It would be a series of delete queries, or you can use cascading as already described. Tom Ellison Microsoft Access MVP Ellison Enterprises - Your One Stop IT Experts On Fri, 30 Jul 2004 07:45:01 -0700, Shanin wrote: I was wondering how this would be done. Currently I have a database set up tracking employees and all kinds of training, disciplinary, errors, etc. They are all linked to the main Employees table by the EmployeeID with referential integrity on. The EmployeeID is or is part of the primary key in all of my other tables. On my Employees table I have a yes/no box for if they are separated. What I was wanting to know, since I don't want to delete the past employee basic info such as wage and reason for leaving, is how I can make one delete query that would delete all their info from these other tables, or at least a one button push to do it. I tried combining them all in one delete query but got the statement couldn't delete from specified tables. Currently I have a separate delete query for each table, which means I would have to run about 7 different queries to get the results I was wanting. Thanks |
#6
|
|||
|
|||
Delete Query
Dear Shanin:
That is clearer. What I posted applies to deleting dependeing rows from a set of related tables nonetheless. As far as having me write the queries, I don't have access to your table designs. I don't know specifically what you need. Apparently, you'd need to delete using a filter criterion of the EmployeeID selected in a form. This could be accomplished with saved queries that reference a control on the form then. Stringing them together in a macro or in VBA code would be rather simple if the relational diagram I explained is itself quite simple. But yes, I expect I could do a project like this without any trouble. However, the free help here is intended more to teach you how to do it yourself. Were you able to follow and apply what I posted before? Are there some specific questions you could ask about what you need to know to implement it? If so, I think you'll be able to get the help you need here just fine. Tom Ellison Microsoft Access MVP Ellison Enterprises - Your One Stop IT Experts On Fri, 30 Jul 2004 10:01:03 -0700, Shanin wrote: Actually I do have cascading deletes selected, but I don't plan on deleting the employee from the tblEmployees. We still want to be able to pull up information in order to do employment verification. Once they leave though, we know longer need to know all their trainings, etc, so I want to delete them from that. All the tables are only related to the tblEmployees and not to each other. Is there a macro or code you could write that would assign running all of those queries one after another? "Tom Ellison" wrote: Dear Shanin: A simple approach might work. If you set Cascade Delete in all the relationships in the same dialog where you set up the referential integrity, then when you delete the row from the Employee table all the dependent rows in the tables will be removed as well. There are some special circumstances in which this cannot be done, but it will tell you this when you try to implement the Cascade Deletes. However, you should take a bit of care. Use a copy of the database to set this up, and test it. Another approach is to write delete queries to be run when you remove an employee. These queries must start at the "top" of the "tree" of related tables. On a piece of paper, put the Employee table at the bottom. Add all the tables that depend on this table (that have EmployeeID as a single part referential key) as "branches" of that table, putting them above the Employee table on your paper. Next, consider whether there are tables that depend on these first-level branches. If so, add them similarly. The point of this exercise is that you cannot delete a row from a "parent" table unless all the related rows in all "child" tables have been removed FIRST. The diagram gives you a visual reference to see this. You can also do this in the Relationship Diagram for your system. I usually organize this left-to-right instead of bottom-to-top, but the important point is to add the "tree" organization to the diagram so it is readable as the hierarchy it represents, with parent on the left and child on the right. From this you can quickly derive the order in which tables must have rows deleted. You cannot leave a "child" as an orphan even though you intend to come back later and remove that child row. The rows of the child tables must be removed first or you have created an "orphan" which violates the referential integrity rules you've set up. Anyway, my guess is that this is one problem you must address. However, in no case could this be done in one delete query. It would be a series of delete queries, or you can use cascading as already described. Tom Ellison Microsoft Access MVP Ellison Enterprises - Your One Stop IT Experts On Fri, 30 Jul 2004 07:45:01 -0700, Shanin wrote: I was wondering how this would be done. Currently I have a database set up tracking employees and all kinds of training, disciplinary, errors, etc. They are all linked to the main Employees table by the EmployeeID with referential integrity on. The EmployeeID is or is part of the primary key in all of my other tables. On my Employees table I have a yes/no box for if they are separated. What I was wanting to know, since I don't want to delete the past employee basic info such as wage and reason for leaving, is how I can make one delete query that would delete all their info from these other tables, or at least a one button push to do it. I tried combining them all in one delete query but got the statement couldn't delete from specified tables. Currently I have a separate delete query for each table, which means I would have to run about 7 different queries to get the results I was wanting. Thanks |
#7
|
|||
|
|||
Delete Query
I just read how I wrote that response and I see I meant to use "one" instead of "you" when asking about writing the macro. I didn't intend for that to sound like I wanted you to write a code for me, I was just wondering if there was a way to do that which it sounds like there is. I haven't used Access since college so it's taking a while for all this stuff to come back to me.
"Tom Ellison" wrote: Dear Shanin: That is clearer. What I posted applies to deleting dependeing rows from a set of related tables nonetheless. As far as having me write the queries, I don't have access to your table designs. I don't know specifically what you need. Apparently, you'd need to delete using a filter criterion of the EmployeeID selected in a form. This could be accomplished with saved queries that reference a control on the form then. Stringing them together in a macro or in VBA code would be rather simple if the relational diagram I explained is itself quite simple. But yes, I expect I could do a project like this without any trouble. However, the free help here is intended more to teach you how to do it yourself. Were you able to follow and apply what I posted before? Are there some specific questions you could ask about what you need to know to implement it? If so, I think you'll be able to get the help you need here just fine. Tom Ellison Microsoft Access MVP Ellison Enterprises - Your One Stop IT Experts On Fri, 30 Jul 2004 10:01:03 -0700, Shanin wrote: Actually I do have cascading deletes selected, but I don't plan on deleting the employee from the tblEmployees. We still want to be able to pull up information in order to do employment verification. Once they leave though, we know longer need to know all their trainings, etc, so I want to delete them from that. All the tables are only related to the tblEmployees and not to each other. Is there a macro or code you could write that would assign running all of those queries one after another? "Tom Ellison" wrote: Dear Shanin: A simple approach might work. If you set Cascade Delete in all the relationships in the same dialog where you set up the referential integrity, then when you delete the row from the Employee table all the dependent rows in the tables will be removed as well. There are some special circumstances in which this cannot be done, but it will tell you this when you try to implement the Cascade Deletes. However, you should take a bit of care. Use a copy of the database to set this up, and test it. Another approach is to write delete queries to be run when you remove an employee. These queries must start at the "top" of the "tree" of related tables. On a piece of paper, put the Employee table at the bottom. Add all the tables that depend on this table (that have EmployeeID as a single part referential key) as "branches" of that table, putting them above the Employee table on your paper. Next, consider whether there are tables that depend on these first-level branches. If so, add them similarly. The point of this exercise is that you cannot delete a row from a "parent" table unless all the related rows in all "child" tables have been removed FIRST. The diagram gives you a visual reference to see this. You can also do this in the Relationship Diagram for your system. I usually organize this left-to-right instead of bottom-to-top, but the important point is to add the "tree" organization to the diagram so it is readable as the hierarchy it represents, with parent on the left and child on the right. From this you can quickly derive the order in which tables must have rows deleted. You cannot leave a "child" as an orphan even though you intend to come back later and remove that child row. The rows of the child tables must be removed first or you have created an "orphan" which violates the referential integrity rules you've set up. Anyway, my guess is that this is one problem you must address. However, in no case could this be done in one delete query. It would be a series of delete queries, or you can use cascading as already described. Tom Ellison Microsoft Access MVP Ellison Enterprises - Your One Stop IT Experts On Fri, 30 Jul 2004 07:45:01 -0700, Shanin wrote: I was wondering how this would be done. Currently I have a database set up tracking employees and all kinds of training, disciplinary, errors, etc. They are all linked to the main Employees table by the EmployeeID with referential integrity on. The EmployeeID is or is part of the primary key in all of my other tables. On my Employees table I have a yes/no box for if they are separated. What I was wanting to know, since I don't want to delete the past employee basic info such as wage and reason for leaving, is how I can make one delete query that would delete all their info from these other tables, or at least a one button push to do it. I tried combining them all in one delete query but got the statement couldn't delete from specified tables. Currently I have a separate delete query for each table, which means I would have to run about 7 different queries to get the results I was wanting. Thanks |
#8
|
|||
|
|||
Delete Query
I just read how I wrote that response and I see I meant to use "one" instead of "you" when asking about writing the macro. I didn't intend for that to sound like I wanted you to write a code for me, I was just wondering if there was a way to do that which it sounds like there is. I haven't used Access since college so it's taking a while for all this stuff to come back to me.
"Tom Ellison" wrote: Dear Shanin: That is clearer. What I posted applies to deleting dependeing rows from a set of related tables nonetheless. As far as having me write the queries, I don't have access to your table designs. I don't know specifically what you need. Apparently, you'd need to delete using a filter criterion of the EmployeeID selected in a form. This could be accomplished with saved queries that reference a control on the form then. Stringing them together in a macro or in VBA code would be rather simple if the relational diagram I explained is itself quite simple. But yes, I expect I could do a project like this without any trouble. However, the free help here is intended more to teach you how to do it yourself. Were you able to follow and apply what I posted before? Are there some specific questions you could ask about what you need to know to implement it? If so, I think you'll be able to get the help you need here just fine. Tom Ellison Microsoft Access MVP Ellison Enterprises - Your One Stop IT Experts On Fri, 30 Jul 2004 10:01:03 -0700, Shanin wrote: Actually I do have cascading deletes selected, but I don't plan on deleting the employee from the tblEmployees. We still want to be able to pull up information in order to do employment verification. Once they leave though, we know longer need to know all their trainings, etc, so I want to delete them from that. All the tables are only related to the tblEmployees and not to each other. Is there a macro or code you could write that would assign running all of those queries one after another? "Tom Ellison" wrote: Dear Shanin: A simple approach might work. If you set Cascade Delete in all the relationships in the same dialog where you set up the referential integrity, then when you delete the row from the Employee table all the dependent rows in the tables will be removed as well. There are some special circumstances in which this cannot be done, but it will tell you this when you try to implement the Cascade Deletes. However, you should take a bit of care. Use a copy of the database to set this up, and test it. Another approach is to write delete queries to be run when you remove an employee. These queries must start at the "top" of the "tree" of related tables. On a piece of paper, put the Employee table at the bottom. Add all the tables that depend on this table (that have EmployeeID as a single part referential key) as "branches" of that table, putting them above the Employee table on your paper. Next, consider whether there are tables that depend on these first-level branches. If so, add them similarly. The point of this exercise is that you cannot delete a row from a "parent" table unless all the related rows in all "child" tables have been removed FIRST. The diagram gives you a visual reference to see this. You can also do this in the Relationship Diagram for your system. I usually organize this left-to-right instead of bottom-to-top, but the important point is to add the "tree" organization to the diagram so it is readable as the hierarchy it represents, with parent on the left and child on the right. From this you can quickly derive the order in which tables must have rows deleted. You cannot leave a "child" as an orphan even though you intend to come back later and remove that child row. The rows of the child tables must be removed first or you have created an "orphan" which violates the referential integrity rules you've set up. Anyway, my guess is that this is one problem you must address. However, in no case could this be done in one delete query. It would be a series of delete queries, or you can use cascading as already described. Tom Ellison Microsoft Access MVP Ellison Enterprises - Your One Stop IT Experts On Fri, 30 Jul 2004 07:45:01 -0700, Shanin wrote: I was wondering how this would be done. Currently I have a database set up tracking employees and all kinds of training, disciplinary, errors, etc. They are all linked to the main Employees table by the EmployeeID with referential integrity on. The EmployeeID is or is part of the primary key in all of my other tables. On my Employees table I have a yes/no box for if they are separated. What I was wanting to know, since I don't want to delete the past employee basic info such as wage and reason for leaving, is how I can make one delete query that would delete all their info from these other tables, or at least a one button push to do it. I tried combining them all in one delete query but got the statement couldn't delete from specified tables. Currently I have a separate delete query for each table, which means I would have to run about 7 different queries to get the results I was wanting. Thanks |
#9
|
|||
|
|||
Delete Query
I just read how I wrote that response and I see I meant to use "one" instead of "you" when asking about writing the macro. I didn't intend for that to sound like I wanted you to write a code for me, I was just wondering if there was a way to do that which it sounds like there is. I haven't used Access since college so it's taking a while for all this stuff to come back to me.
"Tom Ellison" wrote: Dear Shanin: That is clearer. What I posted applies to deleting dependeing rows from a set of related tables nonetheless. As far as having me write the queries, I don't have access to your table designs. I don't know specifically what you need. Apparently, you'd need to delete using a filter criterion of the EmployeeID selected in a form. This could be accomplished with saved queries that reference a control on the form then. Stringing them together in a macro or in VBA code would be rather simple if the relational diagram I explained is itself quite simple. But yes, I expect I could do a project like this without any trouble. However, the free help here is intended more to teach you how to do it yourself. Were you able to follow and apply what I posted before? Are there some specific questions you could ask about what you need to know to implement it? If so, I think you'll be able to get the help you need here just fine. Tom Ellison Microsoft Access MVP Ellison Enterprises - Your One Stop IT Experts On Fri, 30 Jul 2004 10:01:03 -0700, Shanin wrote: Actually I do have cascading deletes selected, but I don't plan on deleting the employee from the tblEmployees. We still want to be able to pull up information in order to do employment verification. Once they leave though, we know longer need to know all their trainings, etc, so I want to delete them from that. All the tables are only related to the tblEmployees and not to each other. Is there a macro or code you could write that would assign running all of those queries one after another? "Tom Ellison" wrote: Dear Shanin: A simple approach might work. If you set Cascade Delete in all the relationships in the same dialog where you set up the referential integrity, then when you delete the row from the Employee table all the dependent rows in the tables will be removed as well. There are some special circumstances in which this cannot be done, but it will tell you this when you try to implement the Cascade Deletes. However, you should take a bit of care. Use a copy of the database to set this up, and test it. Another approach is to write delete queries to be run when you remove an employee. These queries must start at the "top" of the "tree" of related tables. On a piece of paper, put the Employee table at the bottom. Add all the tables that depend on this table (that have EmployeeID as a single part referential key) as "branches" of that table, putting them above the Employee table on your paper. Next, consider whether there are tables that depend on these first-level branches. If so, add them similarly. The point of this exercise is that you cannot delete a row from a "parent" table unless all the related rows in all "child" tables have been removed FIRST. The diagram gives you a visual reference to see this. You can also do this in the Relationship Diagram for your system. I usually organize this left-to-right instead of bottom-to-top, but the important point is to add the "tree" organization to the diagram so it is readable as the hierarchy it represents, with parent on the left and child on the right. From this you can quickly derive the order in which tables must have rows deleted. You cannot leave a "child" as an orphan even though you intend to come back later and remove that child row. The rows of the child tables must be removed first or you have created an "orphan" which violates the referential integrity rules you've set up. Anyway, my guess is that this is one problem you must address. However, in no case could this be done in one delete query. It would be a series of delete queries, or you can use cascading as already described. Tom Ellison Microsoft Access MVP Ellison Enterprises - Your One Stop IT Experts On Fri, 30 Jul 2004 07:45:01 -0700, Shanin wrote: I was wondering how this would be done. Currently I have a database set up tracking employees and all kinds of training, disciplinary, errors, etc. They are all linked to the main Employees table by the EmployeeID with referential integrity on. The EmployeeID is or is part of the primary key in all of my other tables. On my Employees table I have a yes/no box for if they are separated. What I was wanting to know, since I don't want to delete the past employee basic info such as wage and reason for leaving, is how I can make one delete query that would delete all their info from these other tables, or at least a one button push to do it. I tried combining them all in one delete query but got the statement couldn't delete from specified tables. Currently I have a separate delete query for each table, which means I would have to run about 7 different queries to get the results I was wanting. Thanks |
#10
|
|||
|
|||
Delete Query
Quick run down of my tables and relationships.
Main table: tblEmployees Supporting Tables: tblTrainings, tblPIDS, tblEvaluation, tblWage, tblMed/Chart, tblPositionProfile, tblResignation All the supporting tables are linked to the tblEmployees by the EmployeeID, and none of them are linked to each other, only the tblEmployees. I do have tables such as TrainingList and ResignationReasons, but they are basically there just for the purpose of being able to use a combo box in the form to choose which applies. I currently have the delete query setup taking the field [tblEmployees]![separated] and having the criteria set to yes. I then just have the asterik dragged down for the relating table so it deletes everything for those employees with separation as yes. I have one set up for each supporting table except tblResignation and tblWage, since we will still use that info. What selection in the Macro do you use to select and run a query. I would like to associate this Macro with a command button on a switchboard. "Tom Ellison" wrote: Dear Shanin: That is clearer. What I posted applies to deleting dependeing rows from a set of related tables nonetheless. As far as having me write the queries, I don't have access to your table designs. I don't know specifically what you need. Apparently, you'd need to delete using a filter criterion of the EmployeeID selected in a form. This could be accomplished with saved queries that reference a control on the form then. Stringing them together in a macro or in VBA code would be rather simple if the relational diagram I explained is itself quite simple. But yes, I expect I could do a project like this without any trouble. However, the free help here is intended more to teach you how to do it yourself. Were you able to follow and apply what I posted before? Are there some specific questions you could ask about what you need to know to implement it? If so, I think you'll be able to get the help you need here just fine. Tom Ellison Microsoft Access MVP Ellison Enterprises - Your One Stop IT Experts On Fri, 30 Jul 2004 10:01:03 -0700, Shanin wrote: Actually I do have cascading deletes selected, but I don't plan on deleting the employee from the tblEmployees. We still want to be able to pull up information in order to do employment verification. Once they leave though, we know longer need to know all their trainings, etc, so I want to delete them from that. All the tables are only related to the tblEmployees and not to each other. Is there a macro or code you could write that would assign running all of those queries one after another? "Tom Ellison" wrote: Dear Shanin: A simple approach might work. If you set Cascade Delete in all the relationships in the same dialog where you set up the referential integrity, then when you delete the row from the Employee table all the dependent rows in the tables will be removed as well. There are some special circumstances in which this cannot be done, but it will tell you this when you try to implement the Cascade Deletes. However, you should take a bit of care. Use a copy of the database to set this up, and test it. Another approach is to write delete queries to be run when you remove an employee. These queries must start at the "top" of the "tree" of related tables. On a piece of paper, put the Employee table at the bottom. Add all the tables that depend on this table (that have EmployeeID as a single part referential key) as "branches" of that table, putting them above the Employee table on your paper. Next, consider whether there are tables that depend on these first-level branches. If so, add them similarly. The point of this exercise is that you cannot delete a row from a "parent" table unless all the related rows in all "child" tables have been removed FIRST. The diagram gives you a visual reference to see this. You can also do this in the Relationship Diagram for your system. I usually organize this left-to-right instead of bottom-to-top, but the important point is to add the "tree" organization to the diagram so it is readable as the hierarchy it represents, with parent on the left and child on the right. From this you can quickly derive the order in which tables must have rows deleted. You cannot leave a "child" as an orphan even though you intend to come back later and remove that child row. The rows of the child tables must be removed first or you have created an "orphan" which violates the referential integrity rules you've set up. Anyway, my guess is that this is one problem you must address. However, in no case could this be done in one delete query. It would be a series of delete queries, or you can use cascading as already described. Tom Ellison Microsoft Access MVP Ellison Enterprises - Your One Stop IT Experts On Fri, 30 Jul 2004 07:45:01 -0700, Shanin wrote: I was wondering how this would be done. Currently I have a database set up tracking employees and all kinds of training, disciplinary, errors, etc. They are all linked to the main Employees table by the EmployeeID with referential integrity on. The EmployeeID is or is part of the primary key in all of my other tables. On my Employees table I have a yes/no box for if they are separated. What I was wanting to know, since I don't want to delete the past employee basic info such as wage and reason for leaving, is how I can make one delete query that would delete all their info from these other tables, or at least a one button push to do it. I tried combining them all in one delete query but got the statement couldn't delete from specified tables. Currently I have a separate delete query for each table, which means I would have to run about 7 different queries to get the results I was wanting. Thanks |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
INNER JOIN table to query - need to delete query! | Haydnw | Running & Setting Up Queries | 2 | July 29th, 2004 11:25 PM |
Delete query on external db? | NoSpamTakeSquareRootOfNumber | Running & Setting Up Queries | 1 | July 24th, 2004 02:35 PM |
Hidden files in Ms-Query cause ODBC connect errors or Query is wac | needyourhelp | General Discussion | 4 | July 12th, 2004 09:38 PM |
Delete Query cannot delete specified from specified tables... | bbarkman | General Discussion | 2 | July 8th, 2004 01:08 PM |
Delete Query based on result of select query | John Finch | Running & Setting Up Queries | 1 | July 2nd, 2004 05:47 PM |