Delete query basis is unmatched query - it's possible?

knaabis

Active Member
Joined
Apr 25, 2006
Messages
254
Office Version
  1. 2013
Platform
  1. Windows
I want delete some data from table - Orders.
I find these data by Unmatched query.
Can i make delete query, where basis of delete query is unmatched query?
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,889
Office Version
  1. 365
Platform
  1. Windows
Create the unmatched Query and save it.

Now create a delete query using the Unmatched Query as your record source..
 

knaabis

Active Member
Joined
Apr 25, 2006
Messages
254
Office Version
  1. 2013
Platform
  1. Windows
DATA_Table
Doc Document No... Line No
Order SA06208.... 10000
Order SA06208.... 20000
Order SA06208.... 30000
Order SA06208.... 40000
Order SA06208.... 50000
Order SA06208.... 60000

DATA 1_Table
Doc Document No... Line No
Order SA06208.... 10000
Order SA06208.... 20000
Order SA06208.... 30000
Order SA06208.... 40000
Order SA06208.... 60000

Anyway i have problem - i made Unmatched Query (i compare these two tables by two colums - Document No and Line No.) and find inadequate Line 50000. Now i need to delete these line from DATA_Table by Delete query.
 
Last edited:

knaabis

Active Member
Joined
Apr 25, 2006
Messages
254
Office Version
  1. 2013
Platform
  1. Windows
I think the problem is - unmatched Query has two comparing columns.
1) Document No
2) Line No

Also these two compared Tables haven't primary key...
I wrote in the previous post - the unmatched is this row:
Doc No.......Line No
SA06208.....50000

How do I delete it by delete query???
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,889
Office Version
  1. 365
Platform
  1. Windows
Instead of an unmatched query and a delete query, create a Make Table Query as shown below and use that to replace the DATA_Table

Code:
SELECT DATA_Table.[Doc Document No], DATA_Table.[Line No], DATA_Table1.[Doc Document No], DATA_Table1.[Line No] INTO [New Data Table]
FROM DATA_Table INNER JOIN DATA_Table1 ON (DATA_Table.[Line No] = DATA_Table1.[Line No]) AND (DATA_Table.[Doc Document No] = DATA_Table1.[Doc Document No]);
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,015
Messages
5,834,938
Members
430,327
Latest member
Mr_Himalayan778

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top