<html><div style='background-color:'><DIV>
<P>without writing the SQL...you want to select (fields) from tblA where the key is Null in tblB then UNION tblB where salesperson = the sales persons ID. You have to bring back the same fields in both queries in a UNION but it looks like that will be OK. <BR>_D<BR></P></DIV><BR><BR><BR>"Things are only free to the extent that you don't pay for them".-Don Elliker
<DIV></DIV>
<DIV></DIV>
<DIV></DIV>
<DIV></DIV>>From: "Eric Barro" <EBARRO@AFSWEB.COM>
<DIV></DIV>>Reply-To: Access Developers discussion and problem solving<ACCESSD@DATABASEADVISORS.COM>
<DIV></DIV>>To: "Access Developers discussion and problem solving"<ACCESSD@DATABASEADVISORS.COM>
<DIV></DIV>>Subject: [AccessD] need help with query
<DIV></DIV>>Date: Tue, 8 Jul 2003 20:45:58 -0700
<DIV></DIV>>
<DIV></DIV>>MessageAccess gurus,
<DIV></DIV>>
<DIV></DIV>>I need a query that does the following. My brain is fried and I'm not
<DIV></DIV>>thinking straight...I'm hoping someone is out there. :)
<DIV></DIV>>
<DIV></DIV>>Table A has the following fields
<DIV></DIV>>Customer
<DIV></DIV>>Product
<DIV></DIV>>Code
<DIV></DIV>>Cost
<DIV></DIV>>
<DIV></DIV>>Table B has the following fields
<DIV></DIV>>Customer
<DIV></DIV>>Product
<DIV></DIV>>Code
<DIV></DIV>>Cost
<DIV></DIV>>SalesPerson
<DIV></DIV>>
<DIV></DIV>>The rules
<DIV></DIV>>
<DIV></DIV>>1. Table A is the master so that when the lookup is performed it looks up
<DIV></DIV>>Table B first and if it doesn't find it in Table B it assumes that it is in
<DIV></DIV>>Table A.
<DIV></DIV>>2. I need a result set that pulls the records in Table A that are not in
<DIV></DIV>>Table B PLUS all of Table B for that SalesPerson.
<DIV></DIV>>
<DIV></DIV>>Example:
<DIV></DIV>>
<DIV></DIV>> >From Table A
<DIV></DIV>>Customer: XYZ
<DIV></DIV>>Product: ABCD
<DIV></DIV>>Code: 1
<DIV></DIV>>Cost: $10
<DIV></DIV>>
<DIV></DIV>>Customer: RST
<DIV></DIV>>Product: EFGH
<DIV></DIV>>Code: 1
<DIV></DIV>>Cost: $20
<DIV></DIV>>
<DIV></DIV>> >From Table B
<DIV></DIV>>Customer: XYZ
<DIV></DIV>>Product: ABCD
<DIV></DIV>>Code: 1
<DIV></DIV>>Cost: $20
<DIV></DIV>>SalesPerson: John
<DIV></DIV>>
<DIV></DIV>>I want the query to pull for SalesPerson: John (note that the record from
<DIV></DIV>>Table B supersedes the record from Table A (XYZ/ABCD/1)
<DIV></DIV>>
<DIV></DIV>>Customer: XYZ
<DIV></DIV>>Product: ABCD
<DIV></DIV>>Code: 1
<DIV></DIV>>Cost: $20
<DIV></DIV>>
<DIV></DIV>>and
<DIV></DIV>>
<DIV></DIV>>Customer: RST
<DIV></DIV>>Product: EFGH
<DIV></DIV>>Code: 1
<DIV></DIV>>Cost: $20
<DIV></DIV>>
<DIV></DIV>>The primary key is the aggregate of Customer, Product and Code.
<DIV></DIV>>
<DIV></DIV>>
<DIV></DIV>>---
<DIV></DIV>>Eric Barro
<DIV></DIV>>Senior Systems Analyst
<DIV></DIV>>Advanced Field Services
<DIV></DIV>>(208) 772-7060
<DIV></DIV>>http://www.afsweb.com
<DIV></DIV>>
<DIV></DIV>>---
<DIV></DIV>>Outgoing mail is certified Virus Free.
<DIV></DIV>>Checked by AVG anti-virus system (http://www.grisoft.com).
<DIV></DIV>>Version: 6.0.493 / Virus Database: 292 - Release Date: 6/25/2003
<DIV></DIV>>_______________________________________________
<DIV></DIV>>AccessD mailing list
<DIV></DIV>>AccessD@databaseadvisors.com
<DIV></DIV>>http://databaseadvisors.com/mailman/listinfo/accessd
<DIV></DIV>>Website: http://www.databaseadvisors.com
<DIV></DIV></div><br clear=all><hr>MSN 8 with <a href="http://g.msn.com/8HMYENUS/2740??PS=">e-mail virus protection service: </a> 2 months FREE*</html>