[AccessD] need help with query

I would do this by performing two queries and then performing a UNION.
Roughly:
 
SELECT 
    [TableB].Fieldlist
FROM
    [TableB]
WHERE
    [SalesPerson] = 'John'
 
UNION
 
SELECT
    [TableA].FieldList
FROM
    [TabeA] a
LEFT JOIN
    [TableB] b
ON
    [Aggregate of primary keys]
WHERE
    b.[Aggregate of primary keys] IS NULL
 
Note: 
This is off the top of my head so needs to be tested.
Not sure if I got the LEFT JOIN / NULL logic right.
You'll need to fill in the [aggregate PK part]
Field list needs to be identical in both queries for UNION to work
 
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.
 
Cheers,
Andrew
   

-----Original Message-----
From: Eric Barro [mailto:ebarro at afsweb.com]
Sent: Wednesday, 9 July 2003 11:09 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] need help with query


Access 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 <http://www.afsweb.com/>  


IMPORTANT - PLEASE READ ******************** 
This email and any files transmitted with it are confidential and may 
contain information protected by law from disclosure. 
If you have received this message in error, please notify the sender 
immediately and delete this email from your system. 
No warranty is given that this email or files, if attached to this 
email, are free from computer viruses or other defects. They 
are provided on the basis the user assumes all responsibility for 
loss, damage or consequence resulting directly or indirectly from 
their use, whether caused by the negligence of the sender or not.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030709/5660dcba/attachment-0001.html>