<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE>Message</TITLE>
<META http-equiv=Content-Type content="text/html; charset=windows-1252">
<META content="MSHTML 6.00.2800.1170" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><FONT face="Lucida Sans Unicode" color=#0000ff size=2><SPAN
class=407512901-09072003>Access gurus,</SPAN></FONT></DIV>
<DIV><FONT face="Lucida Sans Unicode" color=#0000ff size=2><SPAN
class=407512901-09072003></SPAN></FONT> </DIV>
<DIV><FONT face="Lucida Sans Unicode" color=#0000ff size=2><SPAN
class=407512901-09072003>I need a query that does the following. My brain is
fried and I'm not thinking straight...I'm hoping someone is out there.
:)</SPAN></FONT></DIV>
<DIV><FONT face="Lucida Sans Unicode" color=#0000ff size=2><SPAN
class=407512901-09072003></SPAN></FONT> </DIV>
<DIV><FONT face="Lucida Sans Unicode" color=#0000ff size=2><SPAN
class=407512901-09072003>Table A has the following fields</SPAN></FONT></DIV>
<DIV><FONT face="Lucida Sans Unicode" color=#0000ff size=2><SPAN
class=407512901-09072003>Customer</SPAN></FONT></DIV>
<DIV><FONT face="Lucida Sans Unicode" color=#0000ff size=2><SPAN
class=407512901-09072003>Product</SPAN></FONT></DIV>
<DIV><FONT face="Lucida Sans Unicode" color=#0000ff size=2><SPAN
class=407512901-09072003>Code</SPAN></FONT></DIV>
<DIV><FONT face="Lucida Sans Unicode" color=#0000ff size=2><SPAN
class=407512901-09072003>Cost</SPAN></FONT></DIV>
<DIV><FONT face="Lucida Sans Unicode" color=#0000ff size=2><SPAN
class=407512901-09072003></SPAN></FONT> </DIV>
<DIV><FONT face="Lucida Sans Unicode" color=#0000ff size=2><SPAN
class=407512901-09072003>Table B has the following fields</SPAN></FONT></DIV>
<DIV><FONT face="Lucida Sans Unicode" color=#0000ff size=2><SPAN
class=407512901-09072003>Customer</SPAN></FONT></DIV>
<DIV><FONT face="Lucida Sans Unicode" color=#0000ff size=2><SPAN
class=407512901-09072003>Product</SPAN></FONT></DIV>
<DIV><FONT face="Lucida Sans Unicode" color=#0000ff size=2><SPAN
class=407512901-09072003>Code</SPAN></FONT></DIV>
<DIV><FONT face="Lucida Sans Unicode" color=#0000ff size=2><SPAN
class=407512901-09072003>Cost</SPAN></FONT></DIV>
<DIV><FONT face="Lucida Sans Unicode" color=#0000ff size=2><SPAN
class=407512901-09072003>SalesPerson</SPAN></FONT></DIV>
<DIV><FONT face="Lucida Sans Unicode" color=#0000ff size=2><SPAN
class=407512901-09072003></SPAN></FONT> </DIV>
<DIV><FONT face="Lucida Sans Unicode" color=#0000ff size=2><SPAN
class=407512901-09072003>The rules</SPAN></FONT></DIV>
<DIV><FONT face="Lucida Sans Unicode" color=#0000ff size=2><SPAN
class=407512901-09072003></SPAN></FONT> </DIV>
<DIV><FONT face="Lucida Sans Unicode" color=#0000ff size=2><SPAN
class=407512901-09072003>1. Table A is the master so that when the lookup is
performed it looks up Table B first and if it doesn't find it in Table B it
assumes that it is in Table A.</SPAN></FONT></DIV>
<DIV><FONT face="Lucida Sans Unicode" color=#0000ff size=2><SPAN
class=407512901-09072003>2. I need a result set that pulls the records in Table
A that are not in Table B PLUS all of Table B for that
SalesPerson.</SPAN></FONT></DIV>
<DIV><FONT face="Lucida Sans Unicode" color=#0000ff size=2><SPAN
class=407512901-09072003></SPAN></FONT> </DIV>
<DIV><FONT face="Lucida Sans Unicode" color=#0000ff size=2><SPAN
class=407512901-09072003>Example:</SPAN></FONT></DIV>
<DIV><FONT face="Lucida Sans Unicode" color=#0000ff size=2><SPAN
class=407512901-09072003></SPAN></FONT> </DIV>
<DIV><FONT face="Lucida Sans Unicode" color=#0000ff size=2><SPAN
class=407512901-09072003>From Table A</SPAN></FONT></DIV>
<DIV><FONT face="Lucida Sans Unicode" color=#0000ff size=2><SPAN
class=407512901-09072003>Customer: XYZ</SPAN></FONT></DIV>
<DIV><FONT face="Lucida Sans Unicode" color=#0000ff size=2><SPAN
class=407512901-09072003>Product: ABCD</SPAN></FONT></DIV>
<DIV><FONT face="Lucida Sans Unicode" color=#0000ff size=2><SPAN
class=407512901-09072003>Code: 1</SPAN></FONT></DIV>
<DIV><FONT face="Lucida Sans Unicode" color=#0000ff size=2><SPAN
class=407512901-09072003>Cost: $10</SPAN></FONT></DIV>
<DIV><FONT face="Lucida Sans Unicode" color=#0000ff size=2><SPAN
class=407512901-09072003></SPAN></FONT> </DIV>
<DIV><FONT face="Lucida Sans Unicode" color=#0000ff size=2><SPAN
class=407512901-09072003>Customer: RST</SPAN></FONT></DIV>
<DIV><FONT face="Lucida Sans Unicode" color=#0000ff size=2><SPAN
class=407512901-09072003>Product: EFGH</SPAN></FONT></DIV>
<DIV><FONT face="Lucida Sans Unicode" color=#0000ff size=2><SPAN
class=407512901-09072003>Code: 1</SPAN></FONT></DIV>
<DIV><FONT face="Lucida Sans Unicode" color=#0000ff size=2><SPAN
class=407512901-09072003>Cost: $20</SPAN></FONT></DIV>
<DIV><FONT face="Lucida Sans Unicode" color=#0000ff size=2><SPAN
class=407512901-09072003></SPAN></FONT> </DIV>
<DIV><FONT face="Lucida Sans Unicode" color=#0000ff size=2><SPAN
class=407512901-09072003>From Table B</SPAN></FONT></DIV>
<DIV><FONT face="Lucida Sans Unicode" color=#0000ff size=2><SPAN
class=407512901-09072003>Customer: XYZ</SPAN></FONT></DIV>
<DIV><FONT face="Lucida Sans Unicode" color=#0000ff size=2><SPAN
class=407512901-09072003>Product: ABCD</SPAN></FONT></DIV>
<DIV><FONT face="Lucida Sans Unicode" color=#0000ff size=2><SPAN
class=407512901-09072003>Code: 1</SPAN></FONT></DIV>
<DIV><FONT face="Lucida Sans Unicode" color=#0000ff size=2><SPAN
class=407512901-09072003>Cost: $20</SPAN></FONT></DIV>
<DIV><FONT face="Lucida Sans Unicode" color=#0000ff size=2><SPAN
class=407512901-09072003>SalesPerson: John</SPAN></FONT></DIV>
<DIV><FONT face="Lucida Sans Unicode" color=#0000ff size=2><SPAN
class=407512901-09072003></SPAN></FONT> </DIV>
<DIV><FONT face="Lucida Sans Unicode" color=#0000ff size=2><SPAN
class=407512901-09072003>I want the query to pull for SalesPerson: John (note
that the record from Table B supersedes the record from Table A
(XYZ/ABCD/1)</SPAN></FONT></DIV>
<DIV><FONT face="Lucida Sans Unicode" color=#0000ff size=2><SPAN
class=407512901-09072003></SPAN></FONT> </DIV>
<DIV><FONT face="Lucida Sans Unicode" color=#0000ff size=2><SPAN
class=407512901-09072003>Customer: XYZ</SPAN></FONT></DIV>
<DIV><FONT face="Lucida Sans Unicode" color=#0000ff size=2><SPAN
class=407512901-09072003>Product: ABCD</SPAN></FONT></DIV>
<DIV><FONT face="Lucida Sans Unicode" color=#0000ff size=2><SPAN
class=407512901-09072003>Code: 1</SPAN></FONT></DIV>
<DIV><FONT face="Lucida Sans Unicode" color=#0000ff size=2><SPAN
class=407512901-09072003>Cost: $20</SPAN></FONT></DIV>
<DIV><FONT face="Lucida Sans Unicode" color=#0000ff size=2><SPAN
class=407512901-09072003></SPAN></FONT> </DIV>
<DIV><FONT face="Lucida Sans Unicode" color=#0000ff size=2><SPAN
class=407512901-09072003>and</SPAN></FONT></DIV>
<DIV><FONT face="Lucida Sans Unicode" color=#0000ff size=2><SPAN
class=407512901-09072003></SPAN></FONT> </DIV>
<DIV><FONT face="Lucida Sans Unicode" color=#0000ff size=2><SPAN
class=407512901-09072003>Customer: RST</SPAN></FONT></DIV>
<DIV><FONT face="Lucida Sans Unicode" color=#0000ff size=2><SPAN
class=407512901-09072003>Product: EFGH</SPAN></FONT></DIV>
<DIV><FONT face="Lucida Sans Unicode" color=#0000ff size=2><SPAN
class=407512901-09072003>Code: 1</SPAN></FONT></DIV>
<DIV><FONT face="Lucida Sans Unicode" color=#0000ff size=2><SPAN
class=407512901-09072003>Cost: $20</SPAN></FONT></DIV>
<DIV><FONT face="Lucida Sans Unicode" color=#0000ff size=2><SPAN
class=407512901-09072003></SPAN></FONT> </DIV>
<DIV><FONT face="Lucida Sans Unicode" color=#0000ff size=2><SPAN
class=407512901-09072003>The primary key is the aggregate of Customer, Product
and Code.</SPAN></FONT></DIV>
<DIV><FONT face="Lucida Sans Unicode" color=#0000ff size=2><SPAN
class=407512901-09072003></SPAN></FONT> </DIV>
<DIV> </DIV>
<P><FONT size=2>---<BR>Eric Barro<BR>Senior Systems Analyst<BR>Advanced Field
Services<BR>(208) 772-7060<BR><A href="http://www.afsweb.com/"
target=_blank>http://www.afsweb.com</A> </FONT></P></BODY></HTML>