Solved

Delete Records in one table that match fields in another table

Posted on 2013-06-17
8
498 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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 48

Expert Comment

by:Dale Fye
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

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!

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
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 …
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…

628 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