Access 97 delet query problem

  • Thread starter Thread starter jon
  • Start date Start date
J

jon

Hi
I have 2 tables one is imported every week with up to date data from a
different system, and the second holds most of our data.
Because some of the records are loaded but the job does not arrive there is
no data on our main table so I want to delete redundant records (jobs which
have not reached us and been stopped).
So the query I have done check the main table Baan and if certain fields are
blank and the field orderNo has no corresponding record in the BaanTemp
table [Prod-Order] and it is these I want to delete in the Baan table.
When I run the query in design all look fine it lists all the records with
no info and have no record in the second table but when I run the query to
actually delete the reorders a get a pop up saying "specify the table
containing the records you want to delete".
I have tried several times but always get the same result. any help is
welcome I have pasted the SQL below.

Thanks Jon


DELETE DISTINCTROW Baan.OrderNo, Baan.CmmDate, Baan.dateIcyBaan,
BaanTemp.[Prod-Order], Baan.DueDate
FROM Baan LEFT JOIN BaanTemp ON Baan.OrderNo = BaanTemp.[Prod-Order]
WHERE (((Baan.CmmDate) Is Null) AND ((Baan.dateIcyBaan) Is Null) AND
((BaanTemp.[Prod-Order]) Is Null));
 
Sorted
Thanks



MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You don't include the column names in a Delete:

DELETE DISTINCTROW Baan.*
FROM Baan LEFT JOIN BaanTemp ON Baan.OrderNo = BaanTemp.[Prod-Order]
WHERE Baan.CmmDate Is Null
AND Baan.dateIcyBaan Is Null
AND BaanTemp.[Prod-Order] Is Null

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSSZFz4echKqOuFEgEQLJEgCgiV/RGcm0GFEau0+AFKMYk1r2FAkAoOqC
oOUib1J2/o7PZGyV/U3W9nA/
=gAyI
-----END PGP SIGNATURE-----

Hi
I have 2 tables one is imported every week with up to date data from a
different system, and the second holds most of our data.
Because some of the records are loaded but the job does not arrive there
is no data on our main table so I want to delete redundant records (jobs
which have not reached us and been stopped).
So the query I have done check the main table Baan and if certain fields
are blank and the field orderNo has no corresponding record in the
BaanTemp table [Prod-Order] and it is these I want to delete in the Baan
table.
When I run the query in design all look fine it lists all the records
with no info and have no record in the second table but when I run the
query to actually delete the reorders a get a pop up saying "specify the
table containing the records you want to delete".
I have tried several times but always get the same result. any help is
welcome I have pasted the SQL below.

Thanks Jon


DELETE DISTINCTROW Baan.OrderNo, Baan.CmmDate, Baan.dateIcyBaan,
BaanTemp.[Prod-Order], Baan.DueDate
FROM Baan LEFT JOIN BaanTemp ON Baan.OrderNo = BaanTemp.[Prod-Order]
WHERE (((Baan.CmmDate) Is Null) AND ((Baan.dateIcyBaan) Is Null) AND
((BaanTemp.[Prod-Order]) Is Null));
Click to expand...
Click to expand...
 
Back
Top