• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 533
  • Last Modified:

Delete Records in one table that match fields in another table

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
rrudolph
Asked:
rrudolph
  • 3
  • 2
  • 2
  • +1
5 Solutions
 
chaauCommented:
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
 
jerryb30Commented:
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
 
chaauCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
rrudolphAuthor Commented:
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
 
chaauCommented:
And what error have you received?
0
 
jerryb30Commented:
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
 
rrudolphAuthor Commented:
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
 
Dale FyeCommented:
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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now