[AccessD] Delete Query with a join

Sorry, I've been tied up with other things.

I ended up getting rid of the join and doing it like this:

DELETE [rpt_DMHClaim 15].*, [rpt_DMHClaim 15].EOB_ID
FROM [rpt_DMHClaim 15]
WHERE ((([rpt_DMHClaim 15].EOB_ID) In (SELECT [MaxOfEOB_ID] FROM [qry 01a
rpt_DMHClaim 15 Dupes to be deleted])));


On Tue, Sep 2, 2014 at 6:59 PM, Bill Benson <bensonforums at gmail.com> wrote:

> David: get anywhere?
> On Aug 28, 2014 10:40 PM, "Bill Benson" <bensonforums at gmail.com> wrote:
>
> > Try
> >
> >    DELETE [rpt_DMHClaim 14].*
> >    FROM [rpt_DMHClaim 14]
> > WHERE Exists
> >    (SELECT 1
> >     FROM [qry 01a rpt_DMHClaim 14 Dupes to be > deleted]
> >     WHERE [qry 01a rpt_DMHClaim 14 Dupes to be > deleted].ClaimNumber =
> > [rpt_DMHClaim 14].ClaimNumber
> > AND
> >   [qry 01a rpt_DMHClaim 14 Dupes to be > deleted].MaxOfEOB_ID =
> > [rpt_DMHClaim 14].EOB_ID)
> >
> > Action queries don't much like joins ad even distinctrow can lead to
> > duplicates no? Not sure really.
> >
> > Got that here:
> >
> >
> http://stackoverflow.com/questions/5585732/how-to-delete-in-ms-access-when-using-joins
> >
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>