[AccessD] need help with query

Eric Barro ebarro at afsweb.com
Tue Jul 8 22:45:58 CDT 2003


MessageAccess gurus,

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. :)

Table A has the following fields
Customer
Product
Code
Cost

Table B has the following fields
Customer
Product
Code
Cost
SalesPerson

The rules

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

Example:

>From Table A
Customer: XYZ
Product: ABCD
Code: 1
Cost: $10

Customer: RST
Product: EFGH
Code: 1
Cost: $20

>From Table B
Customer: XYZ
Product: ABCD
Code: 1
Cost: $20
SalesPerson: John

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)

Customer: XYZ
Product: ABCD
Code: 1
Cost: $20

and

Customer: RST
Product: EFGH
Code: 1
Cost: $20

The primary key is the aggregate of Customer, Product and Code.


---
Eric Barro
Senior Systems Analyst
Advanced Field Services
(208) 772-7060
http://www.afsweb.com

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.493 / Virus Database: 292 - Release Date: 6/25/2003
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030708/5e998d21/attachment-0001.html>


More information about the AccessD mailing list