Delete query doesn't!!

My apologies, I meant to post this to the OP not the responder. I hope
she/he will see this and respond.


"Chris2" wrote in message
...

"John Spencer" wrote in message
...
Pardon me for butting in. You probably have a relationship set
between
Client and Meals.

Yes.

In my first post I wrote:

MasterID looks like the Primary Key of client, so
I'm using it that way.


If you have it set to maintain data integrity, you cannot
delete the client until you have deleted ALL the related records in
the
Meals table. Access will delete the client and the related meals
records IF
you set Cascade Delete option to true.

Yes.

I also wrote:

Open the relationships window and show both tables.
Right-click on the relationship link. Check both
boxes for cascading on DELETES and UPDATES.

and:

Because we've got cascading deletes going, the meals
table got it's rows for Mr. Jay Smith deleted, as well.



From an earlier reply to your post entitled "Delete Query Problem"

Do you want to delete the records in the Meals table or in the
Client table
or in both?

The OP wrote:

I wish to delete all clients that have not been seen
for two years or more. A "meal" table contains the
last visit date.



Does the meals table have multiple records for each MasterID? I am
guessing
that LastVisit is a date field that actually specifies the date the
meals
were received.

Only the OP can say for sure. I used multiple records in meals, but
that was an assumption on my part.


Sincerely,

Chris O.

Good catch Chris!

I was so focused on
"Cannot delete from specified table" error,
I never paid attention to WHERE clause!

Does this make more sense?

DELETE DISTINCTROW client.*
FROM client
INNER JOIN
[SELECT
MasterID,
Max(LastVisit) As LatestVisit
FROM
meals
GROUP BY MasterID]. As m
ON
client.MasterID = m.MasterID
WHERE
((m.LastestVisit)DateAdd("yyyy", -2, Date()));

or I suppose you could add
WHERE clause to virtual table.

Plus, John brought up something
I never thought about...

Depending on relationships
(and since you can only delete
from one table in query),
it is possible you cannot delete
from client as long as there are
records for those clients in meals...

I have to get back to my "job work"
and now regret tacking a what-I-thought
was a brief post to a more-complicated
situation than I have bandwidth to pursue.

Apologies and please disregard all
my posts to this thread.

gary
"Chris2" wrote:
"Gary Walter" wrote:
PMFBI

A delete query involving more than
one table is one of the few situations
where *DISTINCTROW* means anything
anymore in Access 200x.

http://support.microsoft.com/default...b;en-us;207761

I might suggest trying on copy of data:

DELETE DISTINCTROW client.*
FROM client INNER JOIN meals
ON client.MasterID = meals.MasterID
WHERE
((meals.LastVisit)DateAdd("yyyy", -2, Date()));

snip
The above query will attempt to delete clients that are still active.

When I run the above query, it attempts to delete Dee Smith (MasterID
2) from client, even though there is a LastVisit in meals more recent
that two years ago.


Sincerely,

Chris O.

"Van T. Dinh" wrote in
message ...
OK. Try:

DELETE DISTINCT client.*
FROM client INNER JOIN meals
ON client.MasterID = meals.MasterID
WHERE ((meals.LastVisit)DateAdd("yyyy", -2, Date()));

--
HTH
Van T. Dinh
MVP (Access)


That generates:

"The Microsoft JET database engine does not recognize 'DISTINCT
client.*' as a valid field name or expression."


Sincerely,

Chris O.

"Gary Walter" wrote in message
...
PMFBI

A delete query involving more than
one table is one of the few situations
where *DISTINCTROW* means anything
anymore in Access 200x.

http://support.microsoft.com/default...b;en-us;207761

I might suggest trying on copy of data:

DELETE DISTINCTROW client.*
FROM client INNER JOIN meals
ON client.MasterID = meals.MasterID
WHERE
((meals.LastVisit)DateAdd("yyyy", -2, Date()));

It has been awhile since I visited this...
so, if you don't mind, would you mind
posting back with your testing. The reason
I ask is because I vaguely remember
needing also a LEFT JOIN which does
not make sense to me right now.

Apologies again for butting in.

good luck,

gary

One other point is:

Are you sure you want to "lose data."

This can come back to bite you in
some situations.

A common alternative is to add a
field to "client" like "fActive" (default -1)
and instead of deleting, change "fActive"
to 0.


Gary Walter,

The above query will attempt to delete clients that are still active.

When I run the above query, it attempts to delete Dee Smith (MasterID
2) from client, even though there is a LastVisit in meals more recent
that two years ago.


Sincerely,

Chris O.

"John Spencer" wrote in message
...
Pardon me for butting in. You probably have a relationship set
between
Client and Meals.

Yes.

In my first post I wrote:

MasterID looks like the Primary Key of client, so
I'm using it that way.


If you have it set to maintain data integrity, you cannot
delete the client until you have deleted ALL the related records in
the
Meals table. Access will delete the client and the related meals
records IF
you set Cascade Delete option to true.

Yes.

I also wrote:

Open the relationships window and show both tables.
Right-click on the relationship link. Check both
boxes for cascading on DELETES and UPDATES.

and:

Because we've got cascading deletes going, the meals
table got it's rows for Mr. Jay Smith deleted, as well.



From an earlier reply to your post entitled "Delete Query Problem"

Do you want to delete the records in the Meals table or in the
Client table
or in both?

The OP wrote:

I wish to delete all clients that have not been seen
for two years or more. A "meal" table contains the
last visit date.



Does the meals table have multiple records for each MasterID? I am
guessing
that LastVisit is a date field that actually specifies the date the
meals
were received.

Only the OP can say for sure. I used multiple records in meals, but
that was an assumption on my part.


Sincerely,

Chris O.

Nope. It doesn't like the word "DISTINCT".

"Van T. Dinh" wrote in message
...
OK. Try:

DELETE DISTINCT client.*
FROM client INNER JOIN meals
ON client.MasterID = meals.MasterID
WHERE ((meals.LastVisit)DateAdd("yyyy", -2, Date()));

--
HTH
Van T. Dinh
MVP (Access)



"Ron Le Blanc" wrote in message
...
This generates a "Cannot delete from specified table" error.

It looks like Access/JET doesn't like deleting the "One" Records in a Join.
Admiited, I mostly delete the "Many" Records first before deleting the "One"
Records whether I enforce Cascade Delete or not.

I think you may need a SubQuery in this case. Try something like:

DELETE client.*
FROM client
WHERE Client.MaterID Not In
(
SELECT DISTINCT Meals.MasterID
FROM Meals
WHERE (Meals.LastVisit DateAdd("yyyy", -2, Date())
);

The above should also delete Records that don't have related Records in
Meals which is probably what you need. The only problem is this type of
Query is fairly slow because of the Not In Clause. Try also:

DELETE client.*
FROM client LEFT JOIN
(
SELECT Meals.MasterID
FROM Meals
WHERE (Meals.LastVisit DateAdd("yyyy", -2, Date())
) AS SubQ
ON client.MasterID = SubQ.MasterID
WHERE SubQ.MasterID Is Null;

If the second SQL works (which I am not sure), it should me much faster than
the first SQL.

--
HTH
Van T. Dinh
MVP (Access)



"Ron Le Blanc" wrote in message
.. .
Nope. It doesn't like the word "DISTINCT".

Chris2
I just applied similar syntax with "Distinctrow" and it appeared to delete
all required non-unique rows. Thanks for your input everyone.
Ben

"Chris2" wrote:


"Van T. Dinh" wrote in
message ...
OK. Try:

DELETE DISTINCT client.*
FROM client INNER JOIN meals
ON client.MasterID = meals.MasterID
WHERE ((meals.LastVisit)DateAdd("yyyy", -2, Date()));

--
HTH
Van T. Dinh
MVP (Access)


That generates:

"The Microsoft JET database engine does not recognize 'DISTINCT
client.*' as a valid field name or expression."


Sincerely,

Chris O.