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
  #1  
Old February 5th, 2006, 05:15 AM posted to microsoft.public.access.queries
external usenet poster
 
Posts: n/a
Default Delete Query Does Not


I have a database that won't let me delete records using a Delete Query.

I have an Access 2003 database that has several tables in it that are all
related to a client table and have referential integraty and the cascade
delete enabled.

I wish to delete all clients that have not been seen for two years or more.
And yes, I really want to delete them.

A "meal" table contains the last visit date. See relationships...

The idea is to find all clients who have not been seen in two or more years
and delete them.
The data sheet view shows that the correct clients are selected. However,
when I try to run the delete query it says it cannot delete the records. The
"client" table is the table to which all other tables are related to.

I don't care if I loose all the data for a client. If the client has not
been in in two years a new record would have to be created anyway.

The SQL generated is:

DELETE client.*, client.MasterID, meals.LastVisit, client.f_name,
client.l_name
FROM client INNER JOIN meals ON client.MasterID = meals.MasterID
WHERE (((meals.LastVisit)Date()-730));

The error message says: "Cannot delete from table".

Does anyone have an idea as to why this does not work and what might be done
to make it work??

Thanks!



  #2  
Old February 5th, 2006, 05:44 AM posted to microsoft.public.access.queries
external usenet poster
 
Posts: n/a
Default Delete Query Does Not

On Sat, 4 Feb 2006 23:15:16 -0500, "Ron Le Blanc"
wrote:

DELETE client.*, client.MasterID, meals.LastVisit, client.f_name,
client.l_name
FROM client INNER JOIN meals ON client.MasterID = meals.MasterID
WHERE (((meals.LastVisit)Date()-730));

The error message says: "Cannot delete from table".


You're trying too hard. Don't delete anything from the Meals table -
cascading deletes will take care of that.

DELETE client.*
FROM client INNER JOIN meals ON client.MasterID = meals.MasterID
WHERE (((meals.LastVisit)Date()-730));


John W. Vinson[MVP]

  #3  
Old February 5th, 2006, 06:35 PM posted to microsoft.public.access.queries
external usenet poster
 
Posts: n/a
Default Delete Query Does Not

John,

You've made the assumption that cascading delete has been implemented.

Ron,
If you have not set up the relationships and implemented cascading deletes when
you did so, then you will have to run two queries.

Delete the appropriate records in the meal table
Then delete records in the client table that don't have any record(s) in the
meal table.


John Vinson wrote:

On Sat, 4 Feb 2006 23:15:16 -0500, "Ron Le Blanc"
wrote:

DELETE client.*, client.MasterID, meals.LastVisit, client.f_name,
client.l_name
FROM client INNER JOIN meals ON client.MasterID = meals.MasterID
WHERE (((meals.LastVisit)Date()-730));

The error message says: "Cannot delete from table".


You're trying too hard. Don't delete anything from the Meals table -
cascading deletes will take care of that.

DELETE client.*
FROM client INNER JOIN meals ON client.MasterID = meals.MasterID
WHERE (((meals.LastVisit)Date()-730));

John W. Vinson[MVP]

  #4  
Old February 5th, 2006, 06:44 PM posted to microsoft.public.access.queries
external usenet poster
 
Posts: n/a
Default Delete Query Does Not

And, if you have not implemented cascading deletes, you will know this when
you try to delete the client row without deleting the meal rows first.
You'll get a message about this, but the data will not be damaged, as the
delete will simply fail. At that point, either implement the cascading or
use Mr. Spencer's suggestion.

You should check the meal table to make sure this has worked. Have the rows
for the related client row been deleted, or are they still there, orphaned?
If they have not been deleted, check the client table to confirm they are
truly orphaned. If so, then the relationship has not been created to
prevent this.

Thorough testing and knowing what to do about it is essential before going
production on a thing like this.

Tom Ellison


"John Spencer" wrote in message
...
John,

You've made the assumption that cascading delete has been implemented.

Ron,
If you have not set up the relationships and implemented cascading deletes
when
you did so, then you will have to run two queries.

Delete the appropriate records in the meal table
Then delete records in the client table that don't have any record(s) in
the
meal table.


John Vinson wrote:

On Sat, 4 Feb 2006 23:15:16 -0500, "Ron Le Blanc"
wrote:

DELETE client.*, client.MasterID, meals.LastVisit, client.f_name,
client.l_name
FROM client INNER JOIN meals ON client.MasterID = meals.MasterID
WHERE (((meals.LastVisit)Date()-730));

The error message says: "Cannot delete from table".


You're trying too hard. Don't delete anything from the Meals table -
cascading deletes will take care of that.

DELETE client.*
FROM client INNER JOIN meals ON client.MasterID = meals.MasterID
WHERE (((meals.LastVisit)Date()-730));

John W. Vinson[MVP]



  #5  
Old February 6th, 2006, 03:33 AM posted to microsoft.public.access.queries
external usenet poster
 
Posts: n/a
Default Delete Query Does Not

On Sun, 05 Feb 2006 12:35:10 -0500, John Spencer
wrote:

You've made the assumption that cascading delete has been implemented.


Ron did say:

I have an Access 2003 database that has several tables in it that are
all related to a client table and have referential integraty and the
cascade delete enabled.


John W. Vinson[MVP]
  #6  
Old February 6th, 2006, 05:21 AM posted to microsoft.public.access.queries
external usenet poster
 
Posts: n/a
Default Delete Query Does Not

This does not work. It selects many clients who have been in within two
years.
It does find a bunch of the "right" clients to delete but also includes what
seems be be random other clients not meeting the two years since last time
they came in.




"John Vinson" wrote in message
...
On Sat, 4 Feb 2006 23:15:16 -0500, "Ron Le Blanc"
wrote:

DELETE client.*, client.MasterID, meals.LastVisit, client.f_name,
client.l_name
FROM client INNER JOIN meals ON client.MasterID = meals.MasterID
WHERE (((meals.LastVisit)Date()-730));

The error message says: "Cannot delete from table".


You're trying too hard. Don't delete anything from the Meals table -
cascading deletes will take care of that.

DELETE client.*
FROM client INNER JOIN meals ON client.MasterID = meals.MasterID
WHERE (((meals.LastVisit)Date()-730));


John W. Vinson[MVP]



  #7  
Old February 6th, 2006, 05:37 AM posted to microsoft.public.access.queries
external usenet poster
 
Posts: n/a
Default Delete Query Does Not

Dear Ron:

I'll make a guess at this. Perhaps we are misunderstanding your meaning of
"LastVisit."

If you have several rows in [meals] each with a different LastVisit, it is
possible you want to delete the client and all meals only when the most
recent LastVisit is prior to 730 days ago. Is that what you want?

The query as I read it would delete a client and related meals if there is
ANY visit more than 730 days ago. That would be a quite different thing.
It could be:

SELECT client.MasterID
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
WHERE MAX(meals.LastVisit) Date() - 730
GROUP BY client.MasterID

I have presented this as a SELECT query not a DELETE. It shows what rows
would be deleted if it were changed to be a DELETE query. By not deleting
anything, testing is easier, as you can change it and run it again without
having to restore the data every time it deletes wrongly.

To see what you are doing further, build another query around this
(temporarily):

SELECT *
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
WHERE client.MasterID IN (
SELECT client.MasterID
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
WHERE MAX(meals.LastVisit) Date() - 730
GROUP BY client.MasterID)
ORDER BY client.MasterID, meals.LastVisit

Such a test is revealing, is it not?

I would remark that this is a potentially dangerous query. If your system
date is off, you could be removing rows you do not intend. That sounds like
a bit of a risk to me.

Tom Ellison


"Ron Le Blanc" wrote in message
...
This does not work. It selects many clients who have been in within two
years.
It does find a bunch of the "right" clients to delete but also includes
what seems be be random other clients not meeting the two years since last
time they came in.




"John Vinson" wrote in message
...
On Sat, 4 Feb 2006 23:15:16 -0500, "Ron Le Blanc"
wrote:

DELETE client.*, client.MasterID, meals.LastVisit, client.f_name,
client.l_name
FROM client INNER JOIN meals ON client.MasterID = meals.MasterID
WHERE (((meals.LastVisit)Date()-730));

The error message says: "Cannot delete from table".


You're trying too hard. Don't delete anything from the Meals table -
cascading deletes will take care of that.

DELETE client.*
FROM client INNER JOIN meals ON client.MasterID = meals.MasterID
WHERE (((meals.LastVisit)Date()-730));


John W. Vinson[MVP]





  #8  
Old February 6th, 2006, 12:54 PM posted to microsoft.public.access.queries
external usenet poster
 
Posts: n/a
Default Delete Query Does Not

Ouch. I completely missed that. Should have known better than to correct the wizard.

John Vinson wrote:

On Sun, 05 Feb 2006 12:35:10 -0500, John Spencer
wrote:

You've made the assumption that cascading delete has been implemented.


Ron did say:

I have an Access 2003 database that has several tables in it that are
all related to a client table and have referential integraty and the
cascade delete enabled.

John W. Vinson[MVP]

  #9  
Old February 7th, 2006, 05:42 AM posted to microsoft.public.access.queries
external usenet poster
 
Posts: n/a
Default Delete Query Does Not

The meals table contains the client master ID, a meals master ID, a list of
date, one per row, of the dates the client has visited the food bank. There
can be and often are multiple entries per client. The last row contains the
last date a client came into the food bank. Soooo... if that date is two or
more years old I want to delete the client and all records related to this
client.


"Tom Ellison" wrote in message
...
Dear Ron:

I'll make a guess at this. Perhaps we are misunderstanding your meaning
of "LastVisit."

If you have several rows in [meals] each with a different LastVisit, it is
possible you want to delete the client and all meals only when the most
recent LastVisit is prior to 730 days ago. Is that what you want?

The query as I read it would delete a client and related meals if there is
ANY visit more than 730 days ago. That would be a quite different thing.
It could be:

SELECT client.MasterID
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
WHERE MAX(meals.LastVisit) Date() - 730
GROUP BY client.MasterID

I have presented this as a SELECT query not a DELETE. It shows what rows
would be deleted if it were changed to be a DELETE query. By not deleting
anything, testing is easier, as you can change it and run it again without
having to restore the data every time it deletes wrongly.

To see what you are doing further, build another query around this
(temporarily):

SELECT *
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
WHERE client.MasterID IN (
SELECT client.MasterID
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
WHERE MAX(meals.LastVisit) Date() - 730
GROUP BY client.MasterID)
ORDER BY client.MasterID, meals.LastVisit

Such a test is revealing, is it not?

I would remark that this is a potentially dangerous query. If your system
date is off, you could be removing rows you do not intend. That sounds
like a bit of a risk to me.

Tom Ellison


"Ron Le Blanc" wrote in message
...
This does not work. It selects many clients who have been in within two
years.
It does find a bunch of the "right" clients to delete but also includes
what seems be be random other clients not meeting the two years since
last time they came in.




"John Vinson" wrote in message
...
On Sat, 4 Feb 2006 23:15:16 -0500, "Ron Le Blanc"
wrote:

DELETE client.*, client.MasterID, meals.LastVisit, client.f_name,
client.l_name
FROM client INNER JOIN meals ON client.MasterID = meals.MasterID
WHERE (((meals.LastVisit)Date()-730));

The error message says: "Cannot delete from table".

You're trying too hard. Don't delete anything from the Meals table -
cascading deletes will take care of that.

DELETE client.*
FROM client INNER JOIN meals ON client.MasterID = meals.MasterID
WHERE (((meals.LastVisit)Date()-730));


John W. Vinson[MVP]







  #10  
Old February 7th, 2006, 05:56 AM posted to microsoft.public.access.queries
external usenet poster
 
Posts: n/a
Default Delete Query Does Not

Dear Ron:

Then it sounds like my understanding is correct. Have you tried what I
proposed?

Tom Ellison


"Ron Le Blanc" wrote in message
...
The meals table contains the client master ID, a meals master ID, a list
of date, one per row, of the dates the client has visited the food bank.
There can be and often are multiple entries per client. The last row
contains the last date a client came into the food bank. Soooo... if that
date is two or more years old I want to delete the client and all records
related to this client.


"Tom Ellison" wrote in message
...
Dear Ron:

I'll make a guess at this. Perhaps we are misunderstanding your meaning
of "LastVisit."

If you have several rows in [meals] each with a different LastVisit, it
is possible you want to delete the client and all meals only when the
most recent LastVisit is prior to 730 days ago. Is that what you want?

The query as I read it would delete a client and related meals if there
is ANY visit more than 730 days ago. That would be a quite different
thing. It could be:

SELECT client.MasterID
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
WHERE MAX(meals.LastVisit) Date() - 730
GROUP BY client.MasterID

I have presented this as a SELECT query not a DELETE. It shows what rows
would be deleted if it were changed to be a DELETE query. By not
deleting anything, testing is easier, as you can change it and run it
again without having to restore the data every time it deletes wrongly.

To see what you are doing further, build another query around this
(temporarily):

SELECT *
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
WHERE client.MasterID IN (
SELECT client.MasterID
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
WHERE MAX(meals.LastVisit) Date() - 730
GROUP BY client.MasterID)
ORDER BY client.MasterID, meals.LastVisit

Such a test is revealing, is it not?

I would remark that this is a potentially dangerous query. If your
system date is off, you could be removing rows you do not intend. That
sounds like a bit of a risk to me.

Tom Ellison


"Ron Le Blanc" wrote in message
...
This does not work. It selects many clients who have been in within two
years.
It does find a bunch of the "right" clients to delete but also includes
what seems be be random other clients not meeting the two years since
last time they came in.




"John Vinson" wrote in message
...
On Sat, 4 Feb 2006 23:15:16 -0500, "Ron Le Blanc"
wrote:

DELETE client.*, client.MasterID, meals.LastVisit, client.f_name,
client.l_name
FROM client INNER JOIN meals ON client.MasterID = meals.MasterID
WHERE (((meals.LastVisit)Date()-730));

The error message says: "Cannot delete from table".

You're trying too hard. Don't delete anything from the Meals table -
cascading deletes will take care of that.

DELETE client.*
FROM client INNER JOIN meals ON client.MasterID = meals.MasterID
WHERE (((meals.LastVisit)Date()-730));


John W. Vinson[MVP]









 




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 10:43 PM.


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