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

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" <LongTom@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:331C5CDF-281B-402E-A064-D6A0404CFE41@xxxxxxxxxxxxxxxx
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 data*** 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 data*** view.
8. Get "Enter Parameter Value?" on "qryExpIndiv.Tenant ID" and all other
fields.
9. Switch to data*** 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" <LongTom@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:AC036769-0CB0-4894-8DC1-ACAB04911678@xxxxxxxxxxxxxxxx
>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" <LongTom@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:018121BA-5984-456C-827F-B0B1632D6312@xxxxxxxxxxxxxxxx
>> > 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" <LongTom@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> >> news:BF804A4B-6A34-47F8-A509-3AB2226D02D6@xxxxxxxxxxxxxxxx
>> >> >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 data*** 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???

.