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 |
#21
|
|||
|
|||
Delete Query Does Not
Dear John:
I had forgotten just how frustrating it is to work in Jet. No such problem with MSDE, of course! ADPs rule! Your solution is then correct, and masterful. Thanks, John! You so often save the day. If the poster wants two years, which is not always 730 days, then you have improved on that as well. Tom Ellison "John Vinson" wrote in message ... 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] |
#22
|
|||
|
|||
Delete Query Does Not
Dear Tom and John,
I am humbled by your insights and sneaky ways of getting from put A to point B to get around the limitations of the Jet daabase! I started with what I thought was a reasonalby simple question and have ended(?) with a completely different set of logic to implement a solution. All I can say is that I am very greatfull to yo both for your assistance on what I originally thought was a cat and dried question. You have left me in the dust and I just want to make sure what we ended up with. Is the following the end solution that we have ended up with? 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())); I will give it a try and let you know the results... Thank you very much!!! PS: Next time I am going to do anything like this I am going to use MySQL and Linux!!!! Thanks again!!! |
#23
|
|||
|
|||
Delete Query Does Not
Dear Ron:
MySQL is not likely to be that much different. SQL is somewhat standardized. The code you provided would delete any "new" Client that does not yet have any data in Meals. Someone may have just set up a new client, and next thing you know, they're gone! I'm not sure you meant that, did you? Tom Ellison "Ron Le Blanc" wrote in message ... Dear Tom and John, I am humbled by your insights and sneaky ways of getting from put A to point B to get around the limitations of the Jet daabase! I started with what I thought was a reasonalby simple question and have ended(?) with a completely different set of logic to implement a solution. All I can say is that I am very greatfull to yo both for your assistance on what I originally thought was a cat and dried question. You have left me in the dust and I just want to make sure what we ended up with. Is the following the end solution that we have ended up with? 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())); I will give it a try and let you know the results... Thank you very much!!! PS: Next time I am going to do anything like this I am going to use MySQL and Linux!!!! Thanks again!!! |
#24
|
|||
|
|||
Delete Query Does Not
You are correct in that I would not want to delete a new client. On the ohte
hand I cannot envisage a situation where a new client would be entered without a meals entry also. So I don't think this is a problem. If a client is entered they will have a meals entry; it is the only way one would be entered into the database, that is, they were provided meals. So, I don't think this is a problem. As to the PCs clock being off by anywhere near two years, that does not seem likely either. I can make sure they check the time and date BEFORE running the DELETE query. Anything else I should watch for?? Thanks a million!! "Tom Ellison" wrote in message ... Dear Ron: MySQL is not likely to be that much different. SQL is somewhat standardized. The code you provided would delete any "new" Client that does not yet have any data in Meals. Someone may have just set up a new client, and next thing you know, they're gone! I'm not sure you meant that, did you? Tom Ellison "Ron Le Blanc" wrote in message ... Dear Tom and John, I am humbled by your insights and sneaky ways of getting from put A to point B to get around the limitations of the Jet daabase! I started with what I thought was a reasonalby simple question and have ended(?) with a completely different set of logic to implement a solution. All I can say is that I am very greatfull to yo both for your assistance on what I originally thought was a cat and dried question. You have left me in the dust and I just want to make sure what we ended up with. Is the following the end solution that we have ended up with? 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())); I will give it a try and let you know the results... Thank you very much!!! PS: Next time I am going to do anything like this I am going to use MySQL and Linux!!!! Thanks again!!! |
#25
|
|||
|
|||
Delete Query Does Not
Dear Ron:
If you think you can get them to check the clock before doing this, then I can't think of any objections. From my experience, I don't like to trust a user with something like this. On reboot, the clock setting comes from a bit of hardware kept alive while the computer is turned off by batteries, so it is slightly tenuous. I make it my practice to find ways to NOT trusy my users, but to do things so as not to insult them either. If the systems have a permanent internet connection, there are web sites where you can get the date and time very accurately. I'd rather use something like that. Indeed, I believe there are utilities that will set the computer clock from the internet when it is booted. This is perhaps the best solution. Being able to synchronize many users, sometimes from around the world, and do so accurately and reliably is often an essential tool. Tom Ellison "Ron Le Blanc" wrote in message ... You are correct in that I would not want to delete a new client. On the ohte hand I cannot envisage a situation where a new client would be entered without a meals entry also. So I don't think this is a problem. If a client is entered they will have a meals entry; it is the only way one would be entered into the database, that is, they were provided meals. So, I don't think this is a problem. As to the PCs clock being off by anywhere near two years, that does not seem likely either. I can make sure they check the time and date BEFORE running the DELETE query. Anything else I should watch for?? Thanks a million!! "Tom Ellison" wrote in message ... Dear Ron: MySQL is not likely to be that much different. SQL is somewhat standardized. The code you provided would delete any "new" Client that does not yet have any data in Meals. Someone may have just set up a new client, and next thing you know, they're gone! I'm not sure you meant that, did you? Tom Ellison "Ron Le Blanc" wrote in message ... Dear Tom and John, I am humbled by your insights and sneaky ways of getting from put A to point B to get around the limitations of the Jet daabase! I started with what I thought was a reasonalby simple question and have ended(?) with a completely different set of logic to implement a solution. All I can say is that I am very greatfull to yo both for your assistance on what I originally thought was a cat and dried question. You have left me in the dust and I just want to make sure what we ended up with. Is the following the end solution that we have ended up with? 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())); I will give it a try and let you know the results... Thank you very much!!! PS: Next time I am going to do anything like this I am going to use MySQL and Linux!!!! Thanks again!!! |
#26
|
|||
|
|||
Delete Query Does Not
Sorry guys, the SQL given below does not work. If I make it a Select Query
it returns a single empty record. It is not a happy camper. This is what's left after pasting the below code in and trying to run it once... SELECT Client.MasterID, * FROM Client WHERE (((Client.MasterID) Not In (SELECT DISTINCT MasterID FROM Meals WHERE Meals.LastVisit DateAdd("yyyy", -2, Date())) And (Client.MasterID) In (SELECT DISTINCT MasterID FROM Meals WHERE Meals.LastVisit = DateAdd("yyyy", -2, Date())))); As you can see it rewrites the whole thing. The below code was entered as is, the query run, and the above is what is left. Any other ideas before I go commit hari-kari? This is most depressing.... Seams so simple yet creates a major ooops. If you Tom and John can't help I haven't got an ice cube's change in hell of figuring it out. Sigh Any other ideas???? "Tom Ellison" wrote in message ... Dear Ron: If you think you can get them to check the clock before doing this, then I can't think of any objections. From my experience, I don't like to trust a user with something like this. On reboot, the clock setting comes from a bit of hardware kept alive while the computer is turned off by batteries, so it is slightly tenuous. I make it my practice to find ways to NOT trusy my users, but to do things so as not to insult them either. If the systems have a permanent internet connection, there are web sites where you can get the date and time very accurately. I'd rather use something like that. Indeed, I believe there are utilities that will set the computer clock from the internet when it is booted. This is perhaps the best solution. Being able to synchronize many users, sometimes from around the world, and do so accurately and reliably is often an essential tool. Tom Ellison "Ron Le Blanc" wrote in message ... You are correct in that I would not want to delete a new client. On the ohte hand I cannot envisage a situation where a new client would be entered without a meals entry also. So I don't think this is a problem. If a client is entered they will have a meals entry; it is the only way one would be entered into the database, that is, they were provided meals. So, I don't think this is a problem. As to the PCs clock being off by anywhere near two years, that does not seem likely either. I can make sure they check the time and date BEFORE running the DELETE query. Anything else I should watch for?? Thanks a million!! "Tom Ellison" wrote in message ... Dear Ron: MySQL is not likely to be that much different. SQL is somewhat standardized. The code you provided would delete any "new" Client that does not yet have any data in Meals. Someone may have just set up a new client, and next thing you know, they're gone! I'm not sure you meant that, did you? Tom Ellison "Ron Le Blanc" wrote in message ... Dear Tom and John, I am humbled by your insights and sneaky ways of getting from put A to point B to get around the limitations of the Jet daabase! I started with what I thought was a reasonalby simple question and have ended(?) with a completely different set of logic to implement a solution. All I can say is that I am very greatfull to yo both for your assistance on what I originally thought was a cat and dried question. You have left me in the dust and I just want to make sure what we ended up with. Is the following the end solution that we have ended up with? 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())); I will give it a try and let you know the results... Thank you very much!!! PS: Next time I am going to do anything like this I am going to use MySQL and Linux!!!! Thanks again!!! |
#27
|
|||
|
|||
Delete Query Does Not
Dear Ron:
I believe you have a simple logic error. I didn't catch it in your earlier post. The AND in the WHERE clause should almost certainly be OR. Is that it? Does that fix this? And, does it make sense? A client can never be both. If a client has no meals more recent than two years ago, he cannot also have any meals within the last two years. Indeed, looking further at the logic, it would be enough just to ask if he has a meal within the last 2 years. You wouldn't need the former test. If he has no meals more recent than 2 years ago, he's deleted. If he has no meals, he's deleted. The first case is subsumed in the second one. Be sure to continue your testing using SELECT until you're sure it is correct, then change to DELETE as we said before. Tom Ellison "Ron Le Blanc" wrote in message ... Sorry guys, the SQL given below does not work. If I make it a Select Query it returns a single empty record. It is not a happy camper. This is what's left after pasting the below code in and trying to run it once... SELECT Client.MasterID, * FROM Client WHERE (((Client.MasterID) Not In (SELECT DISTINCT MasterID FROM Meals WHERE Meals.LastVisit DateAdd("yyyy", -2, Date())) And (Client.MasterID) In (SELECT DISTINCT MasterID FROM Meals WHERE Meals.LastVisit = DateAdd("yyyy", -2, Date())))); As you can see it rewrites the whole thing. The below code was entered as is, the query run, and the above is what is left. Any other ideas before I go commit hari-kari? This is most depressing.... Seams so simple yet creates a major ooops. If you Tom and John can't help I haven't got an ice cube's change in hell of figuring it out. Sigh Any other ideas???? "Tom Ellison" wrote in message ... Dear Ron: If you think you can get them to check the clock before doing this, then I can't think of any objections. From my experience, I don't like to trust a user with something like this. On reboot, the clock setting comes from a bit of hardware kept alive while the computer is turned off by batteries, so it is slightly tenuous. I make it my practice to find ways to NOT trusy my users, but to do things so as not to insult them either. If the systems have a permanent internet connection, there are web sites where you can get the date and time very accurately. I'd rather use something like that. Indeed, I believe there are utilities that will set the computer clock from the internet when it is booted. This is perhaps the best solution. Being able to synchronize many users, sometimes from around the world, and do so accurately and reliably is often an essential tool. Tom Ellison "Ron Le Blanc" wrote in message ... You are correct in that I would not want to delete a new client. On the ohte hand I cannot envisage a situation where a new client would be entered without a meals entry also. So I don't think this is a problem. If a client is entered they will have a meals entry; it is the only way one would be entered into the database, that is, they were provided meals. So, I don't think this is a problem. As to the PCs clock being off by anywhere near two years, that does not seem likely either. I can make sure they check the time and date BEFORE running the DELETE query. Anything else I should watch for?? Thanks a million!! "Tom Ellison" wrote in message ... Dear Ron: MySQL is not likely to be that much different. SQL is somewhat standardized. The code you provided would delete any "new" Client that does not yet have any data in Meals. Someone may have just set up a new client, and next thing you know, they're gone! I'm not sure you meant that, did you? Tom Ellison "Ron Le Blanc" wrote in message ... Dear Tom and John, I am humbled by your insights and sneaky ways of getting from put A to point B to get around the limitations of the Jet daabase! I started with what I thought was a reasonalby simple question and have ended(?) with a completely different set of logic to implement a solution. All I can say is that I am very greatfull to yo both for your assistance on what I originally thought was a cat and dried question. You have left me in the dust and I just want to make sure what we ended up with. Is the following the end solution that we have ended up with? 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())); I will give it a try and let you know the results... Thank you very much!!! PS: Next time I am going to do anything like this I am going to use MySQL and Linux!!!! Thanks again!!! |
#28
|
|||
|
|||
Delete Query Does Not
On Sun, 12 Feb 2006 20:31:18 -0500, "Ron Le Blanc"
wrote: Sorry guys, the SQL given below does not work. If I make it a Select Query it returns a single empty record. Let me reiterate, because I think this WILL work and it's what Tom is trying to explain in other language: 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())); The two subselects will pick those people who DID NOT have any meal in the past two years (the first subselect), but it will also require that the person DID have a meal prior to that time (the second subselect). John W. Vinson[MVP] |
#29
|
|||
|
|||
Delete Query Does Not
Dear John:
And howdy! Now, is my brain getting fried on this one? I think so. It really is AND after all. The good news, it won't then delete anyone with no meals, as I had feared. Now, if the first test passes, the only thing really needed from the second test is that there be at least one meal. If the first test passes, we already know there was no meal in the last 730 days. So, it may as well be eimplified: SELECT * 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); Doing it DISTINCT is obviously not essential, but it might help performance. But would it? Is it faster to search though a redundant set or to eliminate duplicates of everything? I recommend testing both ways to see. And, in the immortal words of B & J, "Thank you for your support!" Tom Ellison "John Vinson" wrote in message ... On Sun, 12 Feb 2006 20:31:18 -0500, "Ron Le Blanc" wrote: Sorry guys, the SQL given below does not work. If I make it a Select Query it returns a single empty record. Let me reiterate, because I think this WILL work and it's what Tom is trying to explain in other language: 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())); The two subselects will pick those people who DID NOT have any meal in the past two years (the first subselect), but it will also require that the person DID have a meal prior to that time (the second subselect). John W. Vinson[MVP] |
#30
|
|||
|
|||
Delete Query Does Not
Sorry guys, I wasn't feeling well for a couple of days. I'm back in the
saddle now. From Ellison and Vinson: 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())); Results after running: DELETE Client.MasterID, * FROM Client WHERE (((Client.MasterID) Not In (SELECT DISTINCT MasterID FROM Meals WHERE Meals.LastVisit DateAdd("yyyy", -2, Date())) And (Client.MasterID) In (SELECT DISTINCT MasterID FROM Meals WHERE Meals.LastVisit = DateAdd("yyyy", -2, Date())))); As you can see it morphed the SQL statement again. It ran for a couple of minutes but as far as I can tell nothing was deleted. I have the same number of records I started with. I entered the same SQL statement (the first one) and changed it to a Select query. It ran for about the same amount of time and then showed me a single empty row in datasheet view. No cigar!!! I am going back to an ABACUS now, assuming I don't have to enter the question in SQL. Sigh Does anyone have a shotgun I can borrow for a few minutes? Any other ideas? Thanks for all your efforts, I really appreciate it. I don't feel out in the woods all alone. You guys are with me, yes? Take care, |
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 |