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