Solved

delete query access 2010

Posted on 2013-02-06
12
310 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

820 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