?
Solved

delete query access 2010

Posted on 2013-02-06
12
Medium Priority
?
324 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

770 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