Solved

Delete Records in one table that match fields in another table

Posted on 2013-06-17
8
493 Views
Last Modified: 2013-06-18
This select query works fine:

SELECT Bloomin_Net_Zero_Invoices_Items.APPLY_TO, Bloomin_XFER_FILE.*
FROM Bloomin_Net_Zero_Invoices_Items INNER JOIN Bloomin_XFER_FILE ON (Bloomin_Net_Zero_Invoices_Items.[Vendor Product Number] = Bloomin_XFER_FILE.[Vendor Product Number]) AND (Bloomin_Net_Zero_Invoices_Items.APPLY_TO = Bloomin_XFER_FILE.APPLY_TO) AND (Bloomin_Net_Zero_Invoices_Items.[Invoice Date] = Bloomin_XFER_FILE.[Invoice Date]);


I would like to change it to a delete query where all of the matching records in the Bloomin_Xfer_file are deleted.

What is the easiest way to do this?
0
Comment
Question by:rrudolph
[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
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 25

Assisted Solution

by:chaau
chaau earned 300 total points
ID: 39255019
The easiest way to "learn" how to do this is to use MS Access wizards. When you construct a query like this using designer on the top toolbar you will find a few buttons: Query Type. The types are: Select, Make Table, Update, .... Delete.
You should always start with the Select query. It is better to switch from design view to SQL View to understand what Access is doing behind the scenes.
When you are happy with your select statement (i.e. you see in the DataSheet view all the records YOU WISH TO DELETE) you can switch the Query Type to "Delete". At this stage Access will ask you what table to delete from (if you used multiple tables for select).
I suggest that you switch to SQL View regularly to understand how Select statement gets transferred to Delete statement.
I find it easy to learn SQL this way
0
 
LVL 26

Assisted Solution

by:jerryb30
jerryb30 earned 200 total points
ID: 39255022
A sample db  would speed things up. Just the 2 tables in question, and some non-sensitive records.  It might be easier to set a composite index (unique, no dupes) in Bloomin_Net_Zero_Invoices_Items, and append records from Bloomin_Xfer_file, and then clearing Bloomin_Xfer_file.
0
 
LVL 25

Accepted Solution

by:
chaau earned 300 total points
ID: 39255031
BTW, for your query delete statement will look like this:

DELETE Bloomin_XFER_FILE
FROM Bloomin_Net_Zero_Invoices_Items INNER JOIN Bloomin_XFER_FILE ON (Bloomin_Net_Zero_Invoices_Items.[Vendor Product Number] = Bloomin_XFER_FILE.[Vendor Product Number]) AND (Bloomin_Net_Zero_Invoices_Items.APPLY_TO = Bloomin_XFER_FILE.APPLY_TO) AND (Bloomin_Net_Zero_Invoices_Items.[Invoice Date] = Bloomin_XFER_FILE.[Invoice Date]);

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:rrudolph
ID: 39255033
Actually, I started just as you suggested with a select query, both tables with joins, select query worked like a charm, switched to delete query and received an error, before it asked me For the delete table.

I neglected to mention that both tables are linked from another access database
0
 
LVL 25

Assisted Solution

by:chaau
chaau earned 300 total points
ID: 39255039
And what error have you received?
0
 
LVL 26

Assisted Solution

by:jerryb30
jerryb30 earned 200 total points
ID: 39255042
Front-End/Back-End?
Can you delete records from the back-end? (Try this with a copy. Delete * from Bloomin_Xfer_file in 'path\dbname.mdb') Just want to make sure it is not a permissions issue.
0
 

Author Comment

by:rrudolph
ID: 39255800
This is the string that eventually worked. To get the Query Engine to generate the correct code, I had to set the query property unique records to yes.

ProStrSQL = "SELECT Bloomin_XFER_FILE.[Invoice Date], Bloomin_XFER_FILE.APPLY_TO, Bloomin_XFER_FILE.[Vendor Product Number], Sum(Bloomin_XFER_FILE.[Extended Price]) AS [SumOfExtended Price]"
ProStrSQL = ProStrSQL & " FROM Bloomin_XFER_FILE"
ProStrSQL = ProStrSQL & " GROUP BY Bloomin_XFER_FILE.[Invoice Date], Bloomin_XFER_FILE.APPLY_TO, Bloomin_XFER_FILE.[Vendor Product Number]"
ProStrSQL = ProStrSQL & " HAVING (((Sum(Bloomin_XFER_FILE.[Extended Price]))=0))"


Thanks to all that responded.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39255807
Well, I wouldn't try Jerryb's last query unless you want to delete all of the records from a particular table.

I'm going to make this genaric, since I'm not certain which table you want to delete from

DELETE FROM Table1
WHERE Exists (
SELECT Table2.ID
FROM Table2
WHERE Table2.[Vendor Product Number] = Table1.[Vendor Product Number]
 AND Table2.APPLY_TO = Table1.APPLY_TO
 AND Table2.[Invoice Date] = Table1.[Invoice Date])
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Suggested Solutions

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

739 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