Delete query unable to delete records from table. Fix how?

G

Guest

I have a form which generates records in a table on "close".
Every time the form is closed it generates another set of records.
Therefore, I end up with many duplicate records in the table.
I created a "Find Duplicates" query which works perfectly.
Then I created a "Delete Duplicate Records" query following instructions
from Access help by joining the table and "Find Duplicates" query on a
common field. Examination of the query in datasheet view show ALL the
correct records.
When I try to run the "Delete Duplicate Records" query, I get a warning
message "Could not delete from specified tables." error message and the
process stops. I am the ONLY user of the computer and software and am set up
as administrator, so I don't think it's a permission issue on that level. (I
am able to delete any and all records from any table in the database
manually.) Any suggestions, other than jumping out a window???
 
A

Allen Browne

Post the SQL statement of your query.

It can probably be solved by using a subquery instead of a frustrated join.
 
G

Guest

Hi Allen Browne,
Thanks for the response.

Here's the SQL statement.

DELETE [tbl:Expenses - Individual].* AS tbl, [tbl:Expenses -
Individual].[Tenant ID #], [tbl:Expenses - Individual].[Item #],
[tbl:Expenses - Individual].[Date Billed], [tbl:Expenses -
Individual].Reference, [tbl:Expenses - Individual].Description, [tbl:Expenses
- Individual].Amount, [tbl:Expenses - Individual].[Date Entered]
FROM [tbl:Expenses - Individual] INNER JOIN [qry: EXPENSES - INDIVIDUAL -
DISTINCT] AS [Distinct] ON [tbl:Expenses - Individual].[Tenant ID #] =
Distinct.[Tenant ID #]
WHERE ((([tbl:Expenses - Individual].[Tenant ID #])=[Distinct].[Tenant ID
#]) AND (([tbl:Expenses - Individual].[Item #])=[Distinct].[MinOfItem #]) AND
(([tbl:Expenses - Individual].[Date Billed])=[Distinct].[Date Billed]) AND
(([tbl:Expenses - Individual].Reference)=[Distinct].[Reference]) AND
(([tbl:Expenses - Individual].Description)=[Distinct].[Description]) AND
(([tbl:Expenses - Individual].Amount)=[Distinct].[Amount]) AND
(([tbl:Expenses - Individual].[Date Entered])=[Distinct].[Date Entered]));
 
A

Allen Browne

Try a subquery instead of a JOIN.

Something like this:

DELETE FROM [tbl:Expenses - Individual]
WHERE EXISTS (SELECT qryExpIndiv.[Tenant ID #]
FROM [qry: EXPENSES - INDIVIDUAL - DISTINCT] AS qryExpIndiv
WHERE ([tbl:Expenses - Individual].[Tenant ID #] = qryExpIndiv.[Tenant ID
#])
AND ([tbl:Expenses - Individual].[Item #] = qryExpIndiv.[MinOfItem #])
AND ([tbl:Expenses - Individual].[Date Billed] = qryExpIndiv.[Date Billed])
AND ([tbl:Expenses - Individual].Reference = qryExpIndiv.[Reference])
AND ([tbl:Expenses - Individual].Description = qryExpIndiv.[Description])
AND ([tbl:Expenses - Individual].Amount = qryExpIndiv.[Amount])
AND ([tbl:Expenses - Individual].[Date Entered] = qryExpIndiv.[Date
Entered]));

For an explanation of subqueries, see:
http://allenbrowne.com/subquery-01.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

LongTom said:
Hi Allen Browne,
Thanks for the response.

Here's the SQL statement.

DELETE [tbl:Expenses - Individual].* AS tbl, [tbl:Expenses -
Individual].[Tenant ID #], [tbl:Expenses - Individual].[Item #],
[tbl:Expenses - Individual].[Date Billed], [tbl:Expenses -
Individual].Reference, [tbl:Expenses - Individual].Description,
[tbl:Expenses
- Individual].Amount, [tbl:Expenses - Individual].[Date Entered]
FROM [tbl:Expenses - Individual] INNER JOIN [qry: EXPENSES - INDIVIDUAL -
DISTINCT] AS [Distinct] ON [tbl:Expenses - Individual].[Tenant ID #] =
Distinct.[Tenant ID #]
WHERE ((([tbl:Expenses - Individual].[Tenant ID #])=[Distinct].[Tenant ID
#]) AND (([tbl:Expenses - Individual].[Item #])=[Distinct].[MinOfItem #])
AND
(([tbl:Expenses - Individual].[Date Billed])=[Distinct].[Date Billed]) AND
(([tbl:Expenses - Individual].Reference)=[Distinct].[Reference]) AND
(([tbl:Expenses - Individual].Description)=[Distinct].[Description]) AND
(([tbl:Expenses - Individual].Amount)=[Distinct].[Amount]) AND
(([tbl:Expenses - Individual].[Date Entered])=[Distinct].[Date Entered]));


Allen Browne said:
Post the SQL statement of your query.

It can probably be solved by using a subquery instead of a frustrated
join.
Click to expand...
Click to expand...
 
G

Guest

I inserted this into an SQL view window and then tried to run it. Clearly
I'm missing something. How do I implement this correctly. Thanks.

Allen Browne said:
Try a subquery instead of a JOIN.

Something like this:

DELETE FROM [tbl:Expenses - Individual]
WHERE EXISTS (SELECT qryExpIndiv.[Tenant ID #]
FROM [qry: EXPENSES - INDIVIDUAL - DISTINCT] AS qryExpIndiv
WHERE ([tbl:Expenses - Individual].[Tenant ID #] = qryExpIndiv.[Tenant ID
#])
AND ([tbl:Expenses - Individual].[Item #] = qryExpIndiv.[MinOfItem #])
AND ([tbl:Expenses - Individual].[Date Billed] = qryExpIndiv.[Date Billed])
AND ([tbl:Expenses - Individual].Reference = qryExpIndiv.[Reference])
AND ([tbl:Expenses - Individual].Description = qryExpIndiv.[Description])
AND ([tbl:Expenses - Individual].Amount = qryExpIndiv.[Amount])
AND ([tbl:Expenses - Individual].[Date Entered] = qryExpIndiv.[Date
Entered]));

For an explanation of subqueries, see:
http://allenbrowne.com/subquery-01.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

LongTom said:
Hi Allen Browne,
Thanks for the response.

Here's the SQL statement.

DELETE [tbl:Expenses - Individual].* AS tbl, [tbl:Expenses -
Individual].[Tenant ID #], [tbl:Expenses - Individual].[Item #],
[tbl:Expenses - Individual].[Date Billed], [tbl:Expenses -
Individual].Reference, [tbl:Expenses - Individual].Description,
[tbl:Expenses
- Individual].Amount, [tbl:Expenses - Individual].[Date Entered]
FROM [tbl:Expenses - Individual] INNER JOIN [qry: EXPENSES - INDIVIDUAL -
DISTINCT] AS [Distinct] ON [tbl:Expenses - Individual].[Tenant ID #] =
Distinct.[Tenant ID #]
WHERE ((([tbl:Expenses - Individual].[Tenant ID #])=[Distinct].[Tenant ID
#]) AND (([tbl:Expenses - Individual].[Item #])=[Distinct].[MinOfItem #])
AND
(([tbl:Expenses - Individual].[Date Billed])=[Distinct].[Date Billed]) AND
(([tbl:Expenses - Individual].Reference)=[Distinct].[Reference]) AND
(([tbl:Expenses - Individual].Description)=[Distinct].[Description]) AND
(([tbl:Expenses - Individual].Amount)=[Distinct].[Amount]) AND
(([tbl:Expenses - Individual].[Date Entered])=[Distinct].[Date Entered]));


Allen Browne said:
Post the SQL statement of your query.

It can probably be solved by using a subquery instead of a frustrated
join.

I have a form which generates records in a table on "close".
Every time the form is closed it generates another set of records.
Therefore, I end up with many duplicate records in the table.
I created a "Find Duplicates" query which works perfectly.
Then I created a "Delete Duplicate Records" query following
instructions
from Access help by joining the table and "Find Duplicates" query on a
common field. Examination of the query in datasheet view show ALL the
correct records.
When I try to run the "Delete Duplicate Records" query, I get a warning
message "Could not delete from specified tables." error message and the
process stops. I am the ONLY user of the computer and software and am
set
up
as administrator, so I don't think it's a permission issue on that
level.
(I
am able to delete any and all records from any table in the database
manually.) Any suggestions, other than jumping out a window???
Click to expand...
Click to expand...
Click to expand...
 
A

Allen Browne

That's the right approach: paste into SQL View, and try switching ot design
view.

Bear in mind that I don't have your tables, so it's just an untested
example. You may need to fix it up if there are mismatched brackets or if
the names are not right.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

LongTom said:
I inserted this into an SQL view window and then tried to run it. Clearly
I'm missing something. How do I implement this correctly. Thanks.

Allen Browne said:
Try a subquery instead of a JOIN.

Something like this:

DELETE FROM [tbl:Expenses - Individual]
WHERE EXISTS (SELECT qryExpIndiv.[Tenant ID #]
FROM [qry: EXPENSES - INDIVIDUAL - DISTINCT] AS qryExpIndiv
WHERE ([tbl:Expenses - Individual].[Tenant ID #] = qryExpIndiv.[Tenant ID
#])
AND ([tbl:Expenses - Individual].[Item #] = qryExpIndiv.[MinOfItem #])
AND ([tbl:Expenses - Individual].[Date Billed] = qryExpIndiv.[Date
Billed])
AND ([tbl:Expenses - Individual].Reference = qryExpIndiv.[Reference])
AND ([tbl:Expenses - Individual].Description = qryExpIndiv.[Description])
AND ([tbl:Expenses - Individual].Amount = qryExpIndiv.[Amount])
AND ([tbl:Expenses - Individual].[Date Entered] = qryExpIndiv.[Date
Entered]));

For an explanation of subqueries, see:
http://allenbrowne.com/subquery-01.html

LongTom said:
Hi Allen Browne,
Thanks for the response.

Here's the SQL statement.

DELETE [tbl:Expenses - Individual].* AS tbl, [tbl:Expenses -
Individual].[Tenant ID #], [tbl:Expenses - Individual].[Item #],
[tbl:Expenses - Individual].[Date Billed], [tbl:Expenses -
Individual].Reference, [tbl:Expenses - Individual].Description,
[tbl:Expenses
- Individual].Amount, [tbl:Expenses - Individual].[Date Entered]
FROM [tbl:Expenses - Individual] INNER JOIN [qry: EXPENSES -
INDIVIDUAL -
DISTINCT] AS [Distinct] ON [tbl:Expenses - Individual].[Tenant ID #] =
Distinct.[Tenant ID #]
WHERE ((([tbl:Expenses - Individual].[Tenant ID #])=[Distinct].[Tenant
ID
#]) AND (([tbl:Expenses - Individual].[Item #])=[Distinct].[MinOfItem
#])
AND
(([tbl:Expenses - Individual].[Date Billed])=[Distinct].[Date Billed])
AND
(([tbl:Expenses - Individual].Reference)=[Distinct].[Reference]) AND
(([tbl:Expenses - Individual].Description)=[Distinct].[Description])
AND
(([tbl:Expenses - Individual].Amount)=[Distinct].[Amount]) AND
(([tbl:Expenses - Individual].[Date Entered])=[Distinct].[Date
Entered]));


:

Post the SQL statement of your query.

It can probably be solved by using a subquery instead of a frustrated
join.

I have a form which generates records in a table on "close".
Every time the form is closed it generates another set of records.
Therefore, I end up with many duplicate records in the table.
I created a "Find Duplicates" query which works perfectly.
Then I created a "Delete Duplicate Records" query following
instructions
from Access help by joining the table and "Find Duplicates" query on
a
common field. Examination of the query in datasheet view show ALL
the
correct records.
When I try to run the "Delete Duplicate Records" query, I get a
warning
message "Could not delete from specified tables." error message and
the
process stops. I am the ONLY user of the computer and software and
am
set
up
as administrator, so I don't think it's a permission issue on that
level.
(I
am able to delete any and all records from any table in the database
manually.) Any suggestions, other than jumping out a window???
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

These are the steps I am performing in order.
1. Open new query in design view.
2. Switch to SQL view.
3. Paste your subquery (I did fix several brackets. You do have the table
and original query names correct.
4. Switch to design view. The table "tbl:Expenses - Inividual" is added to
design view.
5. Switch to datasheet view. Get error message "Query must have at least
one destination field."
6. Add fields from "tbl:Expenses - Inividual" to columns in design view.
7. Switch to datasheet view.
8. Get "Enter Parameter Value?" on "qryExpIndiv.Tenant ID" and all other
fields.
9. Switch to datasheet view. No records displayed.
What should I do next?
Allen Browne said:
That's the right approach: paste into SQL View, and try switching ot design
view.

Bear in mind that I don't have your tables, so it's just an untested
example. You may need to fix it up if there are mismatched brackets or if
the names are not right.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

LongTom said:
I inserted this into an SQL view window and then tried to run it. Clearly
I'm missing something. How do I implement this correctly. Thanks.

Allen Browne said:
Try a subquery instead of a JOIN.

Something like this:

DELETE FROM [tbl:Expenses - Individual]
WHERE EXISTS (SELECT qryExpIndiv.[Tenant ID #]
FROM [qry: EXPENSES - INDIVIDUAL - DISTINCT] AS qryExpIndiv
WHERE ([tbl:Expenses - Individual].[Tenant ID #] = qryExpIndiv.[Tenant ID
#])
AND ([tbl:Expenses - Individual].[Item #] = qryExpIndiv.[MinOfItem #])
AND ([tbl:Expenses - Individual].[Date Billed] = qryExpIndiv.[Date
Billed])
AND ([tbl:Expenses - Individual].Reference = qryExpIndiv.[Reference])
AND ([tbl:Expenses - Individual].Description = qryExpIndiv.[Description])
AND ([tbl:Expenses - Individual].Amount = qryExpIndiv.[Amount])
AND ([tbl:Expenses - Individual].[Date Entered] = qryExpIndiv.[Date
Entered]));

For an explanation of subqueries, see:
http://allenbrowne.com/subquery-01.html

Hi Allen Browne,
Thanks for the response.

Here's the SQL statement.

DELETE [tbl:Expenses - Individual].* AS tbl, [tbl:Expenses -
Individual].[Tenant ID #], [tbl:Expenses - Individual].[Item #],
[tbl:Expenses - Individual].[Date Billed], [tbl:Expenses -
Individual].Reference, [tbl:Expenses - Individual].Description,
[tbl:Expenses
- Individual].Amount, [tbl:Expenses - Individual].[Date Entered]
FROM [tbl:Expenses - Individual] INNER JOIN [qry: EXPENSES -
INDIVIDUAL -
DISTINCT] AS [Distinct] ON [tbl:Expenses - Individual].[Tenant ID #] =
Distinct.[Tenant ID #]
WHERE ((([tbl:Expenses - Individual].[Tenant ID #])=[Distinct].[Tenant
ID
#]) AND (([tbl:Expenses - Individual].[Item #])=[Distinct].[MinOfItem
#])
AND
(([tbl:Expenses - Individual].[Date Billed])=[Distinct].[Date Billed])
AND
(([tbl:Expenses - Individual].Reference)=[Distinct].[Reference]) AND
(([tbl:Expenses - Individual].Description)=[Distinct].[Description])
AND
(([tbl:Expenses - Individual].Amount)=[Distinct].[Amount]) AND
(([tbl:Expenses - Individual].[Date Entered])=[Distinct].[Date
Entered]));


:

Post the SQL statement of your query.

It can probably be solved by using a subquery instead of a frustrated
join.

I have a form which generates records in a table on "close".
Every time the form is closed it generates another set of records.
Therefore, I end up with many duplicate records in the table.
I created a "Find Duplicates" query which works perfectly.
Then I created a "Delete Duplicate Records" query following
instructions
from Access help by joining the table and "Find Duplicates" query on
a
common field. Examination of the query in datasheet view show ALL
the
correct records.
When I try to run the "Delete Duplicate Records" query, I get a
warning
message "Could not delete from specified tables." error message and
the
process stops. I am the ONLY user of the computer and software and
am
set
up
as administrator, so I don't think it's a permission issue on that
level.
(I
am able to delete any and all records from any table in the database
manually.) Any suggestions, other than jumping out a window???
Click to expand...
Click to expand...
Click to expand...
 
A

Allen Browne

Tom, it seems that's not going anywhere. Perhaps we need to back up and see
if there is a way to avoid the bad data getting into the table in the first
place, rather than shutting the gate after the horse has bolted.

In your original post, you say you have a form that "generates records in a
table on 'close'." I don't see the logic of creating these records, and then
deleting them.

Is this a bound form that is saving records it should not?
Or are you executing an Append query to add the new records?
Might there be a way to avoid adding the bad records, or at least to
conditionally add them only if doing so is valid?

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

LongTom said:
These are the steps I am performing in order.
1. Open new query in design view.
2. Switch to SQL view.
3. Paste your subquery (I did fix several brackets. You do have the table
and original query names correct.
4. Switch to design view. The table "tbl:Expenses - Inividual" is added
to
design view.
5. Switch to datasheet view. Get error message "Query must have at least
one destination field."
6. Add fields from "tbl:Expenses - Inividual" to columns in design view.
7. Switch to datasheet view.
8. Get "Enter Parameter Value?" on "qryExpIndiv.Tenant ID" and all other
fields.
9. Switch to datasheet view. No records displayed.
What should I do next?
Allen Browne said:
That's the right approach: paste into SQL View, and try switching ot
design
view.

Bear in mind that I don't have your tables, so it's just an untested
example. You may need to fix it up if there are mismatched brackets or if
the names are not right.

LongTom said:
I inserted this into an SQL view window and then tried to run it.
Clearly
I'm missing something. How do I implement this correctly. Thanks.

:

Try a subquery instead of a JOIN.

Something like this:

DELETE FROM [tbl:Expenses - Individual]
WHERE EXISTS (SELECT qryExpIndiv.[Tenant ID #]
FROM [qry: EXPENSES - INDIVIDUAL - DISTINCT] AS qryExpIndiv
WHERE ([tbl:Expenses - Individual].[Tenant ID #] = qryExpIndiv.[Tenant
ID
#])
AND ([tbl:Expenses - Individual].[Item #] = qryExpIndiv.[MinOfItem #])
AND ([tbl:Expenses - Individual].[Date Billed] = qryExpIndiv.[Date
Billed])
AND ([tbl:Expenses - Individual].Reference = qryExpIndiv.[Reference])
AND ([tbl:Expenses - Individual].Description =
qryExpIndiv.[Description])
AND ([tbl:Expenses - Individual].Amount = qryExpIndiv.[Amount])
AND ([tbl:Expenses - Individual].[Date Entered] = qryExpIndiv.[Date
Entered]));

For an explanation of subqueries, see:
http://allenbrowne.com/subquery-01.html

Hi Allen Browne,
Thanks for the response.

Here's the SQL statement.

DELETE [tbl:Expenses - Individual].* AS tbl, [tbl:Expenses -
Individual].[Tenant ID #], [tbl:Expenses - Individual].[Item #],
[tbl:Expenses - Individual].[Date Billed], [tbl:Expenses -
Individual].Reference, [tbl:Expenses - Individual].Description,
[tbl:Expenses
- Individual].Amount, [tbl:Expenses - Individual].[Date Entered]
FROM [tbl:Expenses - Individual] INNER JOIN [qry: EXPENSES -
INDIVIDUAL -
DISTINCT] AS [Distinct] ON [tbl:Expenses - Individual].[Tenant ID #]
=
Distinct.[Tenant ID #]
WHERE ((([tbl:Expenses - Individual].[Tenant ID
#])=[Distinct].[Tenant
ID
#]) AND (([tbl:Expenses - Individual].[Item
#])=[Distinct].[MinOfItem
#])
AND
(([tbl:Expenses - Individual].[Date Billed])=[Distinct].[Date
Billed])
AND
(([tbl:Expenses - Individual].Reference)=[Distinct].[Reference]) AND
(([tbl:Expenses - Individual].Description)=[Distinct].[Description])
AND
(([tbl:Expenses - Individual].Amount)=[Distinct].[Amount]) AND
(([tbl:Expenses - Individual].[Date Entered])=[Distinct].[Date
Entered]));


:

Post the SQL statement of your query.

It can probably be solved by using a subquery instead of a
frustrated
join.

I have a form which generates records in a table on "close".
Every time the form is closed it generates another set of
records.
Therefore, I end up with many duplicate records in the table.
I created a "Find Duplicates" query which works perfectly.
Then I created a "Delete Duplicate Records" query following
instructions
from Access help by joining the table and "Find Duplicates" query
on
a
common field. Examination of the query in datasheet view show
ALL
the
correct records.
When I try to run the "Delete Duplicate Records" query, I get a
warning
message "Could not delete from specified tables." error message
and
the
process stops. I am the ONLY user of the computer and software
and
am
set
up
as administrator, so I don't think it's a permission issue on
that
level.
(I
am able to delete any and all records from any table in the
database
manually.) Any suggestions, other than jumping out a window???
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

deleting duplicate records obtained from a duplicate query 4
Deleting duplicate records 3
Delete Dups Query Failing 3
remove duplicate records in Access 2007 7
Delete Query Help 2
cannot delete from "specified tables" 3
Delete Duplicates with a Delete Query 3
Delete Query wont work 3

Top