Data from two tables comparison

[evr72]

hello,

I am building a query from a table, this contains item, order number and Department
I want to select all the orders in a specific department. No problem with this,

SELECT MYQRY_MOHTRN.ITNBR, MYQRY_MOHTRN.ORDNO, MYQRY_MOHTRN.WKCTR
FROM MYQRY_MOHTRN
GROUP BY MYQRY_MOHTRN.ITNBR, MYQRY_MOHTRN.ORDNO, MYQRY_MOHTRN.WKCTR
HAVING (((MYQRY_MOHTRN.WKCTR)="BMC"));

I saved the above query as BMCHRS
The query gives me the following

ITNBR	ORDNO	WKCTR
ABCD	M584800	BMC  
ABCD	M591970	BMC  
ABCD	M621940	BMC  
DEFG	M644880	BMC  
XYZZ	M632830	BMC  
WPW	M633690	BMC

the MYQRY_MOHTRN table has the following data

[COLOR=#FCE94F]ITNBR	ORDNO	WKCTR
ABCD	M584800	BMC  
ABCD	M584800	TM
ABCD	M584800	RP
ABCD	M591970	BMC  
ABCD	M591970	TM
ABCD	M591970	SW
ABCD	M591970	RP
ABCD	M621940	BMC  
ABCD	M621940	SW
DEFG	M644880	BMC  
DEFG	M644880	SW
XYZZ	M632830	BMC  
XYZZ	M632830	TK
WPW	M633690	BMC  
WPW	M633690	RP
WPW	M633690	SW[/color]
ABCD	M581223	TM
ABCD	M591878	SW
ABCD	M645689	PC
DEFG	M644555	TM
XYZZ	M699999	TK
WPW	M633333	RP

I want a query that has all the items in BMCHRS query but only show the orders that are not the same as the orders, the WKCTR field is only used to identify the orders to be excluded
this is what I was trying out but I still get orders from BMCHRS

this is what I would like the ending query to be

ABCD	M581223	TM
ABCD	M591878	SW
ABCD	M645689	PC
DEFG	M644555	TM
XYZZ	M699999	TK
WPW	M633333	RP

this is what I have tried so far but no luck

SELECT BMCHRS.ITNBR, MYQRY_MOHTRN.ITNBR, BMCHRS.BMCMO1_ORDNO, MYQRY_MOHTRN.ORDNO, BMCHRS.WKCTR, MYQRY_MOHTRN.WKCTR, IIf(MYQRY_MOHTRN.ORDNO=BMCHRS.BMCMO1_ORDNO,1,0) AS samemo
FROM BMCHRS INNER JOIN MYQRY_MOHTRN ON BMCHRS.ITNBR = MYQRY_MOHTRN.ITNBR
WHERE (((IIf([BMCHRS].[BMCMO1_ORDNO]=[MYQRY_MOHTRN].[ORDNO],1,0))=0));

and

SELECT BMCHRS.ITNBR, MYQRY_MOHTRN.ITNBR, BMCHRS.BMCMO1_ORDNO, MYQRY_MOHTRN.ORDNO, BMCHRS.WKCTR, MYQRY_MOHTRN.WKCTR, IIf(MYQRY_MOHTRN.ORDNO=BMCHRS.BMCMO1_ORDNO,1,0) AS samemo
FROM BMCHRS INNER JOIN MYQRY_MOHTRN ON BMCHRS.ITNBR = MYQRY_MOHTRN.ITNBR
WHERE (([BMCHRS].[BMCMO1_ORDNO]<>[MYQRY_MOHTRN].[ORDNO]));

But no luck on both instances I still get orders from BMCHRS

 

[Andrzejek]

Your first Select, why not simply:
[tt]
SELECT DISTINCT ITNBR, ORDNO, WKCTR
FROM MYQRY_MOHTRN
WHERE MYQRY_MOHTRN.WKCTR = "BMC"
[/tt]
Anyways, if you want to subtract one set of data from another set, you may try:
[tt]
SELECT DISTINCT ITNBR, ORDNO, WKCTR
FROM MYQRY_MOHTRN
WHERE ...[blue]
MINUS[/blue]
SELECT DISTINCT Field1, Field2, Field3
FROM SomeOtherTable
WHERE ...
[/tt]

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[MajP]

Left join your second query to your first query by the ITNBR and the ORDNO. Should be something like

SELECT 
 ITNBR,
 ORDNO
FROM 
 MYQRY_MOHTRN
LEFT JOIN 
 BMCHRS
ON 
 (MYQRY_MOHTRN.ORDNO = BMCHRS.ORDNO) AND (MYQRY_MOHTRN.ITNBR = BMCHRS.ITNBR)
WHERE 
 BMCHRS.ITNBR Is Null

 

[PWise]

Andy:

Does the MINUS keyword work in JET SQL ?

 

[Andrzejek]

PWise,

Sorry, MINUS does not work in Jet SQL

Some explanation here and here. I usually deal straight with ORACLE and keep forgetting about Access' limitations.

Have fun.

---- Andy

There is a great need for a sarcasm font.