<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<TITLE>Message</TITLE>
<META content="MSHTML 6.00.2800.1170" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><SPAN class=900365102-09072003><FONT face=Verdana color=#0000ff size=2>I
would do this by performing two queries and then performing a UNION.
Roughly:</FONT></SPAN></DIV>
<DIV><SPAN class=900365102-09072003><FONT face=Verdana color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=900365102-09072003><FONT face=Verdana color=#0000ff
size=2>SELECT </FONT></SPAN></DIV>
<DIV><SPAN class=900365102-09072003><FONT face=Verdana color=#0000ff
size=2> [TableB].Fieldlist</FONT></SPAN></DIV>
<DIV><SPAN class=900365102-09072003><FONT face=Verdana color=#0000ff
size=2>FROM</FONT></SPAN></DIV>
<DIV><SPAN class=900365102-09072003><FONT face=Verdana color=#0000ff
size=2> [TableB]</FONT></SPAN></DIV>
<DIV><SPAN class=900365102-09072003><FONT face=Verdana color=#0000ff
size=2>WHERE</FONT></SPAN></DIV>
<DIV><SPAN class=900365102-09072003><FONT face=Verdana><FONT color=#0000ff
size=2> [SalesPerson] = 'John'</FONT></FONT></SPAN></DIV>
<DIV><SPAN class=900365102-09072003><FONT face=Verdana color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=900365102-09072003><FONT face=Verdana color=#0000ff
size=2>UNION</FONT></SPAN></DIV>
<DIV><SPAN class=900365102-09072003><FONT face=Verdana color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=900365102-09072003><FONT face=Verdana color=#0000ff
size=2>SELECT</FONT></SPAN></DIV>
<DIV><SPAN class=900365102-09072003><FONT face=Verdana color=#0000ff
size=2> [TableA].FieldList</FONT></SPAN></DIV>
<DIV><SPAN class=900365102-09072003><FONT face=Verdana color=#0000ff
size=2>FROM</FONT></SPAN></DIV>
<DIV><SPAN class=900365102-09072003><FONT face=Verdana><FONT color=#0000ff
size=2> [TabeA] a</FONT></FONT></SPAN></DIV>
<DIV><SPAN class=900365102-09072003><FONT face=Verdana color=#0000ff
size=2>LEFT JOIN</FONT></SPAN></DIV>
<DIV><SPAN class=900365102-09072003><FONT face=Verdana><FONT color=#0000ff
size=2> [TableB] b</FONT></FONT></SPAN></DIV>
<DIV><SPAN class=900365102-09072003><FONT face=Verdana color=#0000ff
size=2>ON</FONT></SPAN></DIV>
<DIV><SPAN class=900365102-09072003><FONT face=Verdana color=#0000ff
size=2> [Aggregate of primary keys]</FONT></SPAN></DIV>
<DIV><SPAN class=900365102-09072003><FONT face=Verdana color=#0000ff
size=2>WHERE</FONT></SPAN></DIV>
<DIV><SPAN class=900365102-09072003><FONT face=Verdana color=#0000ff
size=2> b.[Aggregate of primary keys] IS
NULL</FONT></SPAN></DIV>
<DIV><SPAN class=900365102-09072003><FONT face=Verdana color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=900365102-09072003><FONT face=Verdana color=#0000ff
size=2>Note: </FONT></SPAN></DIV>
<DIV><SPAN class=900365102-09072003><FONT face=Verdana color=#0000ff size=2>This
is off the top of my head so needs to be tested.</FONT></SPAN></DIV>
<DIV><SPAN class=900365102-09072003><FONT face=Verdana color=#0000ff size=2>Not
sure if I got the LEFT JOIN / NULL logic right.</FONT></SPAN></DIV>
<DIV><SPAN class=900365102-09072003><FONT face=Verdana color=#0000ff
size=2>You'll need to fill in the [aggregate PK part]</FONT></SPAN></DIV>
<DIV><SPAN class=900365102-09072003><FONT face=Verdana color=#0000ff
size=2>Field list needs to be identical in both queries for UNION to
work</FONT></SPAN></DIV>
<DIV><SPAN class=900365102-09072003><FONT face=Verdana color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=900365102-09072003><FONT face=Verdana color=#0000ff size=2>Out
of interest, why are these records in separate tables? Would be easier to
place them in one. Also, might be easier to create a single field primary
key just to make things easier in these types of queries.</FONT></SPAN></DIV>
<DIV><SPAN class=900365102-09072003><FONT face=Verdana color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=900365102-09072003><FONT face=Verdana color=#0000ff
size=2>Cheers,<BR>Andrew</FONT></SPAN></DIV>
<DIV><SPAN class=900365102-09072003><FONT face=Verdana color=#0000ff
size=2> </FONT></SPAN></DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B> Eric Barro
[mailto:ebarro@afsweb.com]<BR><B>Sent:</B> Wednesday, 9 July 2003 11:09
AM<BR><B>To:</B> Access Developers discussion and problem
solving<BR><B>Subject:</B> [AccessD] need help with query<BR><BR></FONT></DIV>
<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></BLOCKQUOTE></BODY></HTML>
<BR>
<P><B><FONT SIZE=2 FACE="Arial">IMPORTANT - PLEASE READ</FONT></B> <FONT SIZE=2 FACE="Arial">******************** </FONT></P>
<P><FONT SIZE=2 FACE="Arial">This email and any files transmitted with it are confidential and may </FONT></P>
<P><FONT SIZE=2 FACE="Arial">contain information protected by law from disclosure. </FONT></P>
<P><FONT SIZE=2 FACE="Arial">If you have received this message in error, please notify the sender </FONT></P>
<P><FONT SIZE=2 FACE="Arial">immediately and delete this email from your system. </FONT></P>
<P><FONT SIZE=2 FACE="Arial">No warranty is given that this email or files, if attached to this </FONT></P>
<P><FONT SIZE=2 FACE="Arial">email, are free from computer viruses or other defects. They </FONT></P>
<P><FONT SIZE=2 FACE="Arial">are provided on the basis the user assumes all responsibility for </FONT></P>
<P><FONT SIZE=2 FACE="Arial">loss, damage or consequence resulting directly or indirectly from </FONT></P>
<P><FONT SIZE=2 FACE="Arial">their use, whether caused by the negligence of the sender or not.</FONT></P>