A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Delete Query Does Not



 
 
Thread Tools Display Modes
  #21  
Old February 11th, 2006, 12:18 AM posted to microsoft.public.access.queries
external usenet poster
 
Posts: n/a
Default 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  
Old February 11th, 2006, 08:27 PM posted to microsoft.public.access.queries
external usenet poster
 
Posts: n/a
Default 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  
Old February 12th, 2006, 06:09 AM posted to microsoft.public.access.queries
external usenet poster
 
Posts: n/a
Default 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  
Old February 12th, 2006, 01:53 PM posted to microsoft.public.access.queries
external usenet poster
 
Posts: n/a
Default 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  
Old February 13th, 2006, 12:46 AM posted to microsoft.public.access.queries
external usenet poster
 
Posts: n/a
Default 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  
Old February 13th, 2006, 02:31 AM posted to microsoft.public.access.queries
external usenet poster
 
Posts: n/a
Default 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  
Old February 13th, 2006, 06:51 AM posted to microsoft.public.access.queries
external usenet poster
 
Posts: n/a
Default 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  
Old February 14th, 2006, 04:28 AM posted to microsoft.public.access.queries
external usenet poster
 
Posts: n/a
Default 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  
Old February 14th, 2006, 06:06 AM posted to microsoft.public.access.queries
external usenet poster
 
Posts: n/a
Default 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  
Old February 16th, 2006, 03:58 AM posted to microsoft.public.access.queries
external usenet poster
 
Posts: n/a
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Delete Query Doesnt Ron Le Blanc Running & Setting Up Queries 7 February 8th, 2006 02: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 01:17 AM
Here's a shocker Mike Labosh General Discussion 2 October 26th, 2004 05:04 PM


All times are GMT +1. The time now is 11:31 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.