Delete Query Does Not

[Tom Ellison]

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

 

[Ron Le Blanc]

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!!!

 

[Tom Ellison]

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]

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]

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]

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]

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

 

[John Vinson]

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]

 

[Tom Ellison]

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

 

[Ron Le Blanc]

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,

 

[Ron Le Blanc]

Should I start a new thread? We seem to have used up all the space in this
one.

Take care,

 

[Tom Ellison]

Dear Ron:

You can make the column wider, and we can keep going for another month or
so.

I think you should try again, and I'll let someone else see what they can
do, if you'd like.

I might also be able to do a better job looking at your database. If you
wish, you can send it to me.

1. Compact and Repair

2. Make a copy of the file

3. Rename the copy, making the file extension XXX instead of MDB

4. Compress with Zip

5. Check size of zip file. Under 2 MB?

6. Attach to email and send to me. I post with my real email address, and
live with the spam.

I will be very busy all week, but may look at this Tuesday or so.

Tom Ellison

 

[Ron Le Blanc]

I have sent a copy of the database to Tom Ellison. Would you like a copy
also John Vinson?? It's fairly small, about 650Kb.

Take care

 

[Ron Le Blanc]

Dear John, (ooops! That doesn"t sound right. )

Copy of files sent to Tom Ellison.