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???

Post the SQL statement of your query.

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"LongTom" <(E-Mail Removed)> wrote in message
news:BF804A4B-6A34-47F8-A509-(E-Mail Removed)...
>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???

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

> Post the SQL statement of your query.
>
> It can probably be solved by using a subquery instead of a frustrated join.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "LongTom" <(E-Mail Removed)> wrote in message
> news:BF804A4B-6A34-47F8-A509-(E-Mail Removed)...
> >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???
>
>

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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"LongTom" <(E-Mail Removed)> wrote in message
news:018121BA-5984-456C-827F-(E-Mail Removed)...
> 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" wrote:
>
>> Post the SQL statement of your query.
>>
>> It can probably be solved by using a subquery instead of a frustrated
>> join.
>>
>> "LongTom" <(E-Mail Removed)> wrote in message
>> news:BF804A4B-6A34-47F8-A509-(E-Mail Removed)...
>> >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???

hi Allen,

Allen Browne wrote:
> ... a frustrated join.
Maybe the join is just felling depressed?)


mfG
--> stefan <--

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

> 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
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "LongTom" <(E-Mail Removed)> wrote in message
> news:018121BA-5984-456C-827F-(E-Mail Removed)...
> > 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" wrote:
> >
> >> Post the SQL statement of your query.
> >>
> >> It can probably be solved by using a subquery instead of a frustrated
> >> join.
> >>
> >> "LongTom" <(E-Mail Removed)> wrote in message
> >> news:BF804A4B-6A34-47F8-A509-(E-Mail Removed)...
> >> >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???
>
>

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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"LongTom" <(E-Mail Removed)> wrote in message
news:AC036769-0CB0-4894-8DC1-(E-Mail Removed)...
>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" wrote:
>
>> 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" <(E-Mail Removed)> wrote in message
>> news:018121BA-5984-456C-827F-(E-Mail Removed)...
>> > 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" wrote:
>> >
>> >> Post the SQL statement of your query.
>> >>
>> >> It can probably be solved by using a subquery instead of a frustrated
>> >> join.
>> >>
>> >> "LongTom" <(E-Mail Removed)> wrote in message
>> >> news:BF804A4B-6A34-47F8-A509-(E-Mail Removed)...
>> >> >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???

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

> 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
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "LongTom" <(E-Mail Removed)> wrote in message
> news:AC036769-0CB0-4894-8DC1-(E-Mail Removed)...
> >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" wrote:
> >
> >> 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" <(E-Mail Removed)> wrote in message
> >> news:018121BA-5984-456C-827F-(E-Mail Removed)...
> >> > 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" wrote:
> >> >
> >> >> Post the SQL statement of your query.
> >> >>
> >> >> It can probably be solved by using a subquery instead of a frustrated
> >> >> join.
> >> >>
> >> >> "LongTom" <(E-Mail Removed)> wrote in message
> >> >> news:BF804A4B-6A34-47F8-A509-(E-Mail Removed)...
> >> >> >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???
>
>

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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"LongTom" <(E-Mail Removed)> wrote in message
news:331C5CDF-281B-402E-A064-(E-Mail Removed)...
> 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" wrote:
>
>> 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" <(E-Mail Removed)> wrote in message
>> news:AC036769-0CB0-4894-8DC1-(E-Mail Removed)...
>> >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" wrote:
>> >
>> >> 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" <(E-Mail Removed)> wrote in message
>> >> news:018121BA-5984-456C-827F-(E-Mail Removed)...
>> >> > 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" wrote:
>> >> >
>> >> >> Post the SQL statement of your query.
>> >> >>
>> >> >> It can probably be solved by using a subquery instead of a
>> >> >> frustrated
>> >> >> join.
>> >> >>
>> >> >> "LongTom" <(E-Mail Removed)> wrote in message
>> >> >> news:BF804A4B-6A34-47F8-A509-(E-Mail Removed)...
>> >> >> >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???