Cheers for reply, unfortunately still experiencing problems.
probably didnt give enough information yesterday, brain was shot from
long office day.
my problem is more with access than the SQL, i am trying to run the
SQL in microsoft access 2000. If i replace the DELETE with SELECT it
returns all the records which i wish to delete, thus SQL works ok
However, when i try to execute the DELETE statement i get the error
message
'Could not delete from specified tables'
in a pop up box. The help supplied for this error is the usual ms
waffle which doesnt give you alot to work with.
Any access experts, please have a stab in the dark.
Regards, Alan Aylett
MGFoster <me@privacy.com> wrote in message news:<FjvPc.6320$cK.2377@newsread2.news.pas.earthl ink.net>...[color=blue]
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Might try these:
>
> DELETE N.*
> FROM dbo_SUMBill_New As N INNER JOIN A
> ON N.POBReference = A.POBReference
> AND N.BillAccountNumber = A.BillAccountNumber
>
> or
>
> DELETE *
> FROM dbo_SUMBill_New As N
> WHERE EXISTS (SELECT * FROM A
> WHERE N.POBReference = A.POBReference
> AND N.BillAccountNumber = A.BillAccountNumber)
>
> As a safety precaution change DELETE to SELECT to test - it should show
> you the records that will be selected for deletion.
>
> These queries should delete the records from dbo_SUMBill_New that have
> the same POBReference and BillAccountNumber as table A. Is this what
> you want?
>
> --
> MGFoster:::mgf00 <at> earthlink <decimal-point> net
> Oakland, CA (USA)
>
> -----BEGIN PGP SIGNATURE-----
> Version: PGP for Personal Privacy 5.0
> Charset: noconv
>
> iQA/AwUBQQ6BWoechKqOuFEgEQJd+gCghLYEGRsn/8WAa0i1pIQ3J19HCAMAnjPc
> +/o8GqH0MdK1H27UEcqgYMwM
> =/7+5
> -----END PGP SIGNATURE-----
>
>
> AlanAylett wrote:
>[color=green]
> > Hi all
> >
> > wondering if anybody could spot anything wrong with this SQL DELETE
> > statement, both my Access and SQL skills are not what they used to be
> > so i reckon its something simple, any suggestions appreciated.
> >
> > -----
> > DELETE dbo_SUMBill_New.*, dbo_SUMBill_New.BillAccountNumber,
> > dbo_SUMBill_New.POBReference
> >
> > FROM a INNER JOIN dbo_SUMBill_New ON a.POBReference =
> > dbo_SUMBill_New.POBReference
> >
> > WHERE (((dbo_SUMBill_New.BillAccountNumber)=[a].[BillAccountNumber])
> > AND ((dbo_SUMBill_New.POBReference)=[a].[POBReference]));
> > -----
> >
> > Regards, Alan Aylett[/color][/color]
