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

knaabis

Board Regular
Joined
Apr 25, 2006
Messages
246
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?
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,852
Office Version
  1. 2019
Platform
  1. Windows
Create the unmatched Query and save it.

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

knaabis

Board Regular
Joined
Apr 25, 2006
Messages
246
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

Board Regular
Joined
Apr 25, 2006
Messages
246
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
5,852
Office Version
  1. 2019
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]);
 

Watch MrExcel Video

Forum statistics

Threads
1,108,959
Messages
5,525,899
Members
409,671
Latest member
nasseralateek

This Week's Hot Topics

Top