Re: 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" <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

  • 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
    ... When I try to run the SQL query below it gives me the ... Cannot group on fields selected with '*' (client) ... INNER JOIN meals ... Tom Ellison ...
    (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)
  • Re: Delete Query Does Not
    ... INNER JOIN meals ... The meals table contains the client master ID, a meals master ID, (those ... If you have several rows in each with a different LastVisit, ... I have presented this as a SELECT query not a DELETE. ...
    (microsoft.public.access.queries)
  • Re: Query Using Multiple Categories
    ... Tom Ellison ... >Category - Category ID, Client ID, Type ... How can I perform a query to show all records belonging to more than one ...
    (microsoft.public.access.queries)