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

The second SQL statement creates an error that says you can't have an
aggregate function in a where clause as in (MAX(meals.LastVisit)Date()-730

The meals table contains the client master ID, a meals master ID, (those two
create the combined key for the meals table), 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]







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

Dear Ron:

Maybe I should make excuses. Not.

OK, this may be what is required:

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

Funny what you forget at times. I THINK this is what is needed. Please get
back, OK? This is sanity check day for me!

Tom Ellison


"Ron Le Blanc" wrote in message
...
The second SQL statement creates an error that says you can't have an
aggregate function in a where clause as in
(MAX(meals.LastVisit)Date()-730

The meals table contains the client master ID, a meals master ID, (those
two create the combined key for the meals table), 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]









  #13  
Old February 8th, 2006, 07:04 AM posted to microsoft.public.access.queries
external usenet poster
 
Posts: n/a
Default Delete Query Does Not

Dear Tom,

Syntax error in HAVING clause

Sigh

Take care,

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

Maybe I should make excuses. Not.

OK, this may be what is required:

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

Funny what you forget at times. I THINK this is what is needed. Please
get back, OK? This is sanity check day for me!

Tom Ellison


"Ron Le Blanc" wrote in message
...
The second SQL statement creates an error that says you can't have an
aggregate function in a where clause as in
(MAX(meals.LastVisit)Date()-730

The meals table contains the client master ID, a meals master ID, (those
two create the combined key for the meals table), 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]











  #14  
Old February 8th, 2006, 07:13 AM posted to microsoft.public.access.queries
external usenet poster
 
Posts: n/a
Default Delete Query Does Not

Dear Ron:

Well, I used to be able to write queries. Actually, it's easy enough when
you have the database in front of you.

I think I had the order wrong, didn't I?

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

I've been on my own problem almost straight for 48 hours. Please forgive.
I'm too tired, or old, or something.

Tom Ellison


"Ron Le Blanc" wrote in message
...
Dear Tom,

Syntax error in HAVING clause

Sigh

Take care,

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

Maybe I should make excuses. Not.

OK, this may be what is required:

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

Funny what you forget at times. I THINK this is what is needed. Please
get back, OK? This is sanity check day for me!

Tom Ellison


"Ron Le Blanc" wrote in message
...
The second SQL statement creates an error that says you can't have an
aggregate function in a where clause as in
(MAX(meals.LastVisit)Date()-730

The meals table contains the client master ID, a meals master ID, (those
two create the combined key for the meals table), 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]













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

Dear Tom,

First I thank you for all the help you have given me thus far. This is a
nasty problem!

The last SQL query you gave me (below) runs but gives the following error
message:

"Specify the table containing the records you want to delete"

If I make it a Select query it selects the correct clients!! But a Delete
query give the above error message.

One more tiny tweak maybe?

Thanks and take care...

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

Well, I used to be able to write queries. Actually, it's easy enough when
you have the database in front of you.

I think I had the order wrong, didn't I?

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

I've been on my own problem almost straight for 48 hours. Please forgive.
I'm too tired, or old, or something.

Tom Ellison


"Ron Le Blanc" wrote in message
...
Dear Tom,

Syntax error in HAVING clause

Sigh

Take care,

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

Maybe I should make excuses. Not.

OK, this may be what is required:

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

Funny what you forget at times. I THINK this is what is needed. Please
get back, OK? This is sanity check day for me!

Tom Ellison


"Ron Le Blanc" wrote in message
...
The second SQL statement creates an error that says you can't have an
aggregate function in a where clause as in
(MAX(meals.LastVisit)Date()-730

The meals table contains the client master ID, a meals master ID,
(those two create the combined key for the meals table), 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]















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

Dear Ron:

It's been years since I did this, but it seems to me that, if you take the
working SELECT query, open it in design view, right click in the empty
portion of the top portion of the windos, select Query Type, and then select
Delete Query, it will transform your SELECT query into an equivalent DELETE
query.

I beleive what it would say is:

DELETE client.*
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
GROUP BY client.MasterID
HAVING MAX(meals.LastVisit) Date() - 730

I'm not sure of this query either, but I'm pretty sure using the Design View
capacities to create it will be reliable. Of course, always make a backup
of your data before running anything I suggest. : )

Or anything else new and uncertain you are just testing for the first time.
Or the second time.

Did I mention before that I'm concerned about the use of Date() in this
query. If you system clock is somehow accidentally set forward to 2/9/2008,
it will delete all your data. What is deleted here depends on the system
clock being set correctly. Having the user enter the desired date might be
better, but that too is subject to mis-typing. The best thing I can think
of at the moment is to rank your data by date and choose a date from that
which assures you the most recent 200, 500, 10000, or whatever number of
records are most recent will not be deleted. Test the date for your
deletions against that. It must be a date prior to your 500 most recent
records.

A good question might be to consider for yourself why it is so necessary to
delete them at all. You could use a similar query mechanism to just
disregard them for reporting or forms purposes, and let them be. This
avoids the possible tragic loss of historic information, especially if your
backup scheme is not highly reliable. If at some future time you want to
change the number of days from 730 to what seems better at that time,
everything would adjust to the new limit immediately. If you set it to 1000
days, you'd instantly have 270 days more information show up (assuming there
is some in your table). If table size (the 2 GB max for Jet files, for
example) or performance is your goal, then archiving them first to a
separate database would be an option. Ask yourself, if all the data you
have accumulated in this table were to disappear due to such a malfunction,
what amount of time and money would it cost? Now are you motivated to
protect it thoroughly?

Well, I've given you the rope to hang yourself. : ) What you do with it
is up to you!

Tom Ellison


"Ron Le Blanc" wrote in message
...
Dear Tom,

First I thank you for all the help you have given me thus far. This is
a nasty problem!

The last SQL query you gave me (below) runs but gives the following error
message:

"Specify the table containing the records you want to delete"

If I make it a Select query it selects the correct clients!! But a Delete
query give the above error message.

One more tiny tweak maybe?

Thanks and take care...

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

Well, I used to be able to write queries. Actually, it's easy enough
when you have the database in front of you.

I think I had the order wrong, didn't I?

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

I've been on my own problem almost straight for 48 hours. Please
forgive. I'm too tired, or old, or something.

Tom Ellison


"Ron Le Blanc" wrote in message
...
Dear Tom,

Syntax error in HAVING clause

Sigh

Take care,

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

Maybe I should make excuses. Not.

OK, this may be what is required:

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

Funny what you forget at times. I THINK this is what is needed.
Please get back, OK? This is sanity check day for me!

Tom Ellison


"Ron Le Blanc" wrote in message
...
The second SQL statement creates an error that says you can't have an
aggregate function in a where clause as in
(MAX(meals.LastVisit)Date()-730

The meals table contains the client master ID, a meals master ID,
(those two create the combined key for the meals table), 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]

















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

Now, in a spelling bee, I would never spell it "beleive", but when I let my
fingers to the walking (TM) strange things happen. So, please, make that
"believe" OK?

Tom Ellison


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

It's been years since I did this, but it seems to me that, if you take the
working SELECT query, open it in design view, right click in the empty
portion of the top portion of the windos, select Query Type, and then
select Delete Query, it will transform your SELECT query into an
equivalent DELETE query.

I beleive what it would say is:

DELETE client.*
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
GROUP BY client.MasterID
HAVING MAX(meals.LastVisit) Date() - 730

I'm not sure of this query either, but I'm pretty sure using the Design
View capacities to create it will be reliable. Of course, always make a
backup of your data before running anything I suggest. : )

Or anything else new and uncertain you are just testing for the first
time. Or the second time.

Did I mention before that I'm concerned about the use of Date() in this
query. If you system clock is somehow accidentally set forward to
2/9/2008, it will delete all your data. What is deleted here depends on
the system clock being set correctly. Having the user enter the desired
date might be better, but that too is subject to mis-typing. The best
thing I can think of at the moment is to rank your data by date and choose
a date from that which assures you the most recent 200, 500, 10000, or
whatever number of records are most recent will not be deleted. Test the
date for your deletions against that. It must be a date prior to your 500
most recent records.

A good question might be to consider for yourself why it is so necessary
to delete them at all. You could use a similar query mechanism to just
disregard them for reporting or forms purposes, and let them be. This
avoids the possible tragic loss of historic information, especially if
your backup scheme is not highly reliable. If at some future time you
want to change the number of days from 730 to what seems better at that
time, everything would adjust to the new limit immediately. If you set it
to 1000 days, you'd instantly have 270 days more information show up
(assuming there is some in your table). If table size (the 2 GB max for
Jet files, for example) or performance is your goal, then archiving them
first to a separate database would be an option. Ask yourself, if all the
data you have accumulated in this table were to disappear due to such a
malfunction, what amount of time and money would it cost? Now are you
motivated to protect it thoroughly?

Well, I've given you the rope to hang yourself. : ) What you do with it
is up to you!

Tom Ellison


"Ron Le Blanc" wrote in message
...
Dear Tom,

First I thank you for all the help you have given me thus far. This is
a nasty problem!

The last SQL query you gave me (below) runs but gives the following error
message:

"Specify the table containing the records you want to delete"

If I make it a Select query it selects the correct clients!! But a Delete
query give the above error message.

One more tiny tweak maybe?

Thanks and take care...

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

Well, I used to be able to write queries. Actually, it's easy enough
when you have the database in front of you.

I think I had the order wrong, didn't I?

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

I've been on my own problem almost straight for 48 hours. Please
forgive. I'm too tired, or old, or something.

Tom Ellison


"Ron Le Blanc" wrote in message
...
Dear Tom,

Syntax error in HAVING clause

Sigh

Take care,

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

Maybe I should make excuses. Not.

OK, this may be what is required:

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

Funny what you forget at times. I THINK this is what is needed.
Please get back, OK? This is sanity check day for me!

Tom Ellison


"Ron Le Blanc" wrote in message
...
The second SQL statement creates an error that says you can't have an
aggregate function in a where clause as in
(MAX(meals.LastVisit)Date()-730

The meals table contains the client master ID, a meals master ID,
(those two create the combined key for the meals table), 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]



















  #18  
Old February 9th, 2006, 08:50 PM posted to microsoft.public.access.queries
external usenet poster
 
Posts: n/a
Default Delete Query Does Not

Sigh

Dear Tom,

Almost but no cigar. When I try to run the SQL query below it gives me the
following error:

Cannot group on fields selected with '*' (client)

If it would be easier I could zip up the files and send them to you. Ah,
only thing is you would have to agree that you never "look" at the actual
data as these are real Food Bank Clients who expect there data to be held
privately. Or I could send you the relationship diagrams, or both. Whatever
makes it easier for you.

Thanks for all your help!!
Take care,

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

It's been years since I did this, but it seems to me that, if you take the
working SELECT query, open it in design view, right click in the empty
portion of the top portion of the windos, select Query Type, and then
select Delete Query, it will transform your SELECT query into an
equivalent DELETE query.

I beleive what it would say is:

DELETE client.*
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
GROUP BY client.MasterID
HAVING MAX(meals.LastVisit) Date() - 730

I'm not sure of this query either, but I'm pretty sure using the Design
View capacities to create it will be reliable. Of course, always make a
backup of your data before running anything I suggest. : )

Or anything else new and uncertain you are just testing for the first
time. Or the second time.

Did I mention before that I'm concerned about the use of Date() in this
query. If you system clock is somehow accidentally set forward to
2/9/2008, it will delete all your data. What is deleted here depends on
the system clock being set correctly. Having the user enter the desired
date might be better, but that too is subject to mis-typing. The best
thing I can think of at the moment is to rank your data by date and choose
a date from that which assures you the most recent 200, 500, 10000, or
whatever number of records are most recent will not be deleted. Test the
date for your deletions against that. It must be a date prior to your 500
most recent records.

A good question might be to consider for yourself why it is so necessary
to delete them at all. You could use a similar query mechanism to just
disregard them for reporting or forms purposes, and let them be. This
avoids the possible tragic loss of historic information, especially if
your backup scheme is not highly reliable. If at some future time you
want to change the number of days from 730 to what seems better at that
time, everything would adjust to the new limit immediately. If you set it
to 1000 days, you'd instantly have 270 days more information show up
(assuming there is some in your table). If table size (the 2 GB max for
Jet files, for example) or performance is your goal, then archiving them
first to a separate database would be an option. Ask yourself, if all the
data you have accumulated in this table were to disappear due to such a
malfunction, what amount of time and money would it cost? Now are you
motivated to protect it thoroughly?

Well, I've given you the rope to hang yourself. : ) What you do with it
is up to you!

Tom Ellison


"Ron Le Blanc" wrote in message
...
Dear Tom,

First I thank you for all the help you have given me thus far. This is
a nasty problem!

The last SQL query you gave me (below) runs but gives the following error
message:

"Specify the table containing the records you want to delete"

If I make it a Select query it selects the correct clients!! But a Delete
query give the above error message.

One more tiny tweak maybe?

Thanks and take care...

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

Well, I used to be able to write queries. Actually, it's easy enough
when you have the database in front of you.

I think I had the order wrong, didn't I?

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

I've been on my own problem almost straight for 48 hours. Please
forgive. I'm too tired, or old, or something.

Tom Ellison


"Ron Le Blanc" wrote in message
...
Dear Tom,

Syntax error in HAVING clause

Sigh

Take care,

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

Maybe I should make excuses. Not.

OK, this may be what is required:

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

Funny what you forget at times. I THINK this is what is needed.
Please get back, OK? This is sanity check day for me!

Tom Ellison


"Ron Le Blanc" wrote in message
...
The second SQL statement creates an error that says you can't have an
aggregate function in a where clause as in
(MAX(meals.LastVisit)Date()-730

The meals table contains the client master ID, a meals master ID,
(those two create the combined key for the meals table), 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]



















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

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

Sorry I missed that.

Tom Ellison


"Ron Le Blanc" wrote in message
...
Sigh

Dear Tom,

Almost but no cigar. When I try to run the SQL query below it gives me the
following error:

Cannot group on fields selected with '*' (client)

If it would be easier I could zip up the files and send them to you. Ah,
only thing is you would have to agree that you never "look" at the actual
data as these are real Food Bank Clients who expect there data to be held
privately. Or I could send you the relationship diagrams, or both.
Whatever makes it easier for you.

Thanks for all your help!!
Take care,

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

It's been years since I did this, but it seems to me that, if you take
the working SELECT query, open it in design view, right click in the
empty portion of the top portion of the windos, select Query Type, and
then select Delete Query, it will transform your SELECT query into an
equivalent DELETE query.

I beleive what it would say is:

DELETE client.*
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
GROUP BY client.MasterID
HAVING MAX(meals.LastVisit) Date() - 730

I'm not sure of this query either, but I'm pretty sure using the Design
View capacities to create it will be reliable. Of course, always make a
backup of your data before running anything I suggest. : )

Or anything else new and uncertain you are just testing for the first
time. Or the second time.

Did I mention before that I'm concerned about the use of Date() in this
query. If you system clock is somehow accidentally set forward to
2/9/2008, it will delete all your data. What is deleted here depends on
the system clock being set correctly. Having the user enter the desired
date might be better, but that too is subject to mis-typing. The best
thing I can think of at the moment is to rank your data by date and
choose a date from that which assures you the most recent 200, 500,
10000, or whatever number of records are most recent will not be deleted.
Test the date for your deletions against that. It must be a date prior
to your 500 most recent records.

A good question might be to consider for yourself why it is so necessary
to delete them at all. You could use a similar query mechanism to just
disregard them for reporting or forms purposes, and let them be. This
avoids the possible tragic loss of historic information, especially if
your backup scheme is not highly reliable. If at some future time you
want to change the number of days from 730 to what seems better at that
time, everything would adjust to the new limit immediately. If you set
it to 1000 days, you'd instantly have 270 days more information show up
(assuming there is some in your table). If table size (the 2 GB max for
Jet files, for example) or performance is your goal, then archiving them
first to a separate database would be an option. Ask yourself, if all
the data you have accumulated in this table were to disappear due to such
a malfunction, what amount of time and money would it cost? Now are you
motivated to protect it thoroughly?

Well, I've given you the rope to hang yourself. : ) What you do with
it is up to you!

Tom Ellison


"Ron Le Blanc" wrote in message
...
Dear Tom,

First I thank you for all the help you have given me thus far. This
is a nasty problem!

The last SQL query you gave me (below) runs but gives the following
error message:

"Specify the table containing the records you want to delete"

If I make it a Select query it selects the correct clients!! But a
Delete query give the above error message.

One more tiny tweak maybe?

Thanks and take care...

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

Well, I used to be able to write queries. Actually, it's easy enough
when you have the database in front of you.

I think I had the order wrong, didn't I?

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

I've been on my own problem almost straight for 48 hours. Please
forgive. I'm too tired, or old, or something.

Tom Ellison


"Ron Le Blanc" wrote in message
...
Dear Tom,

Syntax error in HAVING clause

Sigh

Take care,

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

Maybe I should make excuses. Not.

OK, this may be what is required:

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

Funny what you forget at times. I THINK this is what is needed.
Please get back, OK? This is sanity check day for me!

Tom Ellison


"Ron Le Blanc" wrote in message
...
The second SQL statement creates an error that says you can't have
an aggregate function in a where clause as in
(MAX(meals.LastVisit)Date()-730

The meals table contains the client master ID, a meals master ID,
(those two create the combined key for the meals table), 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]





















  #20  
Old February 10th, 2006, 10:14 PM posted to microsoft.public.access.queries
external usenet poster
 
Posts: n/a
Default Delete Query Does Not

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]
 




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 12:56 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.