"set" operators other than UNION

[dalebeitz]

Greetings!

I have a database that tracks salesman daily activities, ie, calling on customers.

tblMaster holds information about one salesman and day
tblDetail holds information about each customer call that ocurred on a given day.
tblSalesman holds salesman information
tblCustomer holds customer information
tblAssign links customers to their assigned salesmen

What I'm trying to do is write a query that lists all customers who have NOT been called on by a given salesman within a range of dates. I'm more familiar with Oracle than I am with Access, and (ignoring the dates for simplicity) I'd do this in Oracle as follows:

Select CustID from tblAssign
where SalesmanID = 7
MINUS
select distinct tblDetail.CustID from tblMaster, tblDetail
where tblMaster.SalesmanID = 7
tblMaster.DayID = tblDetail.DayID

The idea here is that the first select statement lists all customers assigned to a salesman, the second lists all customers called on by that salesman, and the MINUS operator performs a set subtraction on the two result sets, returning the list of customers not called on. How would I do this in Access? From what I've been able to find, Access only supports UNION as a set operator.

Thanks in advance!
Dale Beitz
muza@myne.com

 

[RiverGuy]

Have you tried joining the Assign table on Master table by Salesman ID, and Master and Detail on Day? Selecting where SalesmanID <> 7?

 

[CharlesCook]

Could you do something like this?

The inner query will return a list of customers the
Saleman visited on day 10. The outter query brings back everyone else the salesman is assigned to.

Select
A.CustID
from
tblAssign A
where
((A.SalesmanID = 7)
AND
(A.CustID Not In(select
B.CustID
from
tblMaster B
where
((B.SalesmanID = A.SalesmanID)
AND
(B.DayID = 10)))) CharlesCook.com
ADP - PeopleSoft
ReportSmith - Crystal Reports - SQR - Query
Reporting - Interfaces - Data Mining

 

[MichaelRed]

per CharlesCookdotcom, just replace the &quot;MINUS&quot; with &quot;Not In&quot;. &quot;in&quot; your original query.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over