[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

delete query access 2010

Posted on 2013-02-06
12
Medium Priority
?
332 Views
Last Modified: 2013-02-07
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
0
Comment
Question by:PeterBaileyUk
  • 7
  • 5
12 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38862920
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
 

Author Comment

by:PeterBaileyUk
ID: 38862940
its highlighting the where and saying missing operator
ee.PNG
0
 
LVL 143

Expert Comment

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

Open in new window

0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:PeterBaileyUk
ID: 38862995
Its still squeeling over brackets but i cannot see it. I am in access query grid.
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 38863019
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
 

Author Comment

by:PeterBaileyUk
ID: 38863027
it must be close it says query must have at least one destination field
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38863036
did you change the query type to DELETE?
0
 

Author Comment

by:PeterBaileyUk
ID: 38863050
yes
0
 

Author Comment

by:PeterBaileyUk
ID: 38863052
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38863060
I will never understand the way ms access requires the DELETE queries :(
0
 

Author Comment

by:PeterBaileyUk
ID: 38863067
anyway its working lets see if access 365 improves things that should arrive within the hour.
0
 

Author Closing Comment

by:PeterBaileyUk
ID: 38863069
thx
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question