Solved

Delete Records in one table that match fields in another table

Posted on 2013-06-17
8
450 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
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 24

Assisted Solution

by:chaau
chaau earned 300 total points
Comment Utility
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
Comment Utility
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 24

Accepted Solution

by:
chaau earned 300 total points
Comment Utility
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
 

Author Comment

by:rrudolph
Comment Utility
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 24

Assisted Solution

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

Assisted Solution

by:jerryb30
jerryb30 earned 200 total points
Comment Utility
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
Comment Utility
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)
Comment Utility
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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now