Need help with a Delete Query

  • Thread starter Thread starter Jack Peyton
  • Start date Start date
J

Jack Peyton

I use Access 2000 and I am trying to use a delete query to delete records
from Table "TParkLot" based on criteria from form "FParkLot" and Table
"TGrpUser" (I do not want to delete any of the records in Table "TGrpUser").
The SQL view of the query is:

DELETE TParkLot.*, TParkLot.SiteNo, TParkLot.ParkLotSpac,
TGrpUser.ParkLotID
FROM TParkLot LEFT JOIN TGrpUser ON TParkLot.ParkLotID = TGrpUser.ParkLotID
WHERE (((TParkLot.SiteNo)=[Forms]![FParkLot]![SiteNoB]) AND
((TParkLot.ParkLotSpac) Between [Forms]![FParkLot]![FromA] And
[Forms]![FParkLot]![ToA]) AND ((TGrpUser.ParkLotID) Is Null));

Any advise will be appreciated.
Jack Peyton
 
This might depend on how the relationships between the tables are defined,
but try a subquery to locate the records in TParkLot that have a match in
TGrpUser:

DELETE FROM TParkLot
WHERE EXISTS (SELECT TGrpUser.ParkLotID FROM TGrpUser
WHERE TGrpUser.ParkLotID = TParkLot.ParkLotID)
AND (TParkLot.SiteNo = [Forms]![FParkLot]![SiteNoB])
AND (TParkLot.ParkLotSpac Between [Forms]![FParkLot]![FromA]
And [Forms]![FParkLot]![ToA])
AND (TGrpUser.ParkLotID Is Null);

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066
 
Allen,
Without your help I had been stuck on this query for several days. With
your help I was able to get the query to work in a few minutes.
Many Thanks,
Jack Peyton

Allen Browne said:
This might depend on how the relationships between the tables are defined,
but try a subquery to locate the records in TParkLot that have a match in
TGrpUser:

DELETE FROM TParkLot
WHERE EXISTS (SELECT TGrpUser.ParkLotID FROM TGrpUser
WHERE TGrpUser.ParkLotID = TParkLot.ParkLotID)
AND (TParkLot.SiteNo = [Forms]![FParkLot]![SiteNoB])
AND (TParkLot.ParkLotSpac Between [Forms]![FParkLot]![FromA]
And [Forms]![FParkLot]![ToA])
AND (TGrpUser.ParkLotID Is Null);

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Jack Peyton said:
I use Access 2000 and I am trying to use a delete query to delete records
from Table "TParkLot" based on criteria from form "FParkLot" and Table
"TGrpUser" (I do not want to delete any of the records in Table
"TGrpUser").
The SQL view of the query is:

DELETE TParkLot.*, TParkLot.SiteNo, TParkLot.ParkLotSpac,
TGrpUser.ParkLotID
FROM TParkLot LEFT JOIN TGrpUser ON TParkLot.ParkLotID =
TGrpUser.ParkLotID
WHERE (((TParkLot.SiteNo)=[Forms]![FParkLot]![SiteNoB]) AND
((TParkLot.ParkLotSpac) Between [Forms]![FParkLot]![FromA] And
[Forms]![FParkLot]![ToA]) AND ((TGrpUser.ParkLotID) Is Null));

Any advise will be appreciated.
Jack Peyton
Click to expand...
Click to expand...
 
Back
Top