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 |
#11
|
|||
|
|||
Delete Query Does Not
The second SQL statement creates an error that says you can't have an
aggregate function in a where clause as in (MAX(meals.LastVisit)Date()-730 The meals table contains the client master ID, a meals master ID, (those two create the combined key for the meals table), a list of date, one per row, of the dates the client has visited the food bank. There can be and often are multiple entries per client. The last row contains the last date a client came into the food bank. Soooo... if that date is two or more years old I want to delete the client and all records related to this client. "Tom Ellison" wrote in message ... Dear Ron: I'll make a guess at this. Perhaps we are misunderstanding your meaning of "LastVisit." If you have several rows in [meals] each with a different LastVisit, it is possible you want to delete the client and all meals only when the most recent LastVisit is prior to 730 days ago. Is that what you want? The query as I read it would delete a client and related meals if there is ANY visit more than 730 days ago. That would be a quite different thing. It could be: SELECT client.MasterID FROM client INNER JOIN meals ON meals.MasterID = client.MasterID WHERE MAX(meals.LastVisit) Date() - 730 GROUP BY client.MasterID I have presented this as a SELECT query not a DELETE. It shows what rows would be deleted if it were changed to be a DELETE query. By not deleting anything, testing is easier, as you can change it and run it again without having to restore the data every time it deletes wrongly. To see what you are doing further, build another query around this (temporarily): SELECT * FROM client INNER JOIN meals ON meals.MasterID = client.MasterID WHERE client.MasterID IN ( SELECT client.MasterID FROM client INNER JOIN meals ON meals.MasterID = client.MasterID WHERE MAX(meals.LastVisit) Date() - 730 GROUP BY client.MasterID) ORDER BY client.MasterID, meals.LastVisit Such a test is revealing, is it not? I would remark that this is a potentially dangerous query. If your system date is off, you could be removing rows you do not intend. That sounds like a bit of a risk to me. Tom Ellison "Ron Le Blanc" wrote in message ... This does not work. It selects many clients who have been in within two years. It does find a bunch of the "right" clients to delete but also includes what seems be be random other clients not meeting the two years since last time they came in. "John Vinson" wrote in message ... On Sat, 4 Feb 2006 23:15:16 -0500, "Ron Le Blanc" wrote: DELETE client.*, client.MasterID, meals.LastVisit, client.f_name, client.l_name FROM client INNER JOIN meals ON client.MasterID = meals.MasterID WHERE (((meals.LastVisit)Date()-730)); The error message says: "Cannot delete from table". You're trying too hard. Don't delete anything from the Meals table - cascading deletes will take care of that. DELETE client.* FROM client INNER JOIN meals ON client.MasterID = meals.MasterID WHERE (((meals.LastVisit)Date()-730)); John W. Vinson[MVP] |
#12
|
|||
|
|||
Delete Query Does Not
Dear Ron:
Maybe I should make excuses. Not. OK, this may be what is required: SELECT client.MasterID FROM client INNER JOIN meals ON meals.MasterID = client.MasterID HAVING MAX(meals.LastVisit) Date() - 730 GROUP BY client.MasterID Funny what you forget at times. I THINK this is what is needed. Please get back, OK? This is sanity check day for me! Tom Ellison "Ron Le Blanc" wrote in message ... The second SQL statement creates an error that says you can't have an aggregate function in a where clause as in (MAX(meals.LastVisit)Date()-730 The meals table contains the client master ID, a meals master ID, (those two create the combined key for the meals table), a list of date, one per row, of the dates the client has visited the food bank. There can be and often are multiple entries per client. The last row contains the last date a client came into the food bank. Soooo... if that date is two or more years old I want to delete the client and all records related to this client. "Tom Ellison" wrote in message ... Dear Ron: I'll make a guess at this. Perhaps we are misunderstanding your meaning of "LastVisit." If you have several rows in [meals] each with a different LastVisit, it is possible you want to delete the client and all meals only when the most recent LastVisit is prior to 730 days ago. Is that what you want? The query as I read it would delete a client and related meals if there is ANY visit more than 730 days ago. That would be a quite different thing. It could be: SELECT client.MasterID FROM client INNER JOIN meals ON meals.MasterID = client.MasterID WHERE MAX(meals.LastVisit) Date() - 730 GROUP BY client.MasterID I have presented this as a SELECT query not a DELETE. It shows what rows would be deleted if it were changed to be a DELETE query. By not deleting anything, testing is easier, as you can change it and run it again without having to restore the data every time it deletes wrongly. To see what you are doing further, build another query around this (temporarily): SELECT * FROM client INNER JOIN meals ON meals.MasterID = client.MasterID WHERE client.MasterID IN ( SELECT client.MasterID FROM client INNER JOIN meals ON meals.MasterID = client.MasterID WHERE MAX(meals.LastVisit) Date() - 730 GROUP BY client.MasterID) ORDER BY client.MasterID, meals.LastVisit Such a test is revealing, is it not? I would remark that this is a potentially dangerous query. If your system date is off, you could be removing rows you do not intend. That sounds like a bit of a risk to me. Tom Ellison "Ron Le Blanc" wrote in message ... This does not work. It selects many clients who have been in within two years. It does find a bunch of the "right" clients to delete but also includes what seems be be random other clients not meeting the two years since last time they came in. "John Vinson" wrote in message ... On Sat, 4 Feb 2006 23:15:16 -0500, "Ron Le Blanc" wrote: DELETE client.*, client.MasterID, meals.LastVisit, client.f_name, client.l_name FROM client INNER JOIN meals ON client.MasterID = meals.MasterID WHERE (((meals.LastVisit)Date()-730)); The error message says: "Cannot delete from table". You're trying too hard. Don't delete anything from the Meals table - cascading deletes will take care of that. DELETE client.* FROM client INNER JOIN meals ON client.MasterID = meals.MasterID WHERE (((meals.LastVisit)Date()-730)); John W. Vinson[MVP] |
#13
|
|||
|
|||
Delete Query Does Not
Dear Tom,
Syntax error in HAVING clause Sigh Take care, "Tom Ellison" wrote in message ... Dear Ron: Maybe I should make excuses. Not. OK, this may be what is required: SELECT client.MasterID FROM client INNER JOIN meals ON meals.MasterID = client.MasterID HAVING MAX(meals.LastVisit) Date() - 730 GROUP BY client.MasterID Funny what you forget at times. I THINK this is what is needed. Please get back, OK? This is sanity check day for me! Tom Ellison "Ron Le Blanc" wrote in message ... The second SQL statement creates an error that says you can't have an aggregate function in a where clause as in (MAX(meals.LastVisit)Date()-730 The meals table contains the client master ID, a meals master ID, (those two create the combined key for the meals table), a list of date, one per row, of the dates the client has visited the food bank. There can be and often are multiple entries per client. The last row contains the last date a client came into the food bank. Soooo... if that date is two or more years old I want to delete the client and all records related to this client. "Tom Ellison" wrote in message ... Dear Ron: I'll make a guess at this. Perhaps we are misunderstanding your meaning of "LastVisit." If you have several rows in [meals] each with a different LastVisit, it is possible you want to delete the client and all meals only when the most recent LastVisit is prior to 730 days ago. Is that what you want? The query as I read it would delete a client and related meals if there is ANY visit more than 730 days ago. That would be a quite different thing. It could be: SELECT client.MasterID FROM client INNER JOIN meals ON meals.MasterID = client.MasterID WHERE MAX(meals.LastVisit) Date() - 730 GROUP BY client.MasterID I have presented this as a SELECT query not a DELETE. It shows what rows would be deleted if it were changed to be a DELETE query. By not deleting anything, testing is easier, as you can change it and run it again without having to restore the data every time it deletes wrongly. To see what you are doing further, build another query around this (temporarily): SELECT * FROM client INNER JOIN meals ON meals.MasterID = client.MasterID WHERE client.MasterID IN ( SELECT client.MasterID FROM client INNER JOIN meals ON meals.MasterID = client.MasterID WHERE MAX(meals.LastVisit) Date() - 730 GROUP BY client.MasterID) ORDER BY client.MasterID, meals.LastVisit Such a test is revealing, is it not? I would remark that this is a potentially dangerous query. If your system date is off, you could be removing rows you do not intend. That sounds like a bit of a risk to me. Tom Ellison "Ron Le Blanc" wrote in message ... This does not work. It selects many clients who have been in within two years. It does find a bunch of the "right" clients to delete but also includes what seems be be random other clients not meeting the two years since last time they came in. "John Vinson" wrote in message ... On Sat, 4 Feb 2006 23:15:16 -0500, "Ron Le Blanc" wrote: DELETE client.*, client.MasterID, meals.LastVisit, client.f_name, client.l_name FROM client INNER JOIN meals ON client.MasterID = meals.MasterID WHERE (((meals.LastVisit)Date()-730)); The error message says: "Cannot delete from table". You're trying too hard. Don't delete anything from the Meals table - cascading deletes will take care of that. DELETE client.* FROM client INNER JOIN meals ON client.MasterID = meals.MasterID WHERE (((meals.LastVisit)Date()-730)); John W. Vinson[MVP] |
#14
|
|||
|
|||
Delete Query Does Not
Dear Ron:
Well, I used to be able to write queries. Actually, it's easy enough when you have the database in front of you. I think I had the order wrong, didn't I? SELECT client.MasterID FROM client INNER JOIN meals ON meals.MasterID = client.MasterID GROUP BY client.MasterID HAVING MAX(meals.LastVisit) Date() - 730 I've been on my own problem almost straight for 48 hours. Please forgive. I'm too tired, or old, or something. Tom Ellison "Ron Le Blanc" wrote in message ... Dear Tom, Syntax error in HAVING clause Sigh Take care, "Tom Ellison" wrote in message ... Dear Ron: Maybe I should make excuses. Not. OK, this may be what is required: SELECT client.MasterID FROM client INNER JOIN meals ON meals.MasterID = client.MasterID HAVING MAX(meals.LastVisit) Date() - 730 GROUP BY client.MasterID Funny what you forget at times. I THINK this is what is needed. Please get back, OK? This is sanity check day for me! Tom Ellison "Ron Le Blanc" wrote in message ... The second SQL statement creates an error that says you can't have an aggregate function in a where clause as in (MAX(meals.LastVisit)Date()-730 The meals table contains the client master ID, a meals master ID, (those two create the combined key for the meals table), a list of date, one per row, of the dates the client has visited the food bank. There can be and often are multiple entries per client. The last row contains the last date a client came into the food bank. Soooo... if that date is two or more years old I want to delete the client and all records related to this client. "Tom Ellison" wrote in message ... Dear Ron: I'll make a guess at this. Perhaps we are misunderstanding your meaning of "LastVisit." If you have several rows in [meals] each with a different LastVisit, it is possible you want to delete the client and all meals only when the most recent LastVisit is prior to 730 days ago. Is that what you want? The query as I read it would delete a client and related meals if there is ANY visit more than 730 days ago. That would be a quite different thing. It could be: SELECT client.MasterID FROM client INNER JOIN meals ON meals.MasterID = client.MasterID WHERE MAX(meals.LastVisit) Date() - 730 GROUP BY client.MasterID I have presented this as a SELECT query not a DELETE. It shows what rows would be deleted if it were changed to be a DELETE query. By not deleting anything, testing is easier, as you can change it and run it again without having to restore the data every time it deletes wrongly. To see what you are doing further, build another query around this (temporarily): SELECT * FROM client INNER JOIN meals ON meals.MasterID = client.MasterID WHERE client.MasterID IN ( SELECT client.MasterID FROM client INNER JOIN meals ON meals.MasterID = client.MasterID WHERE MAX(meals.LastVisit) Date() - 730 GROUP BY client.MasterID) ORDER BY client.MasterID, meals.LastVisit Such a test is revealing, is it not? I would remark that this is a potentially dangerous query. If your system date is off, you could be removing rows you do not intend. That sounds like a bit of a risk to me. Tom Ellison "Ron Le Blanc" wrote in message ... This does not work. It selects many clients who have been in within two years. It does find a bunch of the "right" clients to delete but also includes what seems be be random other clients not meeting the two years since last time they came in. "John Vinson" wrote in message ... On Sat, 4 Feb 2006 23:15:16 -0500, "Ron Le Blanc" wrote: DELETE client.*, client.MasterID, meals.LastVisit, client.f_name, client.l_name FROM client INNER JOIN meals ON client.MasterID = meals.MasterID WHERE (((meals.LastVisit)Date()-730)); The error message says: "Cannot delete from table". You're trying too hard. Don't delete anything from the Meals table - cascading deletes will take care of that. DELETE client.* FROM client INNER JOIN meals ON client.MasterID = meals.MasterID WHERE (((meals.LastVisit)Date()-730)); John W. Vinson[MVP] |
#15
|
|||
|
|||
Delete Query Does Not
Dear Tom,
First I thank you for all the help you have given me thus far. This is a nasty problem! The last SQL query you gave me (below) runs but gives the following error message: "Specify the table containing the records you want to delete" If I make it a Select query it selects the correct clients!! But a Delete query give the above error message. One more tiny tweak maybe? Thanks and take care... "Tom Ellison" wrote in message ... Dear Ron: Well, I used to be able to write queries. Actually, it's easy enough when you have the database in front of you. I think I had the order wrong, didn't I? SELECT client.MasterID FROM client INNER JOIN meals ON meals.MasterID = client.MasterID GROUP BY client.MasterID HAVING MAX(meals.LastVisit) Date() - 730 I've been on my own problem almost straight for 48 hours. Please forgive. I'm too tired, or old, or something. Tom Ellison "Ron Le Blanc" wrote in message ... Dear Tom, Syntax error in HAVING clause Sigh Take care, "Tom Ellison" wrote in message ... Dear Ron: Maybe I should make excuses. Not. OK, this may be what is required: SELECT client.MasterID FROM client INNER JOIN meals ON meals.MasterID = client.MasterID HAVING MAX(meals.LastVisit) Date() - 730 GROUP BY client.MasterID Funny what you forget at times. I THINK this is what is needed. Please get back, OK? This is sanity check day for me! Tom Ellison "Ron Le Blanc" wrote in message ... The second SQL statement creates an error that says you can't have an aggregate function in a where clause as in (MAX(meals.LastVisit)Date()-730 The meals table contains the client master ID, a meals master ID, (those two create the combined key for the meals table), a list of date, one per row, of the dates the client has visited the food bank. There can be and often are multiple entries per client. The last row contains the last date a client came into the food bank. Soooo... if that date is two or more years old I want to delete the client and all records related to this client. "Tom Ellison" wrote in message ... Dear Ron: I'll make a guess at this. Perhaps we are misunderstanding your meaning of "LastVisit." If you have several rows in [meals] each with a different LastVisit, it is possible you want to delete the client and all meals only when the most recent LastVisit is prior to 730 days ago. Is that what you want? The query as I read it would delete a client and related meals if there is ANY visit more than 730 days ago. That would be a quite different thing. It could be: SELECT client.MasterID FROM client INNER JOIN meals ON meals.MasterID = client.MasterID WHERE MAX(meals.LastVisit) Date() - 730 GROUP BY client.MasterID I have presented this as a SELECT query not a DELETE. It shows what rows would be deleted if it were changed to be a DELETE query. By not deleting anything, testing is easier, as you can change it and run it again without having to restore the data every time it deletes wrongly. To see what you are doing further, build another query around this (temporarily): SELECT * FROM client INNER JOIN meals ON meals.MasterID = client.MasterID WHERE client.MasterID IN ( SELECT client.MasterID FROM client INNER JOIN meals ON meals.MasterID = client.MasterID WHERE MAX(meals.LastVisit) Date() - 730 GROUP BY client.MasterID) ORDER BY client.MasterID, meals.LastVisit Such a test is revealing, is it not? I would remark that this is a potentially dangerous query. If your system date is off, you could be removing rows you do not intend. That sounds like a bit of a risk to me. Tom Ellison "Ron Le Blanc" wrote in message ... This does not work. It selects many clients who have been in within two years. It does find a bunch of the "right" clients to delete but also includes what seems be be random other clients not meeting the two years since last time they came in. "John Vinson" wrote in message ... On Sat, 4 Feb 2006 23:15:16 -0500, "Ron Le Blanc" wrote: DELETE client.*, client.MasterID, meals.LastVisit, client.f_name, client.l_name FROM client INNER JOIN meals ON client.MasterID = meals.MasterID WHERE (((meals.LastVisit)Date()-730)); The error message says: "Cannot delete from table". You're trying too hard. Don't delete anything from the Meals table - cascading deletes will take care of that. DELETE client.* FROM client INNER JOIN meals ON client.MasterID = meals.MasterID WHERE (((meals.LastVisit)Date()-730)); John W. Vinson[MVP] |
#16
|
|||
|
|||
Delete Query Does Not
Dear Ron:
It's been years since I did this, but it seems to me that, if you take the working SELECT query, open it in design view, right click in the empty portion of the top portion of the windos, select Query Type, and then select Delete Query, it will transform your SELECT query into an equivalent DELETE query. I beleive what it would say is: DELETE client.* FROM client INNER JOIN meals ON meals.MasterID = client.MasterID GROUP BY client.MasterID HAVING MAX(meals.LastVisit) Date() - 730 I'm not sure of this query either, but I'm pretty sure using the Design View capacities to create it will be reliable. Of course, always make a backup of your data before running anything I suggest. : ) Or anything else new and uncertain you are just testing for the first time. Or the second time. Did I mention before that I'm concerned about the use of Date() in this query. If you system clock is somehow accidentally set forward to 2/9/2008, it will delete all your data. What is deleted here depends on the system clock being set correctly. Having the user enter the desired date might be better, but that too is subject to mis-typing. The best thing I can think of at the moment is to rank your data by date and choose a date from that which assures you the most recent 200, 500, 10000, or whatever number of records are most recent will not be deleted. Test the date for your deletions against that. It must be a date prior to your 500 most recent records. A good question might be to consider for yourself why it is so necessary to delete them at all. You could use a similar query mechanism to just disregard them for reporting or forms purposes, and let them be. This avoids the possible tragic loss of historic information, especially if your backup scheme is not highly reliable. If at some future time you want to change the number of days from 730 to what seems better at that time, everything would adjust to the new limit immediately. If you set it to 1000 days, you'd instantly have 270 days more information show up (assuming there is some in your table). If table size (the 2 GB max for Jet files, for example) or performance is your goal, then archiving them first to a separate database would be an option. Ask yourself, if all the data you have accumulated in this table were to disappear due to such a malfunction, what amount of time and money would it cost? Now are you motivated to protect it thoroughly? Well, I've given you the rope to hang yourself. : ) What you do with it is up to you! Tom Ellison "Ron Le Blanc" wrote in message ... Dear Tom, First I thank you for all the help you have given me thus far. This is a nasty problem! The last SQL query you gave me (below) runs but gives the following error message: "Specify the table containing the records you want to delete" If I make it a Select query it selects the correct clients!! But a Delete query give the above error message. One more tiny tweak maybe? Thanks and take care... "Tom Ellison" wrote in message ... Dear Ron: Well, I used to be able to write queries. Actually, it's easy enough when you have the database in front of you. I think I had the order wrong, didn't I? SELECT client.MasterID FROM client INNER JOIN meals ON meals.MasterID = client.MasterID GROUP BY client.MasterID HAVING MAX(meals.LastVisit) Date() - 730 I've been on my own problem almost straight for 48 hours. Please forgive. I'm too tired, or old, or something. Tom Ellison "Ron Le Blanc" wrote in message ... Dear Tom, Syntax error in HAVING clause Sigh Take care, "Tom Ellison" wrote in message ... Dear Ron: Maybe I should make excuses. Not. OK, this may be what is required: SELECT client.MasterID FROM client INNER JOIN meals ON meals.MasterID = client.MasterID HAVING MAX(meals.LastVisit) Date() - 730 GROUP BY client.MasterID Funny what you forget at times. I THINK this is what is needed. Please get back, OK? This is sanity check day for me! Tom Ellison "Ron Le Blanc" wrote in message ... The second SQL statement creates an error that says you can't have an aggregate function in a where clause as in (MAX(meals.LastVisit)Date()-730 The meals table contains the client master ID, a meals master ID, (those two create the combined key for the meals table), a list of date, one per row, of the dates the client has visited the food bank. There can be and often are multiple entries per client. The last row contains the last date a client came into the food bank. Soooo... if that date is two or more years old I want to delete the client and all records related to this client. "Tom Ellison" wrote in message ... Dear Ron: I'll make a guess at this. Perhaps we are misunderstanding your meaning of "LastVisit." If you have several rows in [meals] each with a different LastVisit, it is possible you want to delete the client and all meals only when the most recent LastVisit is prior to 730 days ago. Is that what you want? The query as I read it would delete a client and related meals if there is ANY visit more than 730 days ago. That would be a quite different thing. It could be: SELECT client.MasterID FROM client INNER JOIN meals ON meals.MasterID = client.MasterID WHERE MAX(meals.LastVisit) Date() - 730 GROUP BY client.MasterID I have presented this as a SELECT query not a DELETE. It shows what rows would be deleted if it were changed to be a DELETE query. By not deleting anything, testing is easier, as you can change it and run it again without having to restore the data every time it deletes wrongly. To see what you are doing further, build another query around this (temporarily): SELECT * FROM client INNER JOIN meals ON meals.MasterID = client.MasterID WHERE client.MasterID IN ( SELECT client.MasterID FROM client INNER JOIN meals ON meals.MasterID = client.MasterID WHERE MAX(meals.LastVisit) Date() - 730 GROUP BY client.MasterID) ORDER BY client.MasterID, meals.LastVisit Such a test is revealing, is it not? I would remark that this is a potentially dangerous query. If your system date is off, you could be removing rows you do not intend. That sounds like a bit of a risk to me. Tom Ellison "Ron Le Blanc" wrote in message ... This does not work. It selects many clients who have been in within two years. It does find a bunch of the "right" clients to delete but also includes what seems be be random other clients not meeting the two years since last time they came in. "John Vinson" wrote in message ... On Sat, 4 Feb 2006 23:15:16 -0500, "Ron Le Blanc" wrote: DELETE client.*, client.MasterID, meals.LastVisit, client.f_name, client.l_name FROM client INNER JOIN meals ON client.MasterID = meals.MasterID WHERE (((meals.LastVisit)Date()-730)); The error message says: "Cannot delete from table". You're trying too hard. Don't delete anything from the Meals table - cascading deletes will take care of that. DELETE client.* FROM client INNER JOIN meals ON client.MasterID = meals.MasterID WHERE (((meals.LastVisit)Date()-730)); John W. Vinson[MVP] |
#17
|
|||
|
|||
Delete Query Does Not
Now, in a spelling bee, I would never spell it "beleive", but when I let my
fingers to the walking (TM) strange things happen. So, please, make that "believe" OK? Tom Ellison "Tom Ellison" wrote in message ... Dear Ron: It's been years since I did this, but it seems to me that, if you take the working SELECT query, open it in design view, right click in the empty portion of the top portion of the windos, select Query Type, and then select Delete Query, it will transform your SELECT query into an equivalent DELETE query. I beleive what it would say is: DELETE client.* FROM client INNER JOIN meals ON meals.MasterID = client.MasterID GROUP BY client.MasterID HAVING MAX(meals.LastVisit) Date() - 730 I'm not sure of this query either, but I'm pretty sure using the Design View capacities to create it will be reliable. Of course, always make a backup of your data before running anything I suggest. : ) Or anything else new and uncertain you are just testing for the first time. Or the second time. Did I mention before that I'm concerned about the use of Date() in this query. If you system clock is somehow accidentally set forward to 2/9/2008, it will delete all your data. What is deleted here depends on the system clock being set correctly. Having the user enter the desired date might be better, but that too is subject to mis-typing. The best thing I can think of at the moment is to rank your data by date and choose a date from that which assures you the most recent 200, 500, 10000, or whatever number of records are most recent will not be deleted. Test the date for your deletions against that. It must be a date prior to your 500 most recent records. A good question might be to consider for yourself why it is so necessary to delete them at all. You could use a similar query mechanism to just disregard them for reporting or forms purposes, and let them be. This avoids the possible tragic loss of historic information, especially if your backup scheme is not highly reliable. If at some future time you want to change the number of days from 730 to what seems better at that time, everything would adjust to the new limit immediately. If you set it to 1000 days, you'd instantly have 270 days more information show up (assuming there is some in your table). If table size (the 2 GB max for Jet files, for example) or performance is your goal, then archiving them first to a separate database would be an option. Ask yourself, if all the data you have accumulated in this table were to disappear due to such a malfunction, what amount of time and money would it cost? Now are you motivated to protect it thoroughly? Well, I've given you the rope to hang yourself. : ) What you do with it is up to you! Tom Ellison "Ron Le Blanc" wrote in message ... Dear Tom, First I thank you for all the help you have given me thus far. This is a nasty problem! The last SQL query you gave me (below) runs but gives the following error message: "Specify the table containing the records you want to delete" If I make it a Select query it selects the correct clients!! But a Delete query give the above error message. One more tiny tweak maybe? Thanks and take care... "Tom Ellison" wrote in message ... Dear Ron: Well, I used to be able to write queries. Actually, it's easy enough when you have the database in front of you. I think I had the order wrong, didn't I? SELECT client.MasterID FROM client INNER JOIN meals ON meals.MasterID = client.MasterID GROUP BY client.MasterID HAVING MAX(meals.LastVisit) Date() - 730 I've been on my own problem almost straight for 48 hours. Please forgive. I'm too tired, or old, or something. Tom Ellison "Ron Le Blanc" wrote in message ... Dear Tom, Syntax error in HAVING clause Sigh Take care, "Tom Ellison" wrote in message ... Dear Ron: Maybe I should make excuses. Not. OK, this may be what is required: SELECT client.MasterID FROM client INNER JOIN meals ON meals.MasterID = client.MasterID HAVING MAX(meals.LastVisit) Date() - 730 GROUP BY client.MasterID Funny what you forget at times. I THINK this is what is needed. Please get back, OK? This is sanity check day for me! Tom Ellison "Ron Le Blanc" wrote in message ... The second SQL statement creates an error that says you can't have an aggregate function in a where clause as in (MAX(meals.LastVisit)Date()-730 The meals table contains the client master ID, a meals master ID, (those two create the combined key for the meals table), a list of date, one per row, of the dates the client has visited the food bank. There can be and often are multiple entries per client. The last row contains the last date a client came into the food bank. Soooo... if that date is two or more years old I want to delete the client and all records related to this client. "Tom Ellison" wrote in message ... Dear Ron: I'll make a guess at this. Perhaps we are misunderstanding your meaning of "LastVisit." If you have several rows in [meals] each with a different LastVisit, it is possible you want to delete the client and all meals only when the most recent LastVisit is prior to 730 days ago. Is that what you want? The query as I read it would delete a client and related meals if there is ANY visit more than 730 days ago. That would be a quite different thing. It could be: SELECT client.MasterID FROM client INNER JOIN meals ON meals.MasterID = client.MasterID WHERE MAX(meals.LastVisit) Date() - 730 GROUP BY client.MasterID I have presented this as a SELECT query not a DELETE. It shows what rows would be deleted if it were changed to be a DELETE query. By not deleting anything, testing is easier, as you can change it and run it again without having to restore the data every time it deletes wrongly. To see what you are doing further, build another query around this (temporarily): SELECT * FROM client INNER JOIN meals ON meals.MasterID = client.MasterID WHERE client.MasterID IN ( SELECT client.MasterID FROM client INNER JOIN meals ON meals.MasterID = client.MasterID WHERE MAX(meals.LastVisit) Date() - 730 GROUP BY client.MasterID) ORDER BY client.MasterID, meals.LastVisit Such a test is revealing, is it not? I would remark that this is a potentially dangerous query. If your system date is off, you could be removing rows you do not intend. That sounds like a bit of a risk to me. Tom Ellison "Ron Le Blanc" wrote in message ... This does not work. It selects many clients who have been in within two years. It does find a bunch of the "right" clients to delete but also includes what seems be be random other clients not meeting the two years since last time they came in. "John Vinson" wrote in message ... On Sat, 4 Feb 2006 23:15:16 -0500, "Ron Le Blanc" wrote: DELETE client.*, client.MasterID, meals.LastVisit, client.f_name, client.l_name FROM client INNER JOIN meals ON client.MasterID = meals.MasterID WHERE (((meals.LastVisit)Date()-730)); The error message says: "Cannot delete from table". You're trying too hard. Don't delete anything from the Meals table - cascading deletes will take care of that. DELETE client.* FROM client INNER JOIN meals ON client.MasterID = meals.MasterID WHERE (((meals.LastVisit)Date()-730)); John W. Vinson[MVP] |
#18
|
|||
|
|||
Delete Query Does Not
Sigh
Dear Tom, Almost but no cigar. When I try to run the SQL query below it gives me the following error: Cannot group on fields selected with '*' (client) If it would be easier I could zip up the files and send them to you. Ah, only thing is you would have to agree that you never "look" at the actual data as these are real Food Bank Clients who expect there data to be held privately. Or I could send you the relationship diagrams, or both. Whatever makes it easier for you. Thanks for all your help!! Take care, "Tom Ellison" wrote in message ... Dear Ron: It's been years since I did this, but it seems to me that, if you take the working SELECT query, open it in design view, right click in the empty portion of the top portion of the windos, select Query Type, and then select Delete Query, it will transform your SELECT query into an equivalent DELETE query. I beleive what it would say is: DELETE client.* FROM client INNER JOIN meals ON meals.MasterID = client.MasterID GROUP BY client.MasterID HAVING MAX(meals.LastVisit) Date() - 730 I'm not sure of this query either, but I'm pretty sure using the Design View capacities to create it will be reliable. Of course, always make a backup of your data before running anything I suggest. : ) Or anything else new and uncertain you are just testing for the first time. Or the second time. Did I mention before that I'm concerned about the use of Date() in this query. If you system clock is somehow accidentally set forward to 2/9/2008, it will delete all your data. What is deleted here depends on the system clock being set correctly. Having the user enter the desired date might be better, but that too is subject to mis-typing. The best thing I can think of at the moment is to rank your data by date and choose a date from that which assures you the most recent 200, 500, 10000, or whatever number of records are most recent will not be deleted. Test the date for your deletions against that. It must be a date prior to your 500 most recent records. A good question might be to consider for yourself why it is so necessary to delete them at all. You could use a similar query mechanism to just disregard them for reporting or forms purposes, and let them be. This avoids the possible tragic loss of historic information, especially if your backup scheme is not highly reliable. If at some future time you want to change the number of days from 730 to what seems better at that time, everything would adjust to the new limit immediately. If you set it to 1000 days, you'd instantly have 270 days more information show up (assuming there is some in your table). If table size (the 2 GB max for Jet files, for example) or performance is your goal, then archiving them first to a separate database would be an option. Ask yourself, if all the data you have accumulated in this table were to disappear due to such a malfunction, what amount of time and money would it cost? Now are you motivated to protect it thoroughly? Well, I've given you the rope to hang yourself. : ) What you do with it is up to you! Tom Ellison "Ron Le Blanc" wrote in message ... Dear Tom, First I thank you for all the help you have given me thus far. This is a nasty problem! The last SQL query you gave me (below) runs but gives the following error message: "Specify the table containing the records you want to delete" If I make it a Select query it selects the correct clients!! But a Delete query give the above error message. One more tiny tweak maybe? Thanks and take care... "Tom Ellison" wrote in message ... Dear Ron: Well, I used to be able to write queries. Actually, it's easy enough when you have the database in front of you. I think I had the order wrong, didn't I? SELECT client.MasterID FROM client INNER JOIN meals ON meals.MasterID = client.MasterID GROUP BY client.MasterID HAVING MAX(meals.LastVisit) Date() - 730 I've been on my own problem almost straight for 48 hours. Please forgive. I'm too tired, or old, or something. Tom Ellison "Ron Le Blanc" wrote in message ... Dear Tom, Syntax error in HAVING clause Sigh Take care, "Tom Ellison" wrote in message ... Dear Ron: Maybe I should make excuses. Not. OK, this may be what is required: SELECT client.MasterID FROM client INNER JOIN meals ON meals.MasterID = client.MasterID HAVING MAX(meals.LastVisit) Date() - 730 GROUP BY client.MasterID Funny what you forget at times. I THINK this is what is needed. Please get back, OK? This is sanity check day for me! Tom Ellison "Ron Le Blanc" wrote in message ... The second SQL statement creates an error that says you can't have an aggregate function in a where clause as in (MAX(meals.LastVisit)Date()-730 The meals table contains the client master ID, a meals master ID, (those two create the combined key for the meals table), a list of date, one per row, of the dates the client has visited the food bank. There can be and often are multiple entries per client. The last row contains the last date a client came into the food bank. Soooo... if that date is two or more years old I want to delete the client and all records related to this client. "Tom Ellison" wrote in message ... Dear Ron: I'll make a guess at this. Perhaps we are misunderstanding your meaning of "LastVisit." If you have several rows in [meals] each with a different LastVisit, it is possible you want to delete the client and all meals only when the most recent LastVisit is prior to 730 days ago. Is that what you want? The query as I read it would delete a client and related meals if there is ANY visit more than 730 days ago. That would be a quite different thing. It could be: SELECT client.MasterID FROM client INNER JOIN meals ON meals.MasterID = client.MasterID WHERE MAX(meals.LastVisit) Date() - 730 GROUP BY client.MasterID I have presented this as a SELECT query not a DELETE. It shows what rows would be deleted if it were changed to be a DELETE query. By not deleting anything, testing is easier, as you can change it and run it again without having to restore the data every time it deletes wrongly. To see what you are doing further, build another query around this (temporarily): SELECT * FROM client INNER JOIN meals ON meals.MasterID = client.MasterID WHERE client.MasterID IN ( SELECT client.MasterID FROM client INNER JOIN meals ON meals.MasterID = client.MasterID WHERE MAX(meals.LastVisit) Date() - 730 GROUP BY client.MasterID) ORDER BY client.MasterID, meals.LastVisit Such a test is revealing, is it not? I would remark that this is a potentially dangerous query. If your system date is off, you could be removing rows you do not intend. That sounds like a bit of a risk to me. Tom Ellison "Ron Le Blanc" wrote in message ... This does not work. It selects many clients who have been in within two years. It does find a bunch of the "right" clients to delete but also includes what seems be be random other clients not meeting the two years since last time they came in. "John Vinson" wrote in message ... On Sat, 4 Feb 2006 23:15:16 -0500, "Ron Le Blanc" wrote: DELETE client.*, client.MasterID, meals.LastVisit, client.f_name, client.l_name FROM client INNER JOIN meals ON client.MasterID = meals.MasterID WHERE (((meals.LastVisit)Date()-730)); The error message says: "Cannot delete from table". You're trying too hard. Don't delete anything from the Meals table - cascading deletes will take care of that. DELETE client.* FROM client INNER JOIN meals ON client.MasterID = meals.MasterID WHERE (((meals.LastVisit)Date()-730)); John W. Vinson[MVP] |
#19
|
|||
|
|||
Delete Query Does Not
Dear Ron:
Did you try the method of changing the query in the Design View, as I suggested? Or does this fix it: DELETE client.MasterID FROM client INNER JOIN meals ON meals.MasterID = client.MasterID GROUP BY client.MasterID HAVING MAX(meals.LastVisit) Date() - 730 Sorry I missed that. Tom Ellison "Ron Le Blanc" wrote in message ... Sigh Dear Tom, Almost but no cigar. When I try to run the SQL query below it gives me the following error: Cannot group on fields selected with '*' (client) If it would be easier I could zip up the files and send them to you. Ah, only thing is you would have to agree that you never "look" at the actual data as these are real Food Bank Clients who expect there data to be held privately. Or I could send you the relationship diagrams, or both. Whatever makes it easier for you. Thanks for all your help!! Take care, "Tom Ellison" wrote in message ... Dear Ron: It's been years since I did this, but it seems to me that, if you take the working SELECT query, open it in design view, right click in the empty portion of the top portion of the windos, select Query Type, and then select Delete Query, it will transform your SELECT query into an equivalent DELETE query. I beleive what it would say is: DELETE client.* FROM client INNER JOIN meals ON meals.MasterID = client.MasterID GROUP BY client.MasterID HAVING MAX(meals.LastVisit) Date() - 730 I'm not sure of this query either, but I'm pretty sure using the Design View capacities to create it will be reliable. Of course, always make a backup of your data before running anything I suggest. : ) Or anything else new and uncertain you are just testing for the first time. Or the second time. Did I mention before that I'm concerned about the use of Date() in this query. If you system clock is somehow accidentally set forward to 2/9/2008, it will delete all your data. What is deleted here depends on the system clock being set correctly. Having the user enter the desired date might be better, but that too is subject to mis-typing. The best thing I can think of at the moment is to rank your data by date and choose a date from that which assures you the most recent 200, 500, 10000, or whatever number of records are most recent will not be deleted. Test the date for your deletions against that. It must be a date prior to your 500 most recent records. A good question might be to consider for yourself why it is so necessary to delete them at all. You could use a similar query mechanism to just disregard them for reporting or forms purposes, and let them be. This avoids the possible tragic loss of historic information, especially if your backup scheme is not highly reliable. If at some future time you want to change the number of days from 730 to what seems better at that time, everything would adjust to the new limit immediately. If you set it to 1000 days, you'd instantly have 270 days more information show up (assuming there is some in your table). If table size (the 2 GB max for Jet files, for example) or performance is your goal, then archiving them first to a separate database would be an option. Ask yourself, if all the data you have accumulated in this table were to disappear due to such a malfunction, what amount of time and money would it cost? Now are you motivated to protect it thoroughly? Well, I've given you the rope to hang yourself. : ) What you do with it is up to you! Tom Ellison "Ron Le Blanc" wrote in message ... Dear Tom, First I thank you for all the help you have given me thus far. This is a nasty problem! The last SQL query you gave me (below) runs but gives the following error message: "Specify the table containing the records you want to delete" If I make it a Select query it selects the correct clients!! But a Delete query give the above error message. One more tiny tweak maybe? Thanks and take care... "Tom Ellison" wrote in message ... Dear Ron: Well, I used to be able to write queries. Actually, it's easy enough when you have the database in front of you. I think I had the order wrong, didn't I? SELECT client.MasterID FROM client INNER JOIN meals ON meals.MasterID = client.MasterID GROUP BY client.MasterID HAVING MAX(meals.LastVisit) Date() - 730 I've been on my own problem almost straight for 48 hours. Please forgive. I'm too tired, or old, or something. Tom Ellison "Ron Le Blanc" wrote in message ... Dear Tom, Syntax error in HAVING clause Sigh Take care, "Tom Ellison" wrote in message ... Dear Ron: Maybe I should make excuses. Not. OK, this may be what is required: SELECT client.MasterID FROM client INNER JOIN meals ON meals.MasterID = client.MasterID HAVING MAX(meals.LastVisit) Date() - 730 GROUP BY client.MasterID Funny what you forget at times. I THINK this is what is needed. Please get back, OK? This is sanity check day for me! Tom Ellison "Ron Le Blanc" wrote in message ... The second SQL statement creates an error that says you can't have an aggregate function in a where clause as in (MAX(meals.LastVisit)Date()-730 The meals table contains the client master ID, a meals master ID, (those two create the combined key for the meals table), a list of date, one per row, of the dates the client has visited the food bank. There can be and often are multiple entries per client. The last row contains the last date a client came into the food bank. Soooo... if that date is two or more years old I want to delete the client and all records related to this client. "Tom Ellison" wrote in message ... Dear Ron: I'll make a guess at this. Perhaps we are misunderstanding your meaning of "LastVisit." If you have several rows in [meals] each with a different LastVisit, it is possible you want to delete the client and all meals only when the most recent LastVisit is prior to 730 days ago. Is that what you want? The query as I read it would delete a client and related meals if there is ANY visit more than 730 days ago. That would be a quite different thing. It could be: SELECT client.MasterID FROM client INNER JOIN meals ON meals.MasterID = client.MasterID WHERE MAX(meals.LastVisit) Date() - 730 GROUP BY client.MasterID I have presented this as a SELECT query not a DELETE. It shows what rows would be deleted if it were changed to be a DELETE query. By not deleting anything, testing is easier, as you can change it and run it again without having to restore the data every time it deletes wrongly. To see what you are doing further, build another query around this (temporarily): SELECT * FROM client INNER JOIN meals ON meals.MasterID = client.MasterID WHERE client.MasterID IN ( SELECT client.MasterID FROM client INNER JOIN meals ON meals.MasterID = client.MasterID WHERE MAX(meals.LastVisit) Date() - 730 GROUP BY client.MasterID) ORDER BY client.MasterID, meals.LastVisit Such a test is revealing, is it not? I would remark that this is a potentially dangerous query. If your system date is off, you could be removing rows you do not intend. That sounds like a bit of a risk to me. Tom Ellison "Ron Le Blanc" wrote in message ... This does not work. It selects many clients who have been in within two years. It does find a bunch of the "right" clients to delete but also includes what seems be be random other clients not meeting the two years since last time they came in. "John Vinson" wrote in message ... On Sat, 4 Feb 2006 23:15:16 -0500, "Ron Le Blanc" wrote: DELETE client.*, client.MasterID, meals.LastVisit, client.f_name, client.l_name FROM client INNER JOIN meals ON client.MasterID = meals.MasterID WHERE (((meals.LastVisit)Date()-730)); The error message says: "Cannot delete from table". You're trying too hard. Don't delete anything from the Meals table - cascading deletes will take care of that. DELETE client.* FROM client INNER JOIN meals ON client.MasterID = meals.MasterID WHERE (((meals.LastVisit)Date()-730)); John W. Vinson[MVP] |
#20
|
|||
|
|||
Delete Query Does Not
On Thu, 9 Feb 2006 23:35:04 -0600, "Tom Ellison"
wrote: Dear Ron: Did you try the method of changing the query in the Design View, as I suggested? Or does this fix it: DELETE client.MasterID FROM client INNER JOIN meals ON meals.MasterID = client.MasterID GROUP BY client.MasterID HAVING MAX(meals.LastVisit) Date() - 730 Tom, I may be mistaken, but I think that any Totals operations such as Group By or Max will prevent a query from being updateable - even if, as in this case, it *should* be updateable. It may be necessary to use a subquery such as: DELETE * FROM Client WHERE MasterID NOT IN (SELECT DISTINCT MasterID FROM Meals WHERE Meals.LastVisit DateAdd("yyyy", -2, Date())); to delete all clients except those who have an entry during the past two years. That's a bit different from the criterion as posed though! It may be safer to use TWO subqueries: DELETE * FROM Client WHERE MasterID NOT IN (SELECT DISTINCT MasterID FROM Meals WHERE Meals.LastVisit DateAdd("yyyy", -2, Date())) AND MasterID IN (SELECT DISTINCT MasterID FROM Meals WHERE Meals.LastVisit = DateAdd("yyyy", -2, Date())); to delete only those who *did* have a LastVisit over two years ago, but not since. John W. Vinson[MVP] John W. Vinson[MVP] |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Delete Query Doesnt | Ron Le Blanc | Running & Setting Up Queries | 7 | February 8th, 2006 01:21 AM |
Toolbars, Drop-Down Menus | Rick | New Users | 1 | September 21st, 2005 11:17 AM |
SQL query showing diff between actual and budget | Bon | Running & Setting Up Queries | 3 | August 25th, 2005 12:07 PM |
Nested in-line Query | laura | Running & Setting Up Queries | 0 | February 11th, 2005 12:17 AM |
Here's a shocker | Mike Labosh | General Discussion | 2 | October 26th, 2004 05:04 PM |