Delete Query Doesnt

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

Attached are the DB table relationships in a .png image file.

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!

Please don't attach files to postings in these news groups. I hope no one
opens any attached files from people we don't know.

If you change your query to a select query, can you edit any values?
Does this work?
DELETE client.*
FROM client INNER JOIN meals ON client.MasterID = meals.MasterID
WHERE meals.LastVisitDate()-730;


--
Duane Hookom
MS Access MVP
--

"Ron Le Blanc" wrote in message
...

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

Attached are the DB table relationships in a .png image file.

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!

This does not work either. Some clients who we served in 2005 were included
in the list of clients to delete.

For the record, BOTH referential integrity and cascade delete are set in the
table relationships.

The meals.LastVisit is a date in the meals table. It can have many entries
depending on how many times the client has come in. The query does not seem
to be selecting the right date in the meals table to determine if a client
has been withing two years. Also, the last date entered in the meals table
is the last time that the client was served.

If you are wondering, this is for a food bank that feeds over 750+ clients a
month.

Help needed. I don't understand why dates more recent than two years old are
being selected.


"Duane Hookom" wrote in message
...
Please don't attach files to postings in these news groups. I hope no one
opens any attached files from people we don't know.

If you change your query to a select query, can you edit any values?
Does this work?
DELETE client.*
FROM client INNER JOIN meals ON client.MasterID = meals.MasterID
WHERE meals.LastVisitDate()-730;


--
Duane Hookom
MS Access MVP
--

"Ron Le Blanc" wrote in message
...

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

Attached are the DB table relationships in a .png image file.

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!

Please refer to the replies in one of your other threads. Both Tom Ellison
and John Vinson are helping you in a more recent thread.

--
Duane Hookom
MS Access MVP
--

"Ron Le Blanc" wrote in message
...
This does not work either. Some clients who we served in 2005 were
included in the list of clients to delete.

For the record, BOTH referential integrity and cascade delete are set in
the table relationships.

The meals.LastVisit is a date in the meals table. It can have many entries
depending on how many times the client has come in. The query does not
seem to be selecting the right date in the meals table to determine if a
client has been withing two years. Also, the last date entered in the
meals table is the last time that the client was served.

If you are wondering, this is for a food bank that feeds over 750+ clients
a month.

Help needed. I don't understand why dates more recent than two years old
are being selected.


"Duane Hookom" wrote in message
...
Please don't attach files to postings in these news groups. I hope no one
opens any attached files from people we don't know.

If you change your query to a select query, can you edit any values?
Does this work?
DELETE client.*
FROM client INNER JOIN meals ON client.MasterID = meals.MasterID
WHERE meals.LastVisitDate()-730;


--
Duane Hookom
MS Access MVP
--

"Ron Le Blanc" wrote in message
...

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

Attached are the DB table relationships in a .png image file.

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!

Yup! A new facet to multi-posting.

Tom Ellison


"Duane Hookom" wrote in message
...
Please refer to the replies in one of your other threads. Both Tom Ellison
and John Vinson are helping you in a more recent thread.

--
Duane Hookom
MS Access MVP
--

"Ron Le Blanc" wrote in message
...
This does not work either. Some clients who we served in 2005 were
included in the list of clients to delete.

For the record, BOTH referential integrity and cascade delete are set in
the table relationships.

The meals.LastVisit is a date in the meals table. It can have many
entries depending on how many times the client has come in. The query
does not seem to be selecting the right date in the meals table to
determine if a client has been withing two years. Also, the last date
entered in the meals table is the last time that the client was served.

If you are wondering, this is for a food bank that feeds over 750+
clients a month.

Help needed. I don't understand why dates more recent than two years old
are being selected.


"Duane Hookom" wrote in message
...
Please don't attach files to postings in these news groups. I hope no
one opens any attached files from people we don't know.

If you change your query to a select query, can you edit any values?
Does this work?
DELETE client.*
FROM client INNER JOIN meals ON client.MasterID = meals.MasterID
WHERE meals.LastVisitDate()-730;


--
Duane Hookom
MS Access MVP
--

"Ron Le Blanc" wrote in message
...

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

Attached are the DB table relationships in a .png image file.

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!

The meals table contains the client master ID, a meals master ID, a list of
dates, 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
...
Yup! A new facet to multi-posting.

Tom Ellison


"Duane Hookom" wrote in message
...
Please refer to the replies in one of your other threads. Both Tom
Ellison and John Vinson are helping you in a more recent thread.

--
Duane Hookom
MS Access MVP
--

"Ron Le Blanc" wrote in message
...
This does not work either. Some clients who we served in 2005 were
included in the list of clients to delete.

For the record, BOTH referential integrity and cascade delete are set in
the table relationships.

The meals.LastVisit is a date in the meals table. It can have many
entries depending on how many times the client has come in. The query
does not seem to be selecting the right date in the meals table to
determine if a client has been withing two years. Also, the last date
entered in the meals table is the last time that the client was served.

If you are wondering, this is for a food bank that feeds over 750+
clients a month.

Help needed. I don't understand why dates more recent than two years old
are being selected.


"Duane Hookom" wrote in message
...
Please don't attach files to postings in these news groups. I hope no
one opens any attached files from people we don't know.

If you change your query to a select query, can you edit any values?
Does this work?
DELETE client.*
FROM client INNER JOIN meals ON client.MasterID = meals.MasterID
WHERE meals.LastVisitDate()-730;


--
Duane Hookom
MS Access MVP
--

"Ron Le Blanc" wrote in message
...

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

Attached are the DB table relationships in a .png image file.

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!

Dear Ron:

You will find my replies only in the most recent copy of your thread. I
recommend you respond only there.

Tom Ellison


"Ron Le Blanc" wrote in message
...
The meals table contains the client master ID, a meals master ID, a list
of dates, 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
...
Yup! A new facet to multi-posting.

Tom Ellison


"Duane Hookom" wrote in message
...
Please refer to the replies in one of your other threads. Both Tom
Ellison and John Vinson are helping you in a more recent thread.

--
Duane Hookom
MS Access MVP
--

"Ron Le Blanc" wrote in message
...
This does not work either. Some clients who we served in 2005 were
included in the list of clients to delete.

For the record, BOTH referential integrity and cascade delete are set
in the table relationships.

The meals.LastVisit is a date in the meals table. It can have many
entries depending on how many times the client has come in. The query
does not seem to be selecting the right date in the meals table to
determine if a client has been withing two years. Also, the last date
entered in the meals table is the last time that the client was served.

If you are wondering, this is for a food bank that feeds over 750+
clients a month.

Help needed. I don't understand why dates more recent than two years
old are being selected.


"Duane Hookom" wrote in message
...
Please don't attach files to postings in these news groups. I hope no
one opens any attached files from people we don't know.

If you change your query to a select query, can you edit any values?
Does this work?
DELETE client.*
FROM client INNER JOIN meals ON client.MasterID = meals.MasterID
WHERE meals.LastVisitDate()-730;


--
Duane Hookom
MS Access MVP
--

"Ron Le Blanc" wrote in message
...

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

Attached are the DB table relationships in a .png image file.

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!

Dear Tom,

I have and will reply to the other thread on this problem. This is the
end of this thread!

Take care,

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

You will find my replies only in the most recent copy of your thread. I
recommend you respond only there.

Tom Ellison


"Ron Le Blanc" wrote in message
...
The meals table contains the client master ID, a meals master ID, a list
of dates, 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
...
Yup! A new facet to multi-posting.

Tom Ellison


"Duane Hookom" wrote in message
...
Please refer to the replies in one of your other threads. Both Tom
Ellison and John Vinson are helping you in a more recent thread.

--
Duane Hookom
MS Access MVP
--

"Ron Le Blanc" wrote in message
...
This does not work either. Some clients who we served in 2005 were
included in the list of clients to delete.

For the record, BOTH referential integrity and cascade delete are set
in the table relationships.

The meals.LastVisit is a date in the meals table. It can have many
entries depending on how many times the client has come in. The query
does not seem to be selecting the right date in the meals table to
determine if a client has been withing two years. Also, the last date
entered in the meals table is the last time that the client was
served.

If you are wondering, this is for a food bank that feeds over 750+
clients a month.

Help needed. I don't understand why dates more recent than two years
old are being selected.


"Duane Hookom" wrote in message
...
Please don't attach files to postings in these news groups. I hope no
one opens any attached files from people we don't know.

If you change your query to a select query, can you edit any values?
Does this work?
DELETE client.*
FROM client INNER JOIN meals ON client.MasterID = meals.MasterID
WHERE meals.LastVisitDate()-730;


--
Duane Hookom
MS Access MVP
--

"Ron Le Blanc" wrote in message
...

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

Attached are the DB table relationships in a .png image file.

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!