Solved

delete query access 2010

Posted on 2013-02-06
12
320 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 500 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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Using Sum with Case When within a query 9 47
usage of ms/excell 3d maps in ms/access 2016 6 42
Cant delete records in query 8 47
What does "Between" mean? 6 44
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
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…

732 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