Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Delete Records in one table that match fields in another table

Posted on 2013-06-17
8
Medium Priority
?
524 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 25

Assisted Solution

by:chaau
chaau earned 900 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 600 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 900 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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 900 total points
ID: 39255039
And what error have you received?
0
 
LVL 26

Assisted Solution

by:jerryb30
jerryb30 earned 600 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 49

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

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

886 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