INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Data from two tables comparison

Data from two tables comparison

(OP)
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,

CODE

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

CODE

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

CODE

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
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

CODE

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

CODE

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

CODE

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

RE: Data from two tables comparison

Your first Select, why not simply:

SELECT DISTINCT ITNBR, ORDNO, WKCTR
FROM MYQRY_MOHTRN
WHERE MYQRY_MOHTRN.WKCTR = "BMC"

Anyways, if you want to subtract one set of data from another set, you may try:

SELECT DISTINCT ITNBR, ORDNO, WKCTR
FROM MYQRY_MOHTRN
WHERE ...
MINUS

SELECT DISTINCT Field1, Field2, Field3
FROM SomeOtherTable
WHERE ...

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Data from two tables comparison

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

CODE

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 

RE: Data from two tables comparison

Andy:

Does the MINUS keyword work in JET SQL ?

RE: Data from two tables comparison

PWise,

Sorry, MINUS does not work in Jet SQL blush

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close