delete query access 2010

I have two identicle  tables one has records to work on TblExportVinstems and TblExportVinstemsReviewed where the records are moved too once they are reviewed.

Once they are reviewed they may have some changes and these codes are added to table uploadforcedmatches.

that process all works ok.

What I would like to do is have an option to take the codes out of the TblExportVinstemsReviewed table and append back to TblExportVinstems (which i have coded in vba.

If I do that I need delete the records in TblExportVinstemsReviewed that exist in TblExportVinstems.

and then

If I do that I need delete the records in uploadforcedmatches that DO NOT exist in TblExportVinstemsReviewed.

I tried this

DELETE Exists (SELECT * FROM  [TblExportVinstemsReviewed]  where TblExportVinstemsReviewed.[VRR_VehicleID] = [TblExportVinstems].[VRR_VehicleID]) AS Expr1, *
FROM TblExportVinstemsReviewed
WHERE (((Exists (SELECT * FROM  [TblExportVinstemsReviewed]  where TblExportVinstemsReviewed.[VRR_VehicleID] = [TblExportVinstems].[VRR_VehicleID]))=True));

it fails on vrr_vehicleID
PeterBaileyUkAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
sorry ,,,, the FROM keyword is missing
http://msdn.microsoft.com/en-us/library/office/bb177896%28v=office.12%29.aspx
I had looked at the reference, and still forgot to put it:
DELETE FROM [TblExportVinstemsReviewed] 
WHERE EXISTS (SELECT * 
    FROM  [TblExportVinstems] 
     WHERE ( TblExportVinstemsReviewed.[VRR_VehicleID] = [TblExportVinstems].[VRR_VehicleID] )
)  

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please try this:
DELETE [TblExportVinstemsReviewed] 
WHERE EXISTS (SELECT * 
    FROM  [TblExportVinstems] 
     WHERE TblExportVinstemsReviewed.[VRR_VehicleID] = [TblExportVinstems].[VRR_VehicleID]
)  

Open in new window

http://msdn.microsoft.com/en-us/library/office/bb177896%28v=office.12%29.aspx
0
 
PeterBaileyUkAuthor Commented:
its highlighting the where and saying missing operator
ee.PNG
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I think it's missing ():
DELETE [TblExportVinstemsReviewed] 
WHERE EXISTS (SELECT * 
    FROM  [TblExportVinstems] 
     WHERE ( TblExportVinstemsReviewed.[VRR_VehicleID] = [TblExportVinstems].[VRR_VehicleID] )
)  

Open in new window

0
 
PeterBaileyUkAuthor Commented:
Its still squeeling over brackets but i cannot see it. I am in access query grid.
0
 
PeterBaileyUkAuthor Commented:
it must be close it says query must have at least one destination field
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
did you change the query type to DELETE?
0
 
PeterBaileyUkAuthor Commented:
yes
0
 
PeterBaileyUkAuthor Commented:
I added a field vrr and it works:
DELETE Exists (SELECT *
    FROM  [TblExportVinstems]
     WHERE ( TblExportVinstemsReviewed.[VRR_VehicleID] = [TblExportVinstems].[VRR_VehicleID] )
) AS Expr1, TblExportVinstemsReviewed.VRR_VehicleID
FROM TblExportVinstemsReviewed
WHERE (((Exists (SELECT *
    FROM  [TblExportVinstems]
     WHERE ( TblExportVinstemsReviewed.[VRR_VehicleID] = [TblExportVinstems].[VRR_VehicleID] )
))<>False));
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I will never understand the way ms access requires the DELETE queries :(
0
 
PeterBaileyUkAuthor Commented:
anyway its working lets see if access 365 improves things that should arrive within the hour.
0
 
PeterBaileyUkAuthor Commented:
thx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.