Re: 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" <rleblanc@xxxxxxxxxxxxx> wrote in message
news:LI-dnfgSXou9AHbenZ2dnUVZ_tGdnZ2d@xxxxxxxxxx
<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" <tellison@xxxxxxxxxxx> wrote in message
news:eHxpFPULGHA.3944@xxxxxxxxxxxxxxxxxxxxxxx
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" <rleblanc@xxxxxxxxxxxxx> wrote in message
news:c96dnajKWZQXQnfenZ2dnUVZ_tadnZ2d@xxxxxxxxxx
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" <tellison@xxxxxxxxxxx> wrote in message
news:%23pCVJbHLGHA.2704@xxxxxxxxxxxxxxxxxxxxxxx
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" <rleblanc@xxxxxxxxxxxxx> wrote in message
news:W9WdnUtRZsOXF3TeRVn-qg@xxxxxxxxxx
Dear Tom,

Syntax error in HAVING clause

<Sigh>

Take care,

"Tom Ellison" <tellison@xxxxxxxxxxx> wrote in message
news:%23lm0%23qFLGHA.604@xxxxxxxxxxxxxxxxxxxxxxx
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" <rleblanc@xxxxxxxxxxxxx> wrote in message
news:NpudnbrnJqiG2nTenZ2dnUVZ_tKdnZ2d@xxxxxxxxxx
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" <tellison@xxxxxxxxxxx> wrote in message
news:usBB3btKGHA.3836@xxxxxxxxxxxxxxxxxxxxxxx
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" <rleblanc@xxxxxxxxxxxxx> wrote in message
news:P6idnToUh84uU3venZ2dnUVZ_sOdnZ2d@xxxxxxxxxx
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" <jvinson@xxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:9j0bu19cn0dtigv3mojqbrlddascj5i004@xxxxxxxxxx
On Sat, 4 Feb 2006 23:15:16 -0500, "Ron Le Blanc"
<rleblanc@xxxxxxxxxxxxx> 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]





















.

Relevant Pages

  • lame server messages in named.log
    ... Mar 30 05:42:30.526 security: info: client 202.52.250.176#1052: ... query (cache) denied ...
    (RedHat)
  • lame server messages in named.log
    ... Mar 30 05:42:30.526 security: info: client 202.52.250.176#1052: ... query (cache) denied ...
    (RedHat)
  • Re: Delete Query Does Not
    ... Tom Ellison ... working SELECT query, open it in design view, right click in the empty ... DELETE client.* ... INNER JOIN meals ...
    (microsoft.public.access.queries)
  • Re: Delete Query Does Not
    ... portion of the top portion of the windos, select Query Type, and then select ... DELETE client.* ... INNER JOIN meals ... The error message says: "Cannot delete from table". ...
    (microsoft.public.access.queries)
  • Re: Delete Query Does Not
    ... If I make it a Select query it selects the correct clients!! ... FROM client ... INNER JOIN meals ... The error message says: "Cannot delete from table". ...
    (microsoft.public.access.queries)
Loading